Details
Description
A select of a CASE statement that performed acceptably in 10.2.2.0 is very slow in 10.3.1.4 the first time it is executed.
The following example ij script:
ELAPSEDTIME ON;
CREATE table test1(id integer);
CREATE table test2(id varchar(10));
SELECT CASE WHEN t.id = 1 THEN 'a'
WHEN t.id = 2 THEN 'b'
WHEN t.id = 3 THEN 'c'
WHEN t.id = 4 THEN 'd'
WHEN t.id = 5 THEN 'e'
WHEN t.id = 6 THEN 'f'
WHEN t.id = 7 THEN 'g'
WHEN t.id = 8 THEN 'h'
WHEN t.id = 11 THEN 'i'
WHEN t.id = 12 THEN 'j'
WHEN t.id = 15 THEN 'k'
WHEN t.id = 16 THEN 'l'
WHEN t.id = 23 THEN 'm'
WHEN t.id = 24 THEN 'n'
WHEN t.id = 27 THEN 'o'
WHEN t.id = 31 THEN 'p'
WHEN t.id = 41 THEN 'q'
WHEN t.id = 42 THEN 'r'
WHEN t.id = 50 THEN 's'
ELSE (SELECT t2.id
FROM test2 t2
)
END
FROM test1 t;
When run on 10.2.2.0 the select results in ELAPSED TIME = 187 milliseconds.
When run on 10.3.1.4 the select results in ELAPSED TIME = 62281 milliseconds.
Attachments
Attachments
Issue Links
- is related to
-
DERBY-1620 SQL CASE statement returns ERROR 42X89 when including NULL as a return value
- Closed
- relates to
-
DERBY-3032 java.lang.ClassCastException returning null from a case statement in subquery
- Closed