[MCOL-674] Update a column with a subquery resulted NULLs in the target column Created: 2017-04-20  Updated: 2017-07-13  Resolved: 2017-07-13

Status: Closed
Project: MariaDB ColumnStore
Component/s: DMLProc
Affects Version/s: 1.0.8, 1.1.0
Fix Version/s: 1.0.10

Type: Bug Priority: Critical
Reporter: Daniel Lee (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 1
Labels: None

Sprint: 2017-14

 Description   

Build tested: 1.0.8-1 and develop branch
[root@localhost ~]# mcsmysql mytest
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.1.22-MariaDB Columnstore 1.0.8-1

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [mytest]> create table t1 (c1 int, c2 char(5)) engine=columnstore;
Query OK, 0 rows affected (0.21 sec)

MariaDB [mytest]> create table t2 (c1 int, c2 char(5)) engine=columnstore;
Query OK, 0 rows affected (0.15 sec)

MariaDB [mytest]> insert into t1 values (5, 'aa');
Query OK, 1 row affected (0.13 sec)

MariaDB [mytest]> insert into t1 values (5, 'aa');
Query OK, 1 row affected (0.06 sec)

MariaDB [mytest]> insert into t1 values (5, 'aa');
Query OK, 1 row affected (0.06 sec)

MariaDB [mytest]> insert into t1 values (5, 'aa');
Query OK, 1 row affected (0.05 sec)

MariaDB [mytest]> insert into t1 values (5, 'aa');
Query OK, 1 row affected (0.06 sec)

MariaDB [mytest]> insert into t2 values (1, 'aa');
Query OK, 1 row affected (0.09 sec)

MariaDB [mytest]> insert into t2 values (1, 'aa');
Query OK, 1 row affected (0.06 sec)

MariaDB [mytest]> insert into t2 values (1, 'aa');
Query OK, 1 row affected (0.06 sec)

MariaDB [mytest]> insert into t2 values (1, 'aa');
Query OK, 1 row affected (0.07 sec)

MariaDB [mytest]> insert into t2 values (1, 'aa');
Query OK, 1 row affected (0.05 sec)

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

c1 c2

----------+

5 aa
5 aa
5 aa
5 aa
5 aa

----------+
5 rows in set (0.04 sec)

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

c1 c2

----------+

1 aa
1 aa
1 aa
1 aa
1 aa

----------+
5 rows in set (0.02 sec)

MariaDB [mytest]> update t1 set t1.c1=(select distinct c1 from t2);
Query OK, 5 rows affected (0.07 sec)
Rows matched: 0 Changed: 0 Warnings: 0

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

c1 c2

----------+

NULL aa
NULL aa
NULL aa
NULL aa
NULL aa

----------+
5 rows in set (0.02 sec)

MariaDB [mytest]> update t1 set c1=5;
Query OK, 5 rows affected (0.11 sec)
Rows matched: 0 Changed: 0 Warnings: 0

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

c1 c2

----------+

5 aa
5 aa
5 aa
5 aa
5 aa

----------+
5 rows in set (0.02 sec)

MariaDB [mytest]> update t1 set t1.c1=(select c1 from t2 limit 1);
Query OK, 5 rows affected, 1 warning (0.07 sec)
Rows matched: 0 Changed: 0 Warnings: 1

MariaDB [mytest]> show warnings;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Level Code Message

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

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

c1 c2

----------+

NULL aa
NULL aa
NULL aa
NULL aa
NULL aa

----------+
5 rows in set (0.02 sec)

MariaDB [mytest]>



 Comments   
Comment by Daniel Lee (Inactive) [ 2017-04-20 ]

the same issue occurs when distinct and limit 1 is not used. Could it be update with a subquery instead?

MariaDB [mytest]> update text1 t1 set t1.c1=(select c1 from text2);
Query OK, 5 rows affected (0.07 sec)
Rows matched: 0 Changed: 0 Warnings: 0

MariaDB [mytest]> select c1 from text1;
------

c1

------

NULL
NULL
NULL
NULL
NULL

------
5 rows in set (0.02 sec)

MariaDB [mytest]> select c1 from text2;
------

c1

------

1

------
1 row in set (0.01 sec)

Comment by Daniel Lee (Inactive) [ 2017-04-20 ]

update using table join worked:

update text1 t1, text2 t2 set t1.c1+10=t2.c1 where t1.c1 = t2.c1;

Comment by Andrew Hutchings (Inactive) [ 2017-07-11 ]

Pull request for 1.0 and 1.1.

Comment by Daniel Lee (Inactive) [ 2017-07-13 ]

Build verified: Github source 1.0.10

[root@localhost mariadb-columnstore-server]# git show
commit 435972e50ee33911ce39696ce101d1cd23ed9c2b
Merge: b1d1ca1 5d3fcfe
Author: David.Hall <david.hall@mariadb.com>
Date: Wed Jul 12 13:07:55 2017 -0500

[root@localhost mariadb-columnstore-engine]# git show
commit 3501c1a17a920ee765c6255c5a5fd8c64fed7c8e
Author: david hill <david.hill@mariadb.com>
Date: Wed Jul 12 09:52:28 2017 -0500

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