mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-21 19:45:56 +03:00
231 lines
17 KiB
Plaintext
231 lines
17 KiB
Plaintext
-- source ../include/have_columnstore.inc
|
|
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS mcol641_insert_db;
|
|
--enable_warnings
|
|
|
|
CREATE DATABASE mcol641_insert_db;
|
|
USE mcol641_insert_db;
|
|
|
|
# Tests for inserts, as well as INSERT ... SELECT and LDI.
|
|
SET SQL_MODE='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
|
|
|
|
CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(38,10), d3 DECIMAL(38,38),
|
|
d4 DECIMAL(19), d5 DECIMAL(19,10), d6 DECIMAL(19,19)) ENGINE=columnstore;
|
|
CREATE TABLE cs2 (d1 DECIMAL(38) UNSIGNED, d2 DECIMAL(38,10) UNSIGNED, d3 DECIMAL(38,38) UNSIGNED,
|
|
d4 DECIMAL(19) UNSIGNED, d5 DECIMAL(19,10) UNSIGNED, d6 DECIMAL(19,19) UNSIGNED) ENGINE=columnstore;
|
|
# cs3 is same as cs1, used as the destination of INSERT...SELECT
|
|
CREATE TABLE cs3 (d1 DECIMAL(38), d2 DECIMAL(38,10), d3 DECIMAL(38,38),
|
|
d4 DECIMAL(19), d5 DECIMAL(19,10), d6 DECIMAL(19,19)) ENGINE=columnstore;
|
|
# cs4 is same as cs2, used as the destination of INSERT...SELECT
|
|
CREATE TABLE cs4 (d1 DECIMAL(38) UNSIGNED, d2 DECIMAL(38,10) UNSIGNED, d3 DECIMAL(38,38) UNSIGNED,
|
|
d4 DECIMAL(19) UNSIGNED, d5 DECIMAL(19,10) UNSIGNED, d6 DECIMAL(19,19) UNSIGNED) ENGINE=columnstore;
|
|
|
|
#
|
|
# single value inserts
|
|
#
|
|
# simple inserts
|
|
INSERT INTO cs1 VALUES (125, 1.25, 0.125, 125, 1.25, 0.125);
|
|
INSERT INTO cs1 VALUES (-125, -1.25, -0.125, -125, -1.25, -0.125);
|
|
INSERT INTO cs1 VALUES (1.25, 125, 125, 1.25, 125, 125);
|
|
INSERT INTO cs1 VALUES (-1.25, -125, -125, -1.25, -125, -125);
|
|
|
|
# insert maximum positive and negative values
|
|
INSERT INTO cs1 values (99999999999999999999999999999999999998, 9999999999999999999999999999.9999999998, 0.99999999999999999999999999999999999998, 9999999999999999998, 999999999.9999999998, 0.9999999999999999998);
|
|
INSERT INTO cs1 values (99999999999999999999999999999999999999, 9999999999999999999999999999.9999999999, 0.99999999999999999999999999999999999999, 9999999999999999999, 999999999.9999999999, 0.9999999999999999999);
|
|
INSERT INTO cs1 values (-99999999999999999999999999999999999998, -9999999999999999999999999999.9999999998, -0.99999999999999999999999999999999999998, -9999999999999999998, -999999999.9999999998, -0.9999999999999999998);
|
|
INSERT INTO cs1 values (-99999999999999999999999999999999999999, -9999999999999999999999999999.9999999999, -0.99999999999999999999999999999999999999, -9999999999999999999, -999999999.9999999999, -0.9999999999999999999);
|
|
|
|
# check saturation of positive and negative values
|
|
INSERT INTO cs1 VALUES (123456789012345678901234567890123456789, 12345678901234567890123456789.12345678912, 0.123456789012345678901234567890123456789, 12345678901234567890, 1234567890.12345678901, 0.12345678901234567891);
|
|
INSERT INTO cs1 VALUES (-123456789012345678901234567890123456789, -12345678901234567890123456789.12345678912, -0.123456789012345678901234567890123456789, -12345678901234567890, -1234567890.12345678901, -0.12345678901234567891);
|
|
|
|
# check rounding when scale > 0
|
|
INSERT INTO cs1 VALUES (0, 1.12345678919, 0.123456789012345678901234567890123456789, 0, 1.12345678919, 0.12345678901234567899);
|
|
INSERT INTO cs1 VALUES (0, 1.12345678914, 0.123456789012345678901234567890123456784, 0, 1.12345678914, 0.12345678901234567894);
|
|
INSERT INTO cs1 VALUES (0, 1.99999999999, 0.999999999999999999999999999999999999999, 0, 1.99999999999, 0.99999999999999999999);
|
|
INSERT INTO cs1 VALUES (0, 1.99999999994, 0.999999999999999999999999999999999999994, 0, 1.99999999994, 0.99999999999999999994);
|
|
INSERT INTO cs1 VALUES (0, -1.12345678919, -0.123456789012345678901234567890123456789, 0, -1.12345678919, -0.12345678901234567899);
|
|
INSERT INTO cs1 VALUES (0, -1.12345678914, -0.123456789012345678901234567890123456784, 0, -1.12345678914, -0.12345678901234567894);
|
|
INSERT INTO cs1 VALUES (0, -1.99999999999, -0.999999999999999999999999999999999999999, 0, -1.99999999999, -0.99999999999999999999);
|
|
INSERT INTO cs1 VALUES (0, -1.99999999994, -0.999999999999999999999999999999999999994, 0, -1.99999999994, -0.99999999999999999994);
|
|
|
|
# insert values with leading zeros in the scale
|
|
INSERT INTO cs1 VALUES (0, 1.0000000009, 0.00000000000000000000000000000000000009, 0, 1.0000000009, 0.0000000000000000009);
|
|
INSERT INTO cs1 VALUES (0, 1.00000000009, 0.000000000000000000000000000000000000009, 0, 1.00000000009, 0.00000000000000000009);
|
|
INSERT INTO cs1 VALUES (0, -1.0000000009, -0.00000000000000000000000000000000000009, 0, -1.0000000009, -0.0000000000000000009);
|
|
INSERT INTO cs1 VALUES (0, -1.00000000009, -0.000000000000000000000000000000000000009, 0, -1.00000000009, -0.00000000000000000009);
|
|
|
|
# insert all zeros
|
|
INSERT INTO cs1 VALUES (0, 0, 0, 0, 0, 0);
|
|
|
|
# insert all nulls
|
|
INSERT INTO cs1 VALUES (NULL, NULL, NULL, NULL, NULL, NULL);
|
|
INSERT INTO cs1 VALUES ();
|
|
SELECT "test1", d1, d2, d3, d4, d5, d6 FROM cs1;
|
|
|
|
#
|
|
# multi-value inserts. repeat queries for single-value inserts
|
|
#
|
|
# simple inserts
|
|
DROP TABLE IF EXISTS cs1;
|
|
CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(38,10), d3 DECIMAL(38,38),
|
|
d4 DECIMAL(19), d5 DECIMAL(19,10), d6 DECIMAL(19,19)) ENGINE=columnstore;
|
|
INSERT INTO cs1 VALUES (125, 1.25, 0.125, 125, 1.25, 0.125),
|
|
(-125, -1.25, -0.125, -125, -1.25, -0.125),
|
|
(1.25, 125, 125, 1.25, 125, 125),
|
|
(-1.25, -125, -125, -1.25, -125, -125);
|
|
|
|
# insert maximum positive and negative values
|
|
INSERT INTO cs1 values (99999999999999999999999999999999999998, 9999999999999999999999999999.9999999998, 0.99999999999999999999999999999999999998, 9999999999999999998, 999999999.9999999998, 0.9999999999999999998),
|
|
(99999999999999999999999999999999999999, 9999999999999999999999999999.9999999999, 0.99999999999999999999999999999999999999, 9999999999999999999, 999999999.9999999999, 0.9999999999999999999),
|
|
(-99999999999999999999999999999999999998, -9999999999999999999999999999.9999999998, -0.99999999999999999999999999999999999998, -9999999999999999998, -999999999.9999999998, -0.9999999999999999998),
|
|
(-99999999999999999999999999999999999999, -9999999999999999999999999999.9999999999, -0.99999999999999999999999999999999999999, -9999999999999999999, -999999999.9999999999, -0.9999999999999999999);
|
|
|
|
# check saturation of positive and negative values
|
|
INSERT INTO cs1 VALUES (123456789012345678901234567890123456789, 12345678901234567890123456789.12345678912, 0.123456789012345678901234567890123456789, 12345678901234567890, 1234567890.12345678901, 0.12345678901234567891),
|
|
(-123456789012345678901234567890123456789, -12345678901234567890123456789.12345678912, -0.123456789012345678901234567890123456789, -12345678901234567890, -1234567890.12345678901, -0.12345678901234567891);
|
|
|
|
# check rounding when scale > 0
|
|
INSERT INTO cs1 VALUES (0, 1.12345678919, 0.123456789012345678901234567890123456789, 0, 1.12345678919, 0.12345678901234567899),
|
|
(0, 1.12345678914, 0.123456789012345678901234567890123456784, 0, 1.12345678914, 0.12345678901234567894),
|
|
(0, 1.99999999999, 0.999999999999999999999999999999999999999, 0, 1.99999999999, 0.99999999999999999999),
|
|
(0, 1.99999999994, 0.999999999999999999999999999999999999994, 0, 1.99999999994, 0.99999999999999999994),
|
|
(0, -1.12345678919, -0.123456789012345678901234567890123456789, 0, -1.12345678919, -0.12345678901234567899),
|
|
(0, -1.12345678914, -0.123456789012345678901234567890123456784, 0, -1.12345678914, -0.12345678901234567894),
|
|
(0, -1.99999999999, -0.999999999999999999999999999999999999999, 0, -1.99999999999, -0.99999999999999999999),
|
|
(0, -1.99999999994, -0.999999999999999999999999999999999999994, 0, -1.99999999994, -0.99999999999999999994);
|
|
|
|
# insert values with leading zeros in the scale
|
|
INSERT INTO cs1 VALUES (0, 1.0000000009, 0.00000000000000000000000000000000000009, 0, 1.0000000009, 0.0000000000000000009),
|
|
(0, 1.00000000009, 0.000000000000000000000000000000000000009, 0, 1.00000000009, 0.00000000000000000009),
|
|
(0, -1.0000000009, -0.00000000000000000000000000000000000009, 0, -1.0000000009, -0.0000000000000000009),
|
|
(0, -1.00000000009, -0.000000000000000000000000000000000000009, 0, -1.00000000009, -0.00000000000000000009);
|
|
|
|
# insert all zeros and nulls
|
|
INSERT INTO cs1 VALUES (0, 0, 0, 0, 0, 0),
|
|
(NULL, NULL, NULL, NULL, NULL, NULL);
|
|
|
|
# insert values with digits interleaved with 0s
|
|
INSERT INTO cs1 VALUES (12345678901234567890123456789000000001, 1234567890123456789012345678.0010000008, 0.00001000000000000000000000000000000009, 1234567890120000001, 123456789.0020000009, 0.0000000070000000009),
|
|
(-12345678901234567890123456789000000001, -1234567890123456789012345678.0010000008, -0.00001000000000000000000000000000000009, -1234567890120000001, -123456789.0020000009, -0.0000000070000000009);
|
|
|
|
SELECT "test2", d1, d2, d3, d4, d5, d6 FROM cs1;
|
|
|
|
# INSERT ... SELECT
|
|
INSERT INTO cs3 SELECT * FROM cs1;
|
|
SELECT "insert_select_test1", d1, d2, d3, d4, d5, d6 FROM cs3;
|
|
|
|
# LDI
|
|
--exec rm -f /tmp/wide_decimal_ldi.txt
|
|
SELECT * FROM cs3 INTO OUTFILE '/tmp/wide_decimal_ldi.txt' FIELDS TERMINATED BY ',';
|
|
DELETE FROM cs3;
|
|
LOAD DATA LOCAL INFILE '/tmp/wide_decimal_ldi.txt' INTO TABLE cs3 FIELDS TERMINATED BY ',';
|
|
SELECT "ldi_test1", d1, d2, d3, d4, d5, d6 FROM cs3;
|
|
|
|
# unsigned case. repeat queries for signed case here
|
|
# single value inserts
|
|
|
|
# simple inserts
|
|
INSERT INTO cs2 VALUES (125, 1.25, 0.125, 125, 1.25, 0.125);
|
|
INSERT INTO cs2 VALUES (-125, -1.25, -0.125, -125, -1.25, -0.125);
|
|
INSERT INTO cs2 VALUES (1.25, 125, 125, 1.25, 125, 125);
|
|
INSERT INTO cs2 VALUES (-1.25, -125, -125, -1.25, -125, -125);
|
|
|
|
# insert maximum positive and negative values
|
|
INSERT INTO cs2 values (99999999999999999999999999999999999998, 9999999999999999999999999999.9999999998, 0.99999999999999999999999999999999999998, 9999999999999999998, 999999999.9999999998, 0.9999999999999999998);
|
|
INSERT INTO cs2 values (99999999999999999999999999999999999999, 9999999999999999999999999999.9999999999, 0.99999999999999999999999999999999999999, 9999999999999999999, 999999999.9999999999, 0.9999999999999999999);
|
|
INSERT INTO cs2 values (-99999999999999999999999999999999999998, -9999999999999999999999999999.9999999998, -0.99999999999999999999999999999999999998, -9999999999999999998, -999999999.9999999998, -0.9999999999999999998);
|
|
INSERT INTO cs2 values (-99999999999999999999999999999999999999, -9999999999999999999999999999.9999999999, -0.99999999999999999999999999999999999999, -9999999999999999999, -999999999.9999999999, -0.9999999999999999999);
|
|
|
|
# check saturation of positive and negative values
|
|
INSERT INTO cs2 VALUES (123456789012345678901234567890123456789, 12345678901234567890123456789.12345678912, 0.123456789012345678901234567890123456789, 12345678901234567890, 1234567890.12345678901, 0.12345678901234567891);
|
|
INSERT INTO cs2 VALUES (-123456789012345678901234567890123456789, -12345678901234567890123456789.12345678912, -0.123456789012345678901234567890123456789, -12345678901234567890, -1234567890.12345678901, -0.12345678901234567891);
|
|
|
|
# check rounding when scale > 0
|
|
INSERT INTO cs2 VALUES (0, 1.12345678919, 0.123456789012345678901234567890123456789, 0, 1.12345678919, 0.12345678901234567899);
|
|
INSERT INTO cs2 VALUES (0, 1.12345678914, 0.123456789012345678901234567890123456784, 0, 1.12345678914, 0.12345678901234567894);
|
|
INSERT INTO cs2 VALUES (0, 1.99999999999, 0.999999999999999999999999999999999999999, 0, 1.99999999999, 0.99999999999999999999);
|
|
INSERT INTO cs2 VALUES (0, 1.99999999994, 0.999999999999999999999999999999999999994, 0, 1.99999999994, 0.99999999999999999994);
|
|
INSERT INTO cs2 VALUES (0, -1.12345678919, -0.123456789012345678901234567890123456789, 0, -1.12345678919, -0.12345678901234567899);
|
|
INSERT INTO cs2 VALUES (0, -1.12345678914, -0.123456789012345678901234567890123456784, 0, -1.12345678914, -0.12345678901234567894);
|
|
INSERT INTO cs2 VALUES (0, -1.99999999999, -0.999999999999999999999999999999999999999, 0, -1.99999999999, -0.99999999999999999999);
|
|
INSERT INTO cs2 VALUES (0, -1.99999999994, -0.999999999999999999999999999999999999994, 0, -1.99999999994, -0.99999999999999999994);
|
|
|
|
# insert values with leading zeros in the scale
|
|
INSERT INTO cs2 VALUES (0, 1.0000000009, 0.00000000000000000000000000000000000009, 0, 1.0000000009, 0.0000000000000000009);
|
|
INSERT INTO cs2 VALUES (0, 1.00000000009, 0.000000000000000000000000000000000000009, 0, 1.00000000009, 0.00000000000000000009);
|
|
INSERT INTO cs2 VALUES (0, -1.0000000009, -0.00000000000000000000000000000000000009, 0, -1.0000000009, -0.0000000000000000009);
|
|
INSERT INTO cs2 VALUES (0, -1.00000000009, -0.000000000000000000000000000000000000009, 0, -1.00000000009, -0.00000000000000000009);
|
|
|
|
# insert all zeros
|
|
INSERT INTO cs2 VALUES (0, 0, 0, 0, 0, 0);
|
|
|
|
# insert all nulls
|
|
INSERT INTO cs2 VALUES (NULL, NULL, NULL, NULL, NULL, NULL);
|
|
INSERT INTO cs2 VALUES ();
|
|
SELECT "test3", d1, d2, d3, d4, d5, d6 FROM cs2;
|
|
|
|
# multi-value inserts. repeat queries for single-value inserts
|
|
|
|
# simple inserts
|
|
DROP TABLE IF EXISTS cs2;
|
|
CREATE TABLE cs2 (d1 DECIMAL(38) UNSIGNED, d2 DECIMAL(38,10) UNSIGNED, d3 DECIMAL(38,38) UNSIGNED,
|
|
d4 DECIMAL(19) UNSIGNED, d5 DECIMAL(19,10) UNSIGNED, d6 DECIMAL(19,19) UNSIGNED) ENGINE=columnstore;
|
|
INSERT INTO cs2 VALUES (125, 1.25, 0.125, 125, 1.25, 0.125),
|
|
(-125, -1.25, -0.125, -125, -1.25, -0.125),
|
|
(1.25, 125, 125, 1.25, 125, 125),
|
|
(-1.25, -125, -125, -1.25, -125, -125);
|
|
|
|
# insert maximum positive and negative values
|
|
INSERT INTO cs2 values (99999999999999999999999999999999999998, 9999999999999999999999999999.9999999998, 0.99999999999999999999999999999999999998, 9999999999999999998, 999999999.9999999998, 0.9999999999999999998),
|
|
(99999999999999999999999999999999999999, 9999999999999999999999999999.9999999999, 0.99999999999999999999999999999999999999, 9999999999999999999, 999999999.9999999999, 0.9999999999999999999),
|
|
(-99999999999999999999999999999999999998, -9999999999999999999999999999.9999999998, -0.99999999999999999999999999999999999998, -9999999999999999998, -999999999.9999999998, -0.9999999999999999998),
|
|
(-99999999999999999999999999999999999999, -9999999999999999999999999999.9999999999, -0.99999999999999999999999999999999999999, -9999999999999999999, -999999999.9999999999, -0.9999999999999999999);
|
|
|
|
# check saturation of positive and negative values
|
|
INSERT INTO cs2 VALUES (123456789012345678901234567890123456789, 12345678901234567890123456789.12345678912, 0.123456789012345678901234567890123456789, 12345678901234567890, 1234567890.12345678901, 0.12345678901234567891),
|
|
(-123456789012345678901234567890123456789, -12345678901234567890123456789.12345678912, -0.123456789012345678901234567890123456789, -12345678901234567890, -1234567890.12345678901, -0.12345678901234567891);
|
|
|
|
# check rounding when scale > 0
|
|
INSERT INTO cs2 VALUES (0, 1.12345678919, 0.123456789012345678901234567890123456789, 0, 1.12345678919, 0.12345678901234567899),
|
|
(0, 1.12345678914, 0.123456789012345678901234567890123456784, 0, 1.12345678914, 0.12345678901234567894),
|
|
(0, 1.99999999999, 0.999999999999999999999999999999999999999, 0, 1.99999999999, 0.99999999999999999999),
|
|
(0, 1.99999999994, 0.999999999999999999999999999999999999994, 0, 1.99999999994, 0.99999999999999999994),
|
|
(0, -1.12345678919, -0.123456789012345678901234567890123456789, 0, -1.12345678919, -0.12345678901234567899),
|
|
(0, -1.12345678914, -0.123456789012345678901234567890123456784, 0, -1.12345678914, -0.12345678901234567894),
|
|
(0, -1.99999999999, -0.999999999999999999999999999999999999999, 0, -1.99999999999, -0.99999999999999999999),
|
|
(0, -1.99999999994, -0.999999999999999999999999999999999999994, 0, -1.99999999994, -0.99999999999999999994);
|
|
|
|
# insert values with leading zeros in the scale
|
|
INSERT INTO cs2 VALUES (0, 1.0000000009, 0.00000000000000000000000000000000000009, 0, 1.0000000009, 0.0000000000000000009),
|
|
(0, 1.00000000009, 0.000000000000000000000000000000000000009, 0, 1.00000000009, 0.00000000000000000009),
|
|
(0, -1.0000000009, -0.00000000000000000000000000000000000009, 0, -1.0000000009, -0.0000000000000000009),
|
|
(0, -1.00000000009, -0.000000000000000000000000000000000000009, 0, -1.00000000009, -0.00000000000000000009);
|
|
|
|
# insert all zeros and nulls
|
|
INSERT INTO cs2 VALUES (0, 0, 0, 0, 0, 0),
|
|
(NULL, NULL, NULL, NULL, NULL, NULL);
|
|
|
|
# insert values with digits interleaved with 0s
|
|
INSERT INTO cs2 VALUES (12345678901234567890123456789000000001, 1234567890123456789012345678.0010000008, 0.00001000000000000000000000000000000009, 1234567890120000001, 123456789.0020000009, 0.0000000070000000009),
|
|
(-12345678901234567890123456789000000001, -1234567890123456789012345678.0010000008, -0.00001000000000000000000000000000000009, -1234567890120000001, -123456789.0020000009, -0.0000000070000000009);
|
|
|
|
SELECT "test4", d1, d2, d3, d4, d5, d6 FROM cs2;
|
|
|
|
# INSERT ... SELECT
|
|
INSERT INTO cs4 SELECT * FROM cs2;
|
|
SELECT "insert_select_test2", d1, d2, d3, d4, d5, d6 FROM cs4;
|
|
|
|
# LDI
|
|
--exec rm -f /tmp/wide_decimal_ldi.txt
|
|
SELECT * FROM cs4 INTO OUTFILE '/tmp/wide_decimal_ldi.txt' FIELDS TERMINATED BY ',';
|
|
DELETE FROM cs4;
|
|
LOAD DATA LOCAL INFILE '/tmp/wide_decimal_ldi.txt' INTO TABLE cs4 FIELDS TERMINATED BY ',';
|
|
SELECT "ldi_test2", d1, d2, d3, d4, d5, d6 FROM cs4;
|
|
|
|
# Clean UP
|
|
SET SQL_MODE=default;
|
|
--exec rm -f /tmp/wide_decimal_ldi.txt
|
|
DROP DATABASE mcol641_insert_db;
|