Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4116

working_dml/subupd003.sql bad results

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 1.2, 1.5.2
    • 1.5.3
    • DMLProc
    • None
    • 2020-7

    Description

      In working_dml/misc/subupd003.sql, we find the following query:

      set autocommit=0;
      UPDATE sub1 SET c2 = (SELECT SUM(sub2.c3) FROM sub2 where sub1.c1=sub2.c1);

      // And to see what happened:
      select * from sub1;
      rollback;

      In Columnstore 1.5 when run from clent, we get:
      ERROR 1264 (22003): CAL0002: IDB-2025: Data truncated for column 'c2'

      In columnstore 1.2 and in 1.5 when run in the test suite, we get:
      ---------------------------------+

      c1 c2 c3 s1 s2 s3

      ---------------------------------+

      1 -2147483646 1 1 1 1
      2 -2147483646 2 2 2 2
      3 -2 3 3 3 3
      4 -2 1 1 1 1
      5 -2 99 99 99 99
      6 -2147483646 NULL NULL NULL NULL

      ---------------------------------+

      This is incorrect. The correct answer is (gleaned from a reference innodb):
      --------------------------+

      c1 c2 c3 s1 s2 s3

      --------------------------+

      1 6 1 1 1 1
      2 9 2 2 2 2
      3 NULL 3 3 3 3
      4 NULL 1 1 1 1
      5 NULL 99 99 99 99
      6 2 NULL NULL NULL NULL

      --------------------------+

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          David.Hall David Hall (Inactive) added a comment -

          Be sure to correct the ref file when fixed.

          David.Hall David Hall (Inactive) added a comment - Be sure to correct the ref file when fixed.
          David.Hall David Hall (Inactive) added a comment -

          we_dmlcommandproc.cpp WE_DMLCommandProc::processUpdate() doesn't handle long double, causing logic to fall to default and treat the value as an int64.

          Simply add a case statement for CalpontSystemCatalog::LONGDOUBLE.

          David.Hall David Hall (Inactive) added a comment - we_dmlcommandproc.cpp WE_DMLCommandProc::processUpdate() doesn't handle long double, causing logic to fall to default and treat the value as an int64. Simply add a case statement for CalpontSystemCatalog::LONGDOUBLE.
          tntnatbry Gagan Goel (Inactive) added a comment -

          David.Hall Does it also need to be ported back to 1.4 and 1.2?

          I see that you have corrected the ref file as part of your MCOL-4154 PR in the regression repo here: https://github.com/mariadb-corporation/mariadb-columnstore-regression-test/pull/231/files#diff-ce22293a8e46b23367f519d0e1137b3b

          tntnatbry Gagan Goel (Inactive) added a comment - David.Hall Does it also need to be ported back to 1.4 and 1.2? I see that you have corrected the ref file as part of your MCOL-4154 PR in the regression repo here: https://github.com/mariadb-corporation/mariadb-columnstore-regression-test/pull/231/files#diff-ce22293a8e46b23367f519d0e1137b3b
          David.Hall David Hall (Inactive) added a comment -

          Use
          set sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
          to test from command line

          David.Hall David Hall (Inactive) added a comment - Use set sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"; to test from command line
          dleeyh Daniel Lee (Inactive) added a comment -

          Builds tested 1.5.3-1 (drone #280 and #287)

          The issue is still in build #280, but fixed in #287 (built with community server)

          dleeyh Daniel Lee (Inactive) added a comment - Builds tested 1.5.3-1 (drone #280 and #287) The issue is still in build #280, but fixed in #287 (built with community server)
          dleeyh Daniel Lee (Inactive) added a comment -

          Build verified: 1.5.3-1 (drone #299, Enterprise)

          MariaDB [mytest]> set sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
          Query OK, 0 rows affected (0.000 sec)

          MariaDB [mytest]> set autocommit=0;
          Query OK, 0 rows affected (0.000 sec)

          MariaDB [mytest]> UPDATE sub1 SET c2 = (SELECT SUM(sub2.c3) FROM sub2 where sub1.c1=sub2.c1);
          Query OK, 6 rows affected (0.311 sec)
          Rows matched: 6 Changed: 6 Warnings: 0

          MariaDB [mytest]> select * from sub1;
          --------------------------+

          c1 c2 c3 s1 s2 s3

          --------------------------+

          1 6 1 1 1 1
          2 9 2 2 2 2
          3 NULL 3 3 3 3
          4 NULL 1 1 1 1
          5 NULL 99 99 99 99
          6 2 NULL NULL NULL NULL

          --------------------------+
          6 rows in set (0.028 sec)

          dleeyh Daniel Lee (Inactive) added a comment - Build verified: 1.5.3-1 (drone #299, Enterprise) MariaDB [mytest] > set sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"; Query OK, 0 rows affected (0.000 sec) MariaDB [mytest] > set autocommit=0; Query OK, 0 rows affected (0.000 sec) MariaDB [mytest] > UPDATE sub1 SET c2 = (SELECT SUM(sub2.c3) FROM sub2 where sub1.c1=sub2.c1); Query OK, 6 rows affected (0.311 sec) Rows matched: 6 Changed: 6 Warnings: 0 MariaDB [mytest] > select * from sub1; ----- ---- ---- ---- ---- -----+ c1 c2 c3 s1 s2 s3 ----- ---- ---- ---- ---- -----+ 1 6 1 1 1 1 2 9 2 2 2 2 3 NULL 3 3 3 3 4 NULL 1 1 1 1 5 NULL 99 99 99 99 6 2 NULL NULL NULL NULL ----- ---- ---- ---- ---- -----+ 6 rows in set (0.028 sec)

          People

            dleeyh Daniel Lee (Inactive)
            David.Hall David Hall (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.