[MCOL-4303] UPDATE..SET using another table is not updating Created: 2020-09-09  Updated: 2020-11-12  Resolved: 2020-09-15

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.0.0
Fix Version/s: 5.4.1

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

Sprint: 2020-8

 Description   

Build: 595
artefacts: https://cspkg.s3.amazonaws.com/index.html?prefix=develop-1.5/pull_request/595/centos7/
pipeline: https://ci.columnstore.mariadb.net/mariadb-corporation/mariadb-columnstore-engine/595/2/7

Repro:

CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL) ENGINE=Columnstore;
 
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(2,1),(2,2),(2,3);
 
CREATE TABLE t2 (a INT NOT NULL, b INT NOT NULL) ENGINE=Columnstore;
 
INSERT INTO t2 VALUES (1,1),(1,2),(1,3);
 
UPDATE t1 SET b=(SELECT b FROM t2 ORDER BY b LIMIT 1);
 
 
 
SELECT * FROM t1 ORDER BY a, b;


+---+---+
 
| a | b |
 
+---+---+
 
| 1 | 1 |
 
| 1 | 2 |
 
| 1 | 3 |
 
| 2 | 1 |
 
| 2 | 2 |
 
| 2 | 3 |
 
+---+---+
 
6 rows in set (0.024 sec)

Innodb:

SELECT * FROM t1 ORDER BY a, b;


+---+---+
 
| a | b |
 
+---+---+
 
| 1 | 1 |
 
| 1 | 1 |
 
| 1 | 1 |
 
| 2 | 1 |
 
| 2 | 1 |
 
| 2 | 1 |
 
+---+---+
 
6 rows in set (0.000 sec)

Older build 518 results matched with Innodb and so its a regression.

 Comments   
Comment by Daniel Lee (Inactive) [ 2020-09-15 ]

Build verified: 1.5.4-1 (drone 631)

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.5-3-MariaDB-enterprise MariaDB Enterprise Server

Copyright (c) 2000, 2018, 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 (a INT NOT NULL, b INT NOT NULL) ENGINE=Columnstore;
Query OK, 0 rows affected (0.222 sec)

MariaDB [mytest]> INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(2,1),(2,2),(2,3);
Query OK, 6 rows affected (0.270 sec)
Records: 6 Duplicates: 0 Warnings: 0

MariaDB [mytest]> CREATE TABLE t2 (a INT NOT NULL, b INT NOT NULL) ENGINE=Columnstore;
Query OK, 0 rows affected (0.414 sec)

MariaDB [mytest]> INSERT INTO t2 VALUES (1,1),(1,2),(1,3);
Query OK, 3 rows affected (0.337 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [mytest]> UPDATE t1 SET b=(SELECT b FROM t2 ORDER BY b LIMIT 1);
Query OK, 6 rows affected (0.339 sec)
Rows matched: 6 Changed: 6 Warnings: 0

MariaDB [mytest]>
MariaDB [mytest]> SELECT * FROM t1 ORDER BY a, b;
----+

a b

----+

1 1
1 1
1 1
2 1
2 1
2 1

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

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