Uploaded image for project: 'Apache Trafodion (Retired)'
  1. Apache Trafodion (Retired)
  2. TRAFODION-1575

Self-referencing update updates the column to a wrong value

Add voteWatch issue
    XMLWordPrintableJSON

Details

    • Bug
    • Status: In Progress
    • Major
    • Resolution: Unresolved
    • 1.3-incubating
    • 2.4
    • sql-cmp
    • None
    • Can be reproduced on a workstation

    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.

      Attachments

        Activity

          People

            selvag Selvaganesan Govindarajan
            dbirdsall Dave Birdsall

            Dates

              Created:
              Updated:

              Slack

                Issue deployment