mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-20 09:07:44 +03:00
b53c231ca6
This patch improves handling of NULLs in textual fields in ColumnStore. Previously empty strings were considered NULLs and it could be a problem if data scheme allows for empty strings. It was also one of major reasons of behavior difference between ColumnStore and other engines in MariaDB family. Also, this patch fixes some other bugs and incorrect behavior, for example, incorrect comparison for "column <= ''" which evaluates to constant True for all purposes before this patch.
290 lines
10 KiB
Plaintext
290 lines
10 KiB
Plaintext
DROP DATABASE IF EXISTS mcs93_db;
|
|
CREATE DATABASE mcs93_db;
|
|
USE mcs93_db;
|
|
CREATE TABLE t1(col1 VARCHAR(40), col2 TEXT)ENGINE=Columnstore;
|
|
INSERT INTO t1 VALUES(NULL, NULL);
|
|
INSERT INTO t1 VALUES('', '');
|
|
INSERT INTO t1 VALUES(' aaa', repeat('z',10));
|
|
INSERT INTO t1 VALUES('klm, nopqrst', 'abcdefghijklmno, pqrsuvwxyz ');
|
|
INSERT INTO t1 VALUES('MariaDB, Columnstore', 'mariadb, COLUMNSTORE');
|
|
SELECT col1 FROM t1;
|
|
col1
|
|
NULL
|
|
|
|
aaa
|
|
klm, nopqrst
|
|
MariaDB, Columnstore
|
|
UPDATE t1 SET col1 = LTRIM(col1);
|
|
SELECT col1 FROM t1;
|
|
col1
|
|
NULL
|
|
|
|
aaa
|
|
klm, nopqrst
|
|
MariaDB, Columnstore
|
|
SELECT col2 FROM t1;
|
|
col2
|
|
NULL
|
|
|
|
zzzzzzzzzz
|
|
abcdefghijklmno, pqrsuvwxyz
|
|
mariadb, COLUMNSTORE
|
|
UPDATE t1 SET col2 = RTRIM(col2);
|
|
SELECT col2 FROM t1;
|
|
col2
|
|
NULL
|
|
|
|
zzzzzzzzzz
|
|
abcdefghijklmno, pqrsuvwxyz
|
|
mariadb, COLUMNSTORE
|
|
INSERT INTO t1 VALUES(' Columnstore Engine ', ' COLUMNSTORE ENGINE ');
|
|
SELECT TRIM(TRAILING FROM col1), TRIM(LEADING FROM col1) FROM t1;
|
|
TRIM(TRAILING FROM col1) TRIM(LEADING FROM col1)
|
|
NULL NULL
|
|
|
|
aaa aaa
|
|
klm, nopqrst klm, nopqrst
|
|
MariaDB, Columnstore MariaDB, Columnstore
|
|
Columnstore Engine Columnstore Engine
|
|
SELECT TRIM(TRAILING FROM col2), TRIM(LEADING FROM col2) FROM t1;
|
|
TRIM(TRAILING FROM col2) TRIM(LEADING FROM col2)
|
|
NULL NULL
|
|
|
|
zzzzzzzzzz zzzzzzzzzz
|
|
abcdefghijklmno, pqrsuvwxyz abcdefghijklmno, pqrsuvwxyz
|
|
mariadb, COLUMNSTORE mariadb, COLUMNSTORE
|
|
COLUMNSTORE ENGINE COLUMNSTORE ENGINE
|
|
SELECT * FROM t1;
|
|
col1 col2
|
|
NULL NULL
|
|
|
|
aaa zzzzzzzzzz
|
|
klm, nopqrst abcdefghijklmno, pqrsuvwxyz
|
|
MariaDB, Columnstore mariadb, COLUMNSTORE
|
|
Columnstore Engine COLUMNSTORE ENGINE
|
|
UPDATE t1 SET col1 = TRIM(col1);
|
|
UPDATE t1 SET col2 = TRIM(col2);
|
|
SELECT * FROM t1;
|
|
col1 col2
|
|
NULL NULL
|
|
|
|
aaa zzzzzzzzzz
|
|
klm, nopqrst abcdefghijklmno, pqrsuvwxyz
|
|
MariaDB, Columnstore mariadb, COLUMNSTORE
|
|
Columnstore Engine COLUMNSTORE ENGINE
|
|
SELECT col1, col2, CONCAT(col1,col2) FROM t1;
|
|
col1 col2 CONCAT(col1,col2)
|
|
NULL NULL NULL
|
|
|
|
aaa zzzzzzzzzz aaazzzzzzzzzz
|
|
klm, nopqrst abcdefghijklmno, pqrsuvwxyz klm, nopqrstabcdefghijklmno, pqrsuvwxyz
|
|
MariaDB, Columnstore mariadb, COLUMNSTORE MariaDB, Columnstoremariadb, COLUMNSTORE
|
|
Columnstore Engine COLUMNSTORE ENGINE Columnstore EngineCOLUMNSTORE ENGINE
|
|
SELECT col1, col2, CONCAT(col1, ' ', col2) FROM t1;
|
|
col1 col2 CONCAT(col1, ' ', col2)
|
|
NULL NULL NULL
|
|
|
|
aaa zzzzzzzzzz aaa zzzzzzzzzz
|
|
klm, nopqrst abcdefghijklmno, pqrsuvwxyz klm, nopqrst abcdefghijklmno, pqrsuvwxyz
|
|
MariaDB, Columnstore mariadb, COLUMNSTORE MariaDB, Columnstore mariadb, COLUMNSTORE
|
|
Columnstore Engine COLUMNSTORE ENGINE Columnstore Engine COLUMNSTORE ENGINE
|
|
SELECT col1, col2, CONCAT('Concat', col1, ' ', col2) FROM t1;
|
|
col1 col2 CONCAT('Concat', col1, ' ', col2)
|
|
NULL NULL NULL
|
|
Concat
|
|
aaa zzzzzzzzzz Concataaa zzzzzzzzzz
|
|
klm, nopqrst abcdefghijklmno, pqrsuvwxyz Concatklm, nopqrst abcdefghijklmno, pqrsuvwxyz
|
|
MariaDB, Columnstore mariadb, COLUMNSTORE ConcatMariaDB, Columnstore mariadb, COLUMNSTORE
|
|
Columnstore Engine COLUMNSTORE ENGINE ConcatColumnstore Engine COLUMNSTORE ENGINE
|
|
SELECT col1, col2, CONCAT(col2, ' string123# ', col1) FROM t1;
|
|
col1 col2 CONCAT(col2, ' string123# ', col1)
|
|
NULL NULL NULL
|
|
string123#
|
|
aaa zzzzzzzzzz zzzzzzzzzz string123# aaa
|
|
klm, nopqrst abcdefghijklmno, pqrsuvwxyz abcdefghijklmno, pqrsuvwxyz string123# klm, nopqrst
|
|
MariaDB, Columnstore mariadb, COLUMNSTORE mariadb, COLUMNSTORE string123# MariaDB, Columnstore
|
|
Columnstore Engine COLUMNSTORE ENGINE COLUMNSTORE ENGINE string123# Columnstore Engine
|
|
SELECT col1, col2, CONCAT_WS(',', col2, col1) FROM t1;
|
|
col1 col2 CONCAT_WS(',', col2, col1)
|
|
NULL NULL NULL
|
|
,
|
|
aaa zzzzzzzzzz zzzzzzzzzz,aaa
|
|
klm, nopqrst abcdefghijklmno, pqrsuvwxyz abcdefghijklmno, pqrsuvwxyz,klm, nopqrst
|
|
MariaDB, Columnstore mariadb, COLUMNSTORE mariadb, COLUMNSTORE,MariaDB, Columnstore
|
|
Columnstore Engine COLUMNSTORE ENGINE COLUMNSTORE ENGINE,Columnstore Engine
|
|
SELECT col1, col2, CONCAT_WS('....', col2, col1) FROM t1;
|
|
col1 col2 CONCAT_WS('....', col2, col1)
|
|
NULL NULL NULL
|
|
....
|
|
aaa zzzzzzzzzz zzzzzzzzzz....aaa
|
|
klm, nopqrst abcdefghijklmno, pqrsuvwxyz abcdefghijklmno, pqrsuvwxyz....klm, nopqrst
|
|
MariaDB, Columnstore mariadb, COLUMNSTORE mariadb, COLUMNSTORE....MariaDB, Columnstore
|
|
Columnstore Engine COLUMNSTORE ENGINE COLUMNSTORE ENGINE....Columnstore Engine
|
|
SELECT col1, col2, CONCAT_WS(' string123# ', col2, col1) FROM t1;
|
|
col1 col2 CONCAT_WS(' string123# ', col2, col1)
|
|
NULL NULL NULL
|
|
string123#
|
|
aaa zzzzzzzzzz zzzzzzzzzz string123# aaa
|
|
klm, nopqrst abcdefghijklmno, pqrsuvwxyz abcdefghijklmno, pqrsuvwxyz string123# klm, nopqrst
|
|
MariaDB, Columnstore mariadb, COLUMNSTORE mariadb, COLUMNSTORE string123# MariaDB, Columnstore
|
|
Columnstore Engine COLUMNSTORE ENGINE COLUMNSTORE ENGINE string123# Columnstore Engine
|
|
SELECT col1, col2, INSTR(col1, 'o') FROM t1;
|
|
col1 col2 INSTR(col1, 'o')
|
|
NULL NULL NULL
|
|
0
|
|
aaa zzzzzzzzzz 0
|
|
klm, nopqrst abcdefghijklmno, pqrsuvwxyz 7
|
|
MariaDB, Columnstore mariadb, COLUMNSTORE 11
|
|
Columnstore Engine COLUMNSTORE ENGINE 2
|
|
SELECT col1, LENGTH(col1), col2, LENGTH(col2) FROM t1;
|
|
col1 LENGTH(col1) col2 LENGTH(col2)
|
|
NULL NULL NULL NULL
|
|
0 0
|
|
aaa 3 zzzzzzzzzz 10
|
|
klm, nopqrst 12 abcdefghijklmno, pqrsuvwxyz 27
|
|
MariaDB, Columnstore 20 mariadb, COLUMNSTORE 20
|
|
Columnstore Engine 18 COLUMNSTORE ENGINE 18
|
|
SELECT col1, CHAR_LENGTH(col1), col2, CHAR_LENGTH(col2) FROM t1;
|
|
col1 CHAR_LENGTH(col1) col2 CHAR_LENGTH(col2)
|
|
NULL NULL NULL NULL
|
|
0 0
|
|
aaa 3 zzzzzzzzzz 10
|
|
klm, nopqrst 12 abcdefghijklmno, pqrsuvwxyz 27
|
|
MariaDB, Columnstore 20 mariadb, COLUMNSTORE 20
|
|
Columnstore Engine 18 COLUMNSTORE ENGINE 18
|
|
SET @s = CONVERT('Mariadb Coumnstore' USING ucs2);
|
|
SELECT CHAR_LENGTH(@s), LENGTH(@s);
|
|
CHAR_LENGTH(@s) LENGTH(@s)
|
|
18 36
|
|
SELECT col1, LEFT(col1, 1), col2, LEFT(col2, 2) FROM t1;
|
|
col1 LEFT(col1, 1) col2 LEFT(col2, 2)
|
|
NULL NULL NULL NULL
|
|
|
|
aaa a zzzzzzzzzz zz
|
|
klm, nopqrst k abcdefghijklmno, pqrsuvwxyz ab
|
|
MariaDB, Columnstore M mariadb, COLUMNSTORE ma
|
|
Columnstore Engine C COLUMNSTORE ENGINE CO
|
|
SELECT col1, LEFT(col1,100), col2, LEFT(col2, 100) FROM t1;
|
|
col1 LEFT(col1,100) col2 LEFT(col2, 100)
|
|
NULL NULL NULL NULL
|
|
|
|
aaa aaa zzzzzzzzzz zzzzzzzzzz
|
|
klm, nopqrst klm, nopqrst abcdefghijklmno, pqrsuvwxyz abcdefghijklmno, pqrsuvwxyz
|
|
MariaDB, Columnstore MariaDB, Columnstore mariadb, COLUMNSTORE mariadb, COLUMNSTORE
|
|
Columnstore Engine Columnstore Engine COLUMNSTORE ENGINE COLUMNSTORE ENGINE
|
|
SELECT col1, RIGHT(col1, 2), col2, RIGHT(col2, 2) FROM t1;
|
|
col1 RIGHT(col1, 2) col2 RIGHT(col2, 2)
|
|
NULL NULL NULL NULL
|
|
|
|
aaa aa zzzzzzzzzz zz
|
|
klm, nopqrst st abcdefghijklmno, pqrsuvwxyz yz
|
|
MariaDB, Columnstore re mariadb, COLUMNSTORE RE
|
|
Columnstore Engine ne COLUMNSTORE ENGINE NE
|
|
SELECT col1, RIGHT(col1, 11), col2, RIGHT(col2, 11) FROM t1;
|
|
col1 RIGHT(col1, 11) col2 RIGHT(col2, 11)
|
|
NULL NULL NULL NULL
|
|
|
|
aaa aaa zzzzzzzzzz zzzzzzzzzz
|
|
klm, nopqrst lm, nopqrst abcdefghijklmno, pqrsuvwxyz pqrsuvwxyz
|
|
MariaDB, Columnstore Columnstore mariadb, COLUMNSTORE COLUMNSTORE
|
|
Columnstore Engine tore Engine COLUMNSTORE ENGINE TORE ENGINE
|
|
SELECT LOWER(col1), LOWER(col2) FROM t1;
|
|
LOWER(col1) LOWER(col2)
|
|
NULL NULL
|
|
|
|
aaa zzzzzzzzzz
|
|
klm, nopqrst abcdefghijklmno, pqrsuvwxyz
|
|
mariadb, columnstore mariadb, columnstore
|
|
columnstore engine columnstore engine
|
|
SELECT LCASE(col1), LCASE(col2) FROM t1;
|
|
LCASE(col1) LCASE(col2)
|
|
NULL NULL
|
|
|
|
aaa zzzzzzzzzz
|
|
klm, nopqrst abcdefghijklmno, pqrsuvwxyz
|
|
mariadb, columnstore mariadb, columnstore
|
|
columnstore engine columnstore engine
|
|
SELECT UPPER(col1), UPPER(col2) FROM t1;
|
|
UPPER(col1) UPPER(col2)
|
|
NULL NULL
|
|
|
|
AAA ZZZZZZZZZZ
|
|
KLM, NOPQRST ABCDEFGHIJKLMNO, PQRSUVWXYZ
|
|
MARIADB, COLUMNSTORE MARIADB, COLUMNSTORE
|
|
COLUMNSTORE ENGINE COLUMNSTORE ENGINE
|
|
SELECT UCASE(col1), UCASE(col2) FROM t1;
|
|
UCASE(col1) UCASE(col2)
|
|
NULL NULL
|
|
|
|
AAA ZZZZZZZZZZ
|
|
KLM, NOPQRST ABCDEFGHIJKLMNO, PQRSUVWXYZ
|
|
MARIADB, COLUMNSTORE MARIADB, COLUMNSTORE
|
|
COLUMNSTORE ENGINE COLUMNSTORE ENGINE
|
|
SELECT col1, SUBSTRING(col1, 5), col2, SUBSTRING(col2, 8) FROM t1;
|
|
col1 SUBSTRING(col1, 5) col2 SUBSTRING(col2, 8)
|
|
NULL NULL NULL NULL
|
|
|
|
aaa zzzzzzzzzz zzz
|
|
klm, nopqrst nopqrst abcdefghijklmno, pqrsuvwxyz hijklmno, pqrsuvwxyz
|
|
MariaDB, Columnstore aDB, Columnstore mariadb, COLUMNSTORE , COLUMNSTORE
|
|
Columnstore Engine mnstore Engine COLUMNSTORE ENGINE TORE ENGINE
|
|
SELECT col1, SUBSTRING(col1, 5, 4), col2, SUBSTRING(col2, 8, 3) FROM t1;
|
|
col1 SUBSTRING(col1, 5, 4) col2 SUBSTRING(col2, 8, 3)
|
|
NULL NULL NULL NULL
|
|
|
|
aaa zzzzzzzzzz zzz
|
|
klm, nopqrst nop abcdefghijklmno, pqrsuvwxyz hij
|
|
MariaDB, Columnstore aDB, mariadb, COLUMNSTORE , C
|
|
Columnstore Engine mnst COLUMNSTORE ENGINE TOR
|
|
SELECT col1, SUBSTRING(col1, -5), col2, SUBSTRING(col2, -8) FROM t1;
|
|
col1 SUBSTRING(col1, -5) col2 SUBSTRING(col2, -8)
|
|
NULL NULL NULL NULL
|
|
|
|
aaa zzzzzzzzzz zzzzzzzz
|
|
klm, nopqrst pqrst abcdefghijklmno, pqrsuvwxyz rsuvwxyz
|
|
MariaDB, Columnstore store mariadb, COLUMNSTORE UMNSTORE
|
|
Columnstore Engine ngine COLUMNSTORE ENGINE E ENGINE
|
|
SELECT col1, SUBSTRING(col1, -5, 4), col2, SUBSTRING(col2, -8, 3) FROM t1;
|
|
col1 SUBSTRING(col1, -5, 4) col2 SUBSTRING(col2, -8, 3)
|
|
NULL NULL NULL NULL
|
|
|
|
aaa zzzzzzzzzz zzz
|
|
klm, nopqrst pqrs abcdefghijklmno, pqrsuvwxyz rsu
|
|
MariaDB, Columnstore stor mariadb, COLUMNSTORE UMN
|
|
Columnstore Engine ngin COLUMNSTORE ENGINE E E
|
|
SELECT col1, SUBSTRING_INDEX(col1, 'o', 2), col2, SUBSTRING_INDEX(col2, 'o', 2) FROM t1;
|
|
col1 SUBSTRING_INDEX(col1, 'o', 2) col2 SUBSTRING_INDEX(col2, 'o', 2)
|
|
NULL NULL NULL NULL
|
|
|
|
aaa aaa zzzzzzzzzz zzzzzzzzzz
|
|
klm, nopqrst klm, nopqrst abcdefghijklmno, pqrsuvwxyz abcdefghijklmno, pqrsuvwxyz
|
|
MariaDB, Columnstore MariaDB, Columnst mariadb, COLUMNSTORE mariadb, COLUMNSTORE
|
|
Columnstore Engine Columnst COLUMNSTORE ENGINE COLUMNSTORE ENGINE
|
|
SELECT col1, SUBSTRING_INDEX(col1, 'O', 2), col2, SUBSTRING_INDEX(col2, 'O', 2) FROM t1;
|
|
col1 SUBSTRING_INDEX(col1, 'O', 2) col2 SUBSTRING_INDEX(col2, 'O', 2)
|
|
NULL NULL NULL NULL
|
|
|
|
aaa aaa zzzzzzzzzz zzzzzzzzzz
|
|
klm, nopqrst klm, nopqrst abcdefghijklmno, pqrsuvwxyz abcdefghijklmno, pqrsuvwxyz
|
|
MariaDB, Columnstore MariaDB, Columnstore mariadb, COLUMNSTORE mariadb, COLUMNST
|
|
Columnstore Engine Columnstore Engine COLUMNSTORE ENGINE COLUMNST
|
|
SELECT col1, FIND_IN_SET('mariadb', col1), col2, FIND_IN_SET('mariadb', col2) FROM t1;
|
|
col1 FIND_IN_SET('mariadb', col1) col2 FIND_IN_SET('mariadb', col2)
|
|
NULL NULL NULL NULL
|
|
0 0
|
|
aaa 0 zzzzzzzzzz 0
|
|
klm, nopqrst 0 abcdefghijklmno, pqrsuvwxyz 0
|
|
MariaDB, Columnstore 1 mariadb, COLUMNSTORE 1
|
|
Columnstore Engine 0 COLUMNSTORE ENGINE 0
|
|
DELETE FROM t1;
|
|
INSERT INTO t1 VALUES('75226.62', '96216681'),('27618', '61971811');
|
|
SELECT col1, FORMAT(col1, 2), FORMAT(col1,10) FROM t1;
|
|
col1 FORMAT(col1, 2) FORMAT(col1,10)
|
|
75226.62 75,226.62 75,226.6200000000
|
|
27618 27,618.00 27,618.0000000000
|
|
SELECT col2, FORMAT(col2, 2), FORMAT(col2,10) FROM t1;
|
|
col2 FORMAT(col2, 2) FORMAT(col2,10)
|
|
96216681 96,216,681.00 96,216,681.0000000000
|
|
61971811 61,971,811.00 61,971,811.0000000000
|
|
DROP DATABASE mcs93_db;
|