|
This update fails in 6.1.1 but succeeds in 5.6.2:
UPDATE test_cs SET b='Update' WHERE a IN (SELECT a FROM test_innodb);
Tested example 6.1.1:
root@logs:~$ mariadb -s
|
MariaDB [(none)]> SHOW STATUS like 'columnstore_version';
|
Variable_name Value
|
Columnstore_version 6.1.1
|
MariaDB [(none)]> create schema test;
|
MariaDB [(none)]> use test;
|
MariaDB [test]> DROP TABLE if EXISTS test_cs;
|
MariaDB [test]> DROP TABLE if EXISTS test_innodb;
|
MariaDB [test]> CREATE TABLE test_cs (a INT,b VARCHAR(100)) ENGINE=columnstore;
|
MariaDB [test]> INSERT INTO test_cs VALUES (1,'Test1'),(2,'Test2'),(3,'Test3'),(4,'Test4');
|
MariaDB [test]> INSERT INTO test_cs VALUES (null,'TestNULL'),(6,NULL),(7,'Test7');
|
MariaDB [test]> CREATE TABLE test_innodb (a INT,b VARCHAR(100));
|
MariaDB [test]> INSERT INTO test_innodb VALUES (1,'innodb1'),(2,'innodb2'),(3,'innodb3');
|
MariaDB [test]>
|
MariaDB [test]> SELECT * from test_cs;
|
a b
|
1 Test1
|
2 Test2
|
3 Test3
|
4 Test4
|
NULL TestNULL
|
6 NULL
|
7 Test7
|
MariaDB [test]> -- this UPDATE did not work - no change/update on the data
|
MariaDB [test]> UPDATE test_cs SET b='Update' WHERE a IN (SELECT a FROM test_innodb);
|
MariaDB [test]> SELECT * from test_cs;
|
a b
|
1 Test1
|
2 Test2
|
3 Test3
|
4 Test4
|
NULL TestNULL
|
6 NULL
|
7 Test7
|
MariaDB [test]> -- this UPDATE is a workaround and work
|
MariaDB [test]> UPDATE test_cs JOIN test_innodb ON (test_cs.a=test_innodb.a) SET test_cs.b='Update';
|
MariaDB [test]> SELECT * from test_cs;
|
a b
|
1 Update
|
2 Update
|
3 Update
|
4 Test4
|
NULL TestNULL
|
6 NULL
|
7 Test7
|
MariaDB [test]>
|
Tested example 5.6.2:
root@logs:~$ mariadb -s
|
MariaDB [(none)]> SHOW STATUS like 'columnstore_version';
|
Variable_name Value
|
Columnstore_version 5.6.2
|
MariaDB [(none)]> create schema test;
|
MariaDB [(none)]> USE test;
|
MariaDB [test]> DROP TABLE if EXISTS test_cs;
|
MariaDB [test]> DROP TABLE if EXISTS test_innodb;
|
MariaDB [test]> CREATE TABLE test_cs (a INT,b VARCHAR(100)) ENGINE=columnstore;
|
MariaDB [test]> INSERT INTO test_cs VALUES (1,'Test1'),(2,'Test2'),(3,'Test3'),(4,'Test4');
|
MariaDB [test]> INSERT INTO test_cs VALUES (null,'TestNULL'),(6,NULL),(7,'Test7');
|
MariaDB [test]> CREATE TABLE test_innodb (a INT,b VARCHAR(100));
|
MariaDB [test]> INSERT INTO test_innodb VALUES (1,'innodb1'),(2,'innodb2'),(3,'innodb3');
|
MariaDB [test]> SELECT * from test_cs;
|
a b
|
1 Test1
|
2 Test2
|
3 Test3
|
4 Test4
|
NULL TestNULL
|
6 NULL
|
7 Test7
|
MariaDB [test]> -- this UPDATE works on version 5.6.2
|
MariaDB [test]> UPDATE test_cs SET b='Update' WHERE a IN (SELECT a FROM test_innodb);
|
MariaDB [test]> SELECT * from test_cs;
|
a b
|
1 Update
|
2 Update
|
3 Update
|
4 Test4
|
NULL TestNULL
|
6 NULL
|
7 Test7
|
MariaDB [test]>
|
|