mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-28 06:45:06 +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.
55 lines
2.5 KiB
Plaintext
55 lines
2.5 KiB
Plaintext
#
|
|
# Test aliases with Columnstore engine
|
|
# Author: Bharath, bharath.bokka@mariadb.com
|
|
#
|
|
-- source ../include/have_columnstore.inc
|
|
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS mcs78_db;
|
|
--enable_warnings
|
|
|
|
CREATE DATABASE mcs78_db;
|
|
USE mcs78_db;
|
|
|
|
CREATE TABLE t1
|
|
(
|
|
t1_tinyint TINYINT DEFAULT 0,
|
|
t1_int INT DEFAULT NULL,
|
|
t1_bigint BIGINT,
|
|
t1_decimal DECIMAL(5,2),
|
|
t1_text TEXT,
|
|
t1_char CHAR(5),
|
|
t1_varchar VARCHAR(255) DEFAULT 'hello world!',
|
|
t1_datetime DATETIME
|
|
)ENGINE=Columnstore;
|
|
INSERT INTO t1 VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
|
|
INSERT INTO t1 VALUES(1, 11, 987654321, 122.32, repeat('z', 20), 'aaa', repeat('a', 20), '1111-11-09 11:11:11');
|
|
INSERT INTO t1 VALUES(0, 12, 887654321, 222.32, repeat('y', 20), 'aaa', repeat('b', 20), '1111-11-11 11:11:12');
|
|
INSERT INTO t1 VALUES(1, -12, -987654321, -122.32, repeat('z', 20), 'bbb', repeat('a', 20), '1111-11-10 11:11:10');
|
|
INSERT INTO t1 VALUES(0, -11, -887654321, -222.32, repeat('x', 20), 'bbb', repeat('b', 20), '1111-11-1 11:11:19');
|
|
|
|
SELECT * FROM t1;
|
|
|
|
SELECT COUNT(*) AS 'Total Rows' FROM t1;
|
|
SELECT SUM(t1_int*t1_int) AS 'Sum of Squares of Integer values' FROM t1;
|
|
SELECT t1_int 'Serial no', t1_text 'Name of the Book', t1_datetime 'Publish Date' FROM t1;
|
|
SELECT CONCAT(t1_char, t1_text, t1_varchar) 'Concatinating String Columns' FROM t1;
|
|
SELECT t1_tinyint AS 'Boolean value' FROM t1;
|
|
--disable_warnings
|
|
SELECT t1_tinyint 'Tinyint', t1_bigint 'Big Int', t1_decimal 'Decimal', t1_text AS 'Text', t1_varchar AS 'Varchar', t1_datetime AS 'Date' FROM t1 WHERE 'Decimal' >= -300 ORDER BY 'Date';
|
|
SELECT COUNT(*) AS 'Total rows' FROM t1 AS table1 , t1 AS table2 WHERE table1.t1_int = table2.t1_int;
|
|
--enable_warnings
|
|
SELECT table1.t1_tinyint, table2.t1_int FROM t1 AS table1 JOIN t1 AS table2 ON table1.t1_int = table2.t1_int;
|
|
|
|
CREATE TABLE t2(col1 INT, col2 TEXT)ENGINE=Columnstore;
|
|
INSERT INTO t2 VALUES(1, repeat('s',20)),(2, repeat('o',20)),(3, 'sss'),(4, 'ooo');
|
|
CREATE TABLE t3(col1 INT, col2 DATETIME)ENGINE=Columnstore;
|
|
INSERT INTO t3 VALUES(1, '2020-2-2'),(2, '2020-3-3');
|
|
|
|
SELECT table1.col1, table1.col2 AS 'Text from table1', table2.col2 AS 'Date from table2' FROM t2 AS table1 JOIN t3 AS table2 ON table1.col1 = table2.col1;
|
|
SELECT table1.col1, table1.col2 AS 'Text from table1', table2.col2 AS 'Date from table2' FROM t2 AS table1 LEFT JOIN t3 AS table2 ON table1.col1 = table2.col1;
|
|
SELECT table1.col1, table1.col2 AS 'Text from table1', table2.col2 AS 'Date from table2' FROM t2 AS table1 RIGHT JOIN t3 AS table2 ON table1.col1 = table2.col1;
|
|
|
|
# Clean UP
|
|
DROP DATABASE mcs78_db;
|