-- 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;