View Issue Details

IDProjectCategoryView StatusLast Update
0008317ADK-V4REVSQLpublic2018-07-21 08:27
ReporterScott RobertsAssigned ToIvan Smahin 
PriorityhighSeveritymajorReproducibilityalways
Status resolvedResolutionfixed 
PlatformALLOSALLOS VersionALL
Product Version 
Target VersionFixed in Version8.3.x 
Summary0008317: CASE statement with ENUM values crashes Valentina Studio
DescriptionWhen ENUM values are used in the WHEN clauses of a CASE statement, Valentina Studio crashes.
Steps To Reproduce1. Open the Houses and Rooms database (see attached files).
2. Execute the test_enum_case procedure.
3. Valentina Studio crashes.
TagsNo tags attached.

Activities

Scott Roberts

Scott Roberts

2018-07-19 13:52

reporter  

Houses and Rooms.zip (13,554 bytes)
Ruslan Zasukhin

Ruslan Zasukhin

2018-07-20 07:34

administrator   ~0010281

crash actually in vkernel.
Ivan Smahin

Ivan Smahin

2018-07-20 12:15

manager   ~0010284

Actually you forgot to iterate cursor in the loop and you will get an endless loop staying on the first record.

It must be something like this one:
create or replace procedure test_enum_case
    (
    )
begin
    declare v_cursor cursor for
        select
            *
        from
            houses;
    
    declare v_address string;
    declare v_house_type house_type;
    
    open v_cursor;
    fetch first v_cursor into
        v_address,
        v_house_type;

    loop
        begin
            case v_house_type
                when 'garden' then
                    print v_address || ' - garden';
                when 'ranch' then
                    print v_address || ' - ranch';
                when 'apartment' then
                    print v_address || ' - apartment';
            end case

        fetch NEXT v_cursor into
            v_address,
            v_house_type;

        EXCEPTION
            WHEN ERR_CURSOR_WRONG_POSITION THEN
                LEAVE
        end
    end loop
end
Scott Roberts

Scott Roberts

2018-07-20 13:32

reporter   ~0010285

I had the cursor iteration in my project. I used this database to see if I could reproduce the issue and forgot to include the cursor iteration in the code.

Also, are ENUMs and STRINGs interchangeable like DATEs and STRINGs? When I INSERT a new record, I use a string for an ENUM field and that works fine.

However, when I used a CURSOR in a SQL procedure, I could not use a STRING variable for the ENUM field and had to use a variable that was declared as the ENUM type.

I was able to work around the CASE issue that I had here by declaring another variable of type STRING and setting it to the value of the ENUM variable. I then used that STRING variable in my CASE statement.

-----

For example (using the attached database):

INSERT INTO houses(address, house_type) VALUES('123 Street', 'garden');

works fine.

But, if the code I provided above is changed to declare v_house_type as STRING, there will be a crash at the FETCH statement. I had to change that v_house_type to house_type to avoid that crash. Then, I found the issue with the CASE statement.

Issue History

Date Modified Username Field Change
2018-07-19 13:52 Scott Roberts New Issue
2018-07-19 13:52 Scott Roberts File Added: Houses and Rooms.zip
2018-07-20 07:34 Ruslan Zasukhin Assigned To => Ivan Smahin
2018-07-20 07:34 Ruslan Zasukhin Status new => assigned
2018-07-20 07:34 Ruslan Zasukhin Note Added: 0010281
2018-07-20 12:15 Ivan Smahin Note Added: 0010284
2018-07-20 13:32 Scott Roberts Note Added: 0010285
2018-07-21 08:27 Ivan Smahin Status assigned => resolved
2018-07-21 08:27 Ivan Smahin Fixed in Version => 8.3.x
2018-07-21 08:27 Ivan Smahin Resolution open => fixed