Details
Major Description
As shown in the following execution output, the update statement tries to update c2 with count(distinct c2) from the same table. While the subquery ‘select c from (select count(distinct c2) from mytable) dt(c)’ returns the correct result 3 when it is run by itself, the update statement using the same subquery updated the column c2 to 2, instead of 3. The updated value always seems to be 1 less in this case.
Here is the execution output:
>>create schema mytest;
— SQL operation complete.
>>
>>create table mytable (c1 char(1), c2 integer);
— SQL operation complete.
>>
>>insert into mytable values ('A', 100), ('B', 200), ('C', 300);
— 3 row(s) inserted.
>>select * from mytable order by 1;
C1 C2
– -----------
A 100
B 200
C 300
— 3 row(s) selected.
>>select c from (select count(distinct c2) from mytable) dt(c);
C
--------------------
3
— 1 row(s) selected.
>>
>>prepare xx from update mytable set c2 =
+>(select c from (select count(distinct c2) from mytable) dt(c))
+>where c2 = 100;
— SQL command prepared.
>>explain options 'f' xx;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
12 . 13 root x 1.00E+001
10 11 12 tuple_flow 1.00E+001
. . 11 trafodion_insert MYTABLE 1.00E+000
9 . 10 sort 1.00E+001
8 4 9 hybrid_hash_join 1.00E+001
6 7 8 nested_join 1.00E+001
. . 7 trafodion_delete MYTABLE 1.00E+000
5 . 6 sort 1.00E+001
. . 5 trafodion_scan MYTABLE 1.00E+001
3 . 4 sort_scalar_aggr 1.00E+000
2 . 3 sort_scalar_aggr 1.00E+000
1 . 2 hash_groupby 2.00E+000
. . 1 trafodion_scan MYTABLE 1.00E+002
— SQL operation complete.
>>execute xx;
— 1 row(s) updated.
>>
>>select * from mytable order by 1;
C1 C2
– -----------
A 2
B 200
C 300
— 3 row(s) selected.
>>
>>drop schema mytest cascade;
— SQL operation complete.
>>
The value of C2 in row A above should have been updated to 3.
This problem was found by Wei-Shiun Tsai.