[MCOL-4116] working_dml/subupd003.sql bad results Created: 2020-06-25  Updated: 2020-07-14  Resolved: 2020-07-10

Status: Closed
Project: MariaDB ColumnStore
Component/s: DMLProc
Affects Version/s: 1.2, 1.5.2
Fix Version/s: 1.5.3

Type: Bug Priority: Major
Reporter: David Hall (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Sprint: 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

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

 Comments   
Comment by David Hall (Inactive) [ 2020-06-25 ]

Be sure to correct the ref file when fixed.

Comment by David Hall (Inactive) [ 2020-07-06 ]

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.

Comment by Gagan Goel (Inactive) [ 2020-07-08 ]

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

Comment by David Hall (Inactive) [ 2020-07-10 ]

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

Comment by Daniel Lee (Inactive) [ 2020-07-10 ]

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)

Comment by Daniel Lee (Inactive) [ 2020-07-10 ]

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)

Generated at Thu Feb 08 02:47:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.