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.
68 lines
2.0 KiB
Plaintext
68 lines
2.0 KiB
Plaintext
#
|
|
# Test CHECK Constraint
|
|
# Author: Bharath, bharath.bokka@mariadb.com
|
|
#
|
|
-- source ../include/have_columnstore.inc
|
|
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS mcs74_db;
|
|
--enable_warnings
|
|
|
|
CREATE DATABASE mcs74_db;
|
|
USE mcs74_db;
|
|
|
|
CREATE TABLE t1(t1_int INT, t1_char CHAR(5), CHECK (t1_int > 0))ENGINE=Columnstore;
|
|
--replace_regex /( COLLATE=latin1_swedish_ci)//
|
|
SHOW CREATE TABLE t1;
|
|
INSERT INTO t1 VALUES(NULL, NULL);
|
|
INSERT INTO t1 VALUES(1, 'a');
|
|
--error ER_CONSTRAINT_FAILED
|
|
INSERT INTO t1 VALUES(0, 'b');
|
|
--error ER_CONSTRAINT_FAILED
|
|
INSERT INTO t1 VALUES(-1, 'c');
|
|
SELECT * FROM t1;
|
|
|
|
CREATE TABLE t2(t1_char CHAR(10), CONSTRAINT c1 CHECK(t1_char = ''))ENGINE=Columnstore;
|
|
--replace_regex /( COLLATE=latin1_swedish_ci)//
|
|
SHOW CREATE TABLE t2;
|
|
INSERT INTO t2 VALUES();
|
|
INSERT INTO t2 VALUES('');
|
|
--error ER_CONSTRAINT_FAILED
|
|
INSERT INTO t2 VALUES('aaaa');
|
|
SELECT * FROM t2;
|
|
|
|
CREATE TABLE t3(
|
|
t3_decimal DECIMAL(5,2),
|
|
t3_blob BLOB,
|
|
t3_datetime DATETIME,
|
|
CONSTRAINT t3_check_date CHECK(t3_datetime >= '1212-12-12 12:12:12'),
|
|
CONSTRAINT t3_check_decimal CHECK(t3_decimal < 888.88),
|
|
CONSTRAINT t3_check_blob CHECK(t3_blob = 'aaaa')
|
|
)ENGINE=Columnstore;
|
|
--replace_regex /( COLLATE=latin1_swedish_ci)//
|
|
SHOW CREATE TABLE t3;
|
|
INSERT INTO t3(t3_decimal) VALUES(NULL);
|
|
INSERT INTO t3(t3_decimal) VALUES(888.87);
|
|
--error ER_CONSTRAINT_FAILED
|
|
INSERT INTO t3(t3_decimal) VALUES(888.88);
|
|
--error ER_CONSTRAINT_FAILED
|
|
INSERT INTO t3(t3_decimal) VALUES(888.89);
|
|
--error ER_CONSTRAINT_FAILED
|
|
INSERT INTO t3(t3_blob) VALUES('');
|
|
INSERT INTO t3(t3_blob) VALUES('aaaa');
|
|
--error ER_CONSTRAINT_FAILED
|
|
INSERT INTO t3(t3_blob) VALUES('aaaaa');
|
|
--error ER_CONSTRAINT_FAILED
|
|
INSERT INTO t3(t3_blob) VALUES('aaa');
|
|
INSERT INTO t3(t3_datetime) VALUES(NULL);
|
|
--error ER_CONSTRAINT_FAILED
|
|
INSERT INTO t3(t3_datetime) VALUES('0:0:0');
|
|
INSERT INTO t3(t3_datetime) VALUES('1212-12-12 12:12:12');
|
|
INSERT INTO t3(t3_datetime) VALUES('1212-12-12 12:12:13');
|
|
--error ER_CONSTRAINT_FAILED
|
|
INSERT INTO t3(t3_datetime) VALUES('1212-12-12 12:12:11');
|
|
SELECT * FROM t3;
|
|
|
|
# Clean UP
|
|
DROP DATABASE mcs74_db;
|