mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
1028 lines
49 KiB
Text
1028 lines
49 KiB
Text
#
|
||
# Dynamic column function test
|
||
#
|
||
|
||
--source include/default_charset.inc
|
||
|
||
--echo #
|
||
--echo # column create
|
||
--echo #
|
||
select hex(COLUMN_CREATE(1, NULL AS char character set utf8)) as exp;
|
||
select hex(COLUMN_CREATE(1, "afaf" AS char character set utf8 collate utf8_general_ci)) as ex;
|
||
select hex(COLUMN_CREATE(1, 1212 AS char character set utf8 collate utf8_general_ci)) as ex;
|
||
select hex(COLUMN_CREATE(1, 12.12 AS char character set utf8 collate utf8_general_ci)) as ex;
|
||
select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS char character set utf8 collate utf8_general_ci)) as ex;
|
||
select hex(COLUMN_CREATE(1, NULL AS unsigned int));
|
||
select hex(COLUMN_CREATE(1, 1212 AS unsigned int));
|
||
select hex(COLUMN_CREATE(1, 7 AS unsigned int));
|
||
select hex(COLUMN_CREATE(1, 8 AS unsigned int));
|
||
select hex(COLUMN_CREATE(1, 127 AS unsigned int));
|
||
select hex(COLUMN_CREATE(1, 128 AS unsigned int));
|
||
select hex(COLUMN_CREATE(1, 12.12 AS unsigned int));
|
||
select hex(COLUMN_CREATE(1, ~0));
|
||
select hex(COLUMN_CREATE(1, -1));
|
||
select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS unsigned int)) as ex;
|
||
select hex(COLUMN_CREATE(1, NULL AS int));
|
||
select hex(COLUMN_CREATE(1, 1212 AS int));
|
||
select hex(COLUMN_CREATE(1, 7 AS int));
|
||
select hex(COLUMN_CREATE(1, 8 AS int));
|
||
select hex(COLUMN_CREATE(1, 127 AS int));
|
||
select hex(COLUMN_CREATE(1, 128 AS int));
|
||
select hex(COLUMN_CREATE(1, 12.12 AS int));
|
||
select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS int)) as ex;
|
||
select hex(COLUMN_CREATE(1, NULL AS double));
|
||
select hex(COLUMN_CREATE(1, 1212 AS double));
|
||
select hex(COLUMN_CREATE(1, 12.12 AS double));
|
||
select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS double)) as ex;
|
||
select hex(COLUMN_CREATE(1, NULL AS decimal));
|
||
select hex(COLUMN_CREATE(1, 1212 AS decimal));
|
||
select hex(COLUMN_CREATE(1, 7 AS decimal));
|
||
select hex(COLUMN_CREATE(1, 8 AS decimal));
|
||
select hex(COLUMN_CREATE(1, 127 AS decimal));
|
||
select hex(COLUMN_CREATE(1, 128 AS decimal));
|
||
select hex(COLUMN_CREATE(1, 12.12 AS decimal));
|
||
select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS decimal)) as ex;
|
||
select hex(COLUMN_CREATE(1, NULL AS date));
|
||
select hex(COLUMN_CREATE(1, "2011-04-05" AS date));
|
||
select hex(COLUMN_CREATE(1, NULL AS time));
|
||
select hex(COLUMN_CREATE(1, "0:45:49.000001" AS time));
|
||
select hex(COLUMN_CREATE(1, NULL AS datetime));
|
||
select hex(COLUMN_CREATE(1, "2011-04-05 0:45:49.000001" AS datetime)) as ex;
|
||
select hex(COLUMN_CREATE(1, "afaf" AS char character set utf8 collate utf8_general_ci,
|
||
2, 1212 AS unsigned int,
|
||
3, 1212 AS int,
|
||
4, 12.12 AS double,
|
||
4+1, 12.12 AS decimal,
|
||
6, "2011-04-05" AS date,
|
||
7, "- 0:45:49.000001" AS time,
|
||
8, "2011-04-05 0:45:49.000001" AS datetime)) as ex;
|
||
explain extended
|
||
select hex(COLUMN_CREATE(1, "afaf" AS char character set utf8,
|
||
2, 1212 AS unsigned int,
|
||
3, 1212 AS int,
|
||
4, 12.12 AS double,
|
||
4+1, 12.12 AS decimal,
|
||
6, "2011-04-05" AS date,
|
||
7, "- 0:45:49.000001" AS time,
|
||
8, "2011-04-05 0:45:49.000001" AS datetime)) as ex;
|
||
select hex(column_create(1, 0.0 AS decimal));
|
||
select hex(column_create(1, 1.0 AS decimal));
|
||
|
||
--echo #
|
||
--echo # column get uint
|
||
--echo #
|
||
select column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned int) as ex;
|
||
explain extended
|
||
select column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned int) as ex;
|
||
explain extended
|
||
select column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned) as ex;
|
||
select column_get(column_create(1, 1212 AS decimal), 1 as unsigned int) as ex;
|
||
select column_get(column_create(1, 1212 AS double), 1 as unsigned int) as ex;
|
||
select column_get(column_create(1, 1212 AS int), 1 as unsigned int) as ex;
|
||
select column_get(column_create(1, "1212" AS char), 1 as unsigned int) as ex;
|
||
select column_get(column_create(1, "2011-04-05" AS date), 1 as unsigned int) as ex;
|
||
select column_get(column_create(1, "8:46:06.23434" AS time), 1 as unsigned int) as ex;
|
||
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as unsigned int) as ex;
|
||
select column_get(column_create(1, NULL AS unsigned int), 1 as unsigned int) as ex;
|
||
--echo # column geint truncation & warnings
|
||
select column_get(column_create(1, -1212 AS int), 1 as unsigned int) as ex;
|
||
select column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as unsigned int) as ex;
|
||
select column_get(column_create(1, 999.9999999999999999 AS decimal), 1 as unsigned int) as ex;
|
||
select column_get(column_create(1, -1 AS decimal), 1 as unsigned int) as ex;
|
||
--replace_result e+029 e+29
|
||
select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as unsigned int) as ex;
|
||
select column_get(column_create(1, 999.9 AS double), 1 as unsigned int) as ex;
|
||
select column_get(column_create(1, -1 AS double), 1 as unsigned int) as ex;
|
||
select column_get(column_create(1, "1212III" AS char), 1 as unsigned int) as ex;
|
||
|
||
--echo #
|
||
--echo # column get int
|
||
--echo #
|
||
select column_get(column_create(1, 1212 AS int), 1 as int) as ex;
|
||
explain extended
|
||
select column_get(column_create(1, 1212 AS int), 1 as int) as ex;
|
||
explain extended
|
||
select column_get(column_create(1, 1212 AS int), 1 as signed int) as ex;
|
||
select column_get(column_create(1, -1212 AS int), 1 as int) as ex;
|
||
select column_get(column_create(1, 1212 AS decimal), 1 as int) as ex;
|
||
select column_get(column_create(1, 1212 AS double), 1 as int) as ex;
|
||
select column_get(column_create(1, 1212 AS unsigned int), 1 as int) as ex;
|
||
select column_get(column_create(1, "1212" AS char), 1 as int) as ex;
|
||
select column_get(column_create(1, "-1212" AS char), 1 as int) as ex;
|
||
select column_get(column_create(1, "2011-04-05" AS date), 1 as int) as ex;
|
||
select column_get(column_create(1, "8:46:06.23434" AS time), 1 as int) as ex;
|
||
select column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as int) as ex;
|
||
select column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as int) as ex;
|
||
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(6)), 1 as int) as ex;
|
||
select column_get(column_create(1, NULL AS int), 1 as int) as ex;
|
||
--echo #column gett truncation & warnings
|
||
select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as int) as ex;
|
||
select column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as int) as ex;
|
||
select column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as int) as ex;
|
||
select column_get(column_create(1, 999.9999999999999999 AS decimal), 1 as int) as ex;
|
||
select column_get(column_create(1, 999.9 AS double), 1 as int) as ex;
|
||
--replace_result e+029 e+29
|
||
select column_get(column_create(1, -99999999999999999999999999999 AS double), 1 as int) as ex;
|
||
select column_get(column_create(1, "-1212III" AS char), 1 as int) as ex;
|
||
select column_get(column_create(1, "1212III" AS char), 1 as int) as ex;
|
||
select column_get(COLUMN_CREATE(1, ~0), 1 as signed) as ex;
|
||
select column_get(COLUMN_CREATE(1, ~0), 1 as unsigned) as ex;
|
||
select column_get(COLUMN_CREATE(1, -1), 1 as signed) as ex;
|
||
select column_get(COLUMN_CREATE(1, -1), 1 as unsigned) as ex;
|
||
|
||
--echo #
|
||
--echo #column get char
|
||
--echo #
|
||
select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset utf8) as ex;
|
||
explain extended
|
||
select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, 1212 AS unsigned int), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, 1212 AS int), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, -1212 AS int), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, 9223372036854775807 AS int), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, -9223372036854775808 AS int), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, 1212.12 AS decimal), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, 1212.12 AS double), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, "2011-04-05" AS date), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, "8:46:06.23434" AS time), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, "8:46:06.23434" AS time(0)), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(0)), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(6)), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, NULL AS char charset utf8), 1 as char charset utf8) as ex;
|
||
select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset binary) as ex;
|
||
explain extended
|
||
select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset binary) as ex;
|
||
|
||
--echo #
|
||
--echo # column get real
|
||
--echo #
|
||
select column_get(column_create(1, 1212.12 AS double), 1 as double) as ex;
|
||
explain extended
|
||
select column_get(column_create(1, 1212.12 AS double), 1 as double) as ex;
|
||
explain extended
|
||
select column_get(column_create(1, 1212.12 AS double), 1 as double(6,2)) as ex;
|
||
select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as double) as ex;
|
||
select column_get(column_create(1, 9223372036854775807 AS int), 1 as double) as ex;
|
||
select column_get(column_create(1, -9223372036854775808 AS int), 1 as double) as ex;
|
||
select column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as double) as ex;
|
||
select column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as double) as ex;
|
||
select column_get(column_create(1, "2011-04-05" AS date), 1 as double) as ex;
|
||
select column_get(column_create(1, "8:46:06.23434" AS time), 1 as double) as ex;
|
||
select column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as double) as ex;
|
||
select column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as double) as ex;
|
||
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as double) as ex;
|
||
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(6)), 1 as double) as ex;
|
||
# The replace result is needed for windows.
|
||
select round(column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as double(20,6)),3) as ex;
|
||
select column_get(column_create(1, NULL AS double), 1 as double) as ex;
|
||
|
||
-- echo # column get real truncation & warnings
|
||
select column_get(column_create(1, "1223.5aa" AS char), 1 as double) as ex;
|
||
select column_get(column_create(1, "aa" AS char), 1 as double) as ex;
|
||
select column_get(column_create(1, "1223.5555" AS double), 1 as double(5,2)) as ex;
|
||
select column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2)) as ex;
|
||
|
||
--echo #
|
||
--echo # column get decimal
|
||
--echo #
|
||
select column_get(column_create(1, 1212.12 AS double), 1 as decimal) as ex;
|
||
select column_get(column_create(1, 1212.12 AS double), 1 as decimal(6,2)) as ex;
|
||
explain extended
|
||
select column_get(column_create(1, 1212.12 AS double), 1 as decimal) as ex;
|
||
explain extended
|
||
select column_get(column_create(1, 1212.12 AS double), 1 as decimal(6,2)) as ex;
|
||
select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as decimal(20,0)) as ex;
|
||
select column_get(column_create(1, 9223372036854775807 AS int), 1 as decimal(32,0)) as ex;
|
||
select column_get(column_create(1, -9223372036854775808 AS int), 1 as decimal(32,0)) as ex;
|
||
select column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as decimal(40,10)) as ex;
|
||
select column_get(column_create(1, "2011-04-05" AS date), 1 as decimal(32,6)) as ex;
|
||
select column_get(column_create(1, "8:46:06.23434" AS time), 1 as decimal(32,6)) as ex;
|
||
select column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as decimal(32,6)) as ex;
|
||
select column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as decimal(32,6)) as ex;
|
||
select column_get(column_create(1, "2011-04-05 8:46:06.123456" AS datetime), 1 as decimal(32,6)) as ex;
|
||
select column_get(column_create(1, "2011-04-05 8:46:06.123456" AS datetime(6)), 1 as decimal(32,6)) as ex;
|
||
select column_get(column_create(1, "2011-04-05 8:46:06.12345678" AS datetime(6)), 1 as decimal(32,8)) as ex;
|
||
select column_get(column_create(1, NULL as decimal), 1 as decimal(32,10)) as ex;
|
||
select column_get(column_create(1, "1223.5555" as decimal(10,5)), 1 as decimal(6,2)) as ex;
|
||
|
||
-- echo # column get decimal truncation & warnings
|
||
select column_get(column_create(1, "1223.5aa" AS char), 1 as decimal(32,10)) as ex;
|
||
select column_get(column_create(1, "aa" AS char), 1 as decimal(32,10)) as ex;
|
||
select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as decimal) as ex;
|
||
select column_get(column_create(1, 9223372036854775807 AS int), 1 as decimal) as ex;
|
||
select column_get(column_create(1, -9223372036854775808 AS int), 1 as decimal) as ex;
|
||
select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as decimal) as ex;
|
||
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as decimal) as ex;
|
||
select column_get(column_create(1, "1223.5555" as double), 1 as decimal(5,2)) as ex;
|
||
select column_get(column_create(1, "-1223.5555" as double), 1 as decimal(5,2)) as ex;
|
||
select column_get(column_create(1, "1223.5555" AS double), 1 as decimal(3,2)) as ex;
|
||
select column_get(column_create(1, "1223.5555" AS decimal(10,5)), 1 as decimal(3,2)) as ex;
|
||
select column_get(column_create(1, 0.0 AS decimal,2, 0.0 as decimal), 1 as decimal) as ex;
|
||
|
||
--echo #
|
||
--echo # column get datetime
|
||
--echo #
|
||
select column_get(column_create(1, 20010203101112.121314 as double), 1 as datetime) as ex;
|
||
select column_get(column_create(1, 20010203101112.121314 as decimal), 1 as datetime) as ex;
|
||
select column_get(column_create(1, 20010203101112 as unsigned int), 1 as datetime) as ex;
|
||
select column_get(column_create(1, 20010203101112 as int), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "20010203101112" as char), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as datetime(0)) as ex;
|
||
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as datetime(6)) as ex;
|
||
select column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as datetime) as ex;
|
||
|
||
select column_get(column_create(1, 20010203 as unsigned int), 1 as datetime) as ex;
|
||
select column_get(column_create(1, 20010203 as int), 1 as datetime) as ex;
|
||
select column_get(column_create(1, 20010203), 1 as datetime) as ex;
|
||
select column_get(column_create(1, 20010203.0), 1 as datetime) as ex;
|
||
select column_get(column_create(1, 20010203.0 as double), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "2001-02-03"), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "20010203"), 1 as datetime) as ex;
|
||
select column_get(column_create(1, 0), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "2001021"), 1 as datetime) as ex;
|
||
|
||
SET timestamp=unix_timestamp('2001-02-03 10:20:30');
|
||
select column_get(column_create(1, "8:46:06.23434" AS time), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "-808:46:06.23434" AS time), 1 as datetime) as ex;
|
||
SET timestamp=DEFAULT;
|
||
|
||
set @@sql_mode="allow_invalid_dates";
|
||
select column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "0000-00-000" AS CHAR), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "2001-00-02" AS CHAR), 1 as datetime) as ex;
|
||
set @@sql_mode="";
|
||
|
||
-- echo # column get datetime truncation & warnings
|
||
select column_get(column_create(1, "1223.5aa" AS char), 1 as datetime) as ex;
|
||
--replace_result e+019 e+19
|
||
select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as datetime) as ex;
|
||
select column_get(column_create(1, 9223372036854775807 AS int), 1 as datetime) as ex;
|
||
select column_get(column_create(1, -9223372036854775808 AS int), 1 as datetime) as ex;
|
||
select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as datetime) as ex;
|
||
--replace_result e+029 e+29
|
||
select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "20010231"), 1 as datetime) as ex;
|
||
select column_get(column_create(1, "0" AS CHAR), 1 as datetime) as ex;
|
||
|
||
|
||
--echo #
|
||
--echo # column get date
|
||
--echo #
|
||
select column_get(column_create(1, 20010203101112.121314 as double), 1 as date) as ex;
|
||
select column_get(column_create(1, 20010203101112.121314 as decimal), 1 as date) as ex;
|
||
select column_get(column_create(1, 20010203101112 as unsigned int), 1 as date) as ex;
|
||
select column_get(column_create(1, 20010203101112 as int), 1 as date) as ex;
|
||
select column_get(column_create(1, "20010203101112" as char), 1 as date) as ex;
|
||
select column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as date) as ex;
|
||
select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as date) as ex;
|
||
select column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as date) as ex;
|
||
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as date) as ex;
|
||
select column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as date) as ex;
|
||
select column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as date) as ex;
|
||
|
||
select column_get(column_create(1, 20010203 as unsigned int), 1 as date) as ex;
|
||
select column_get(column_create(1, 20010203 as int), 1 as date) as ex;
|
||
select column_get(column_create(1, 20010203), 1 as date) as ex;
|
||
select column_get(column_create(1, 20010203.0), 1 as date) as ex;
|
||
select column_get(column_create(1, 20010203.0 as double), 1 as date) as ex;
|
||
select column_get(column_create(1, "2001-02-03"), 1 as date) as ex;
|
||
select column_get(column_create(1, "20010203"), 1 as date) as ex;
|
||
select column_get(column_create(1, 0), 1 as date) as ex;
|
||
select column_get(column_create(1, "2001021"), 1 as date) as ex;
|
||
|
||
set @@sql_mode="allow_invalid_dates";
|
||
select column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as date) as ex;
|
||
select column_get(column_create(1, "0000-00-000" AS CHAR), 1 as date) as ex;
|
||
select column_get(column_create(1, "2001-00-02" AS CHAR), 1 as date) as ex;
|
||
set @@sql_mode="";
|
||
|
||
-- echo # column get date truncation & warnings
|
||
select column_get(column_create(1, "1223.5aa" AS char), 1 as date) as ex;
|
||
--replace_result e+019 e+19
|
||
select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as date) as ex;
|
||
select column_get(column_create(1, 9223372036854775807 AS int), 1 as date) as ex;
|
||
select column_get(column_create(1, -9223372036854775808 AS int), 1 as date) as ex;
|
||
select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as date) as ex;
|
||
--replace_result e+029 e+29
|
||
select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as date) as ex;
|
||
select column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as date) as ex;
|
||
select column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as date) as ex;
|
||
select column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as date) as ex;
|
||
select column_get(column_create(1, "20010231"), 1 as date) as ex;
|
||
select column_get(column_create(1, "0" AS CHAR), 1 as date) as ex;
|
||
|
||
--echo #
|
||
--echo # column get time
|
||
--echo #
|
||
select column_get(column_create(1, 20010203101112.121314 as double), 1 as time) as ex;
|
||
select column_get(column_create(1, 20010203101112.121314 as decimal), 1 as time) as ex;
|
||
select column_get(column_create(1, 20010203101112 as unsigned int), 1 as time) as ex;
|
||
select column_get(column_create(1, 8080102 as unsigned int), 1 as time) as ex;
|
||
select column_get(column_create(1, 20010203101112 as int), 1 as time) as ex;
|
||
select column_get(column_create(1, -8080102 as int), 1 as time) as ex;
|
||
select column_get(column_create(1, "20010203101112" as char), 1 as time) as ex;
|
||
select column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as time) as ex;
|
||
select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as time) as ex;
|
||
select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as time(6)) as ex;
|
||
select column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as time) as ex;
|
||
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as time(6)) as ex;
|
||
select column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as time(6)) as ex;
|
||
select column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as time(6)) as ex;
|
||
select column_get(column_create(1, "830:46:06.23434" AS CHAR), 1 as time(6)) as ex;
|
||
select column_get(column_create(1, "830:46:06" AS CHAR), 1 as time(6)) as ex;
|
||
select cast("-830:46:06.23434" AS time(6)) as ex;
|
||
select 1,cast("-830:46:06.23434" AS time(6)) as ex;
|
||
select hex(column_create(1, "-830:46:06.23434" AS CHAR)) as ex;
|
||
select column_get(column_create(1, "-830:46:06.23434" AS CHAR), 1 as time(6)) as ex;
|
||
select column_get(column_create(1, "0" AS CHAR), 1 as time) as ex;
|
||
select column_get(column_create(1, "6" AS CHAR), 1 as time) as ex;
|
||
select column_get(column_create(1, "1:6" AS CHAR), 1 as time) as ex;
|
||
select column_get(column_create(1, "2:1:6" AS CHAR), 1 as time) as ex;
|
||
|
||
select column_get(column_create(1, 0), 1 as time) as ex;
|
||
select column_get(column_create(1, "2001021"), 1 as time) as ex;
|
||
|
||
set @@sql_mode="allow_invalid_dates";
|
||
select column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as time) as ex;
|
||
set @@sql_mode="";
|
||
|
||
-- echo # column get date truncation & warnings
|
||
select column_get(column_create(1, "1223.5aa" AS char), 1 as time) as ex;
|
||
select column_get(column_create(1, "1223.5aa" AS char), 1 as time(3)) as ex;
|
||
--replace_result e+019 e+19
|
||
select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as time) as ex;
|
||
select column_get(column_create(1, 9223372036854775807 AS int), 1 as time) as ex;
|
||
select column_get(column_create(1, -9223372036854775808 AS int), 1 as time) as ex;
|
||
select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as time) as ex;
|
||
--replace_result e+029 e+29
|
||
select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as time) as ex;
|
||
select column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as time) as ex;
|
||
select column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as time) as ex;
|
||
select column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as time) as ex;
|
||
select column_get(column_create(1, "2001-02-03"), 1 as time) as ex;
|
||
select column_get(column_create(1, "20010203"), 1 as time) as ex;
|
||
|
||
|
||
-- echo # column add
|
||
select hex(column_add(column_create(1, 1212 as integer), 2, 1212 as integer)) as ex;
|
||
select hex(column_add(column_create(1, 1212 as integer), 1, 1212 as integer)) as ex;
|
||
select hex(column_add(column_create(1, 1212 as integer), 1, NULL as integer)) as ex;
|
||
select hex(column_add(column_create(1, 1212 as integer), 2, NULL as integer)) as ex;
|
||
select hex(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer)) as ex;
|
||
select column_get(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer), 1 as integer) as ex;
|
||
select column_get(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer), 2 as integer) as ex;
|
||
select hex(column_add(column_create(1, 1212 as integer), 1, 1212 as integer, 2, 11 as integer)) as ex;
|
||
select hex(column_add(column_create(1, NULL as integer), 1, 1212 as integer, 2, 11 as integer)) as ex;
|
||
select hex(column_add(column_create(1, 1212 as integer, 2, 1212 as integer), 1, 11 as integer)) as ex;
|
||
select hex(column_add(column_create(1, 1), 1, null)) as ex;
|
||
select column_list(column_add(column_create(1, 1), 1, null)) as ex;
|
||
select column_list(column_add(column_create(1, 1), 1, "")) as ex;
|
||
select hex(column_add("", 1, 1)) as ex;
|
||
|
||
-- echo # column delete
|
||
select hex(column_delete(column_create(1, 1212 as integer, 2, 1212 as integer), 1)) as ex;
|
||
select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2)) as ex;
|
||
select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 3)) as ex;
|
||
select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 4)) as ex;
|
||
select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2, 1)) as ex;
|
||
select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2, 3)) as ex;
|
||
select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 1, 2, 3)) as ex;
|
||
select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 1, 2, 3, 10)) as ex;
|
||
select hex(column_delete(column_create(1, 1), 1)) as ex;
|
||
select hex(column_delete("", 1)) as ex;
|
||
|
||
-- echo # column exists
|
||
select column_exists(column_create(1, 1212 as integer, 2, 1212 as integer), 1) as ex;
|
||
select column_exists(column_create(1, 1212 as integer, 2, 1212 as integer), 4) as ex;
|
||
|
||
-- echo # column list
|
||
select column_list(column_create(1, 1212 as integer, 2, 1212 as integer)) as ex;
|
||
select column_list(column_create(1, 1212 as integer)) as ex;
|
||
select column_list(column_create(1, NULL as integer)) as ex;
|
||
|
||
--echo #
|
||
--echo # check error handling
|
||
--echo #
|
||
--error ER_DYN_COL_DATA
|
||
select HEX(COLUMN_CREATE(1, 5, 1, 5));
|
||
--error 1064
|
||
select HEX(COLUMN_CREATE("", 1, 5, 1, 5));
|
||
--error ER_DYN_COL_WRONG_FORMAT
|
||
select COLUMN_LIST("a");
|
||
--error ER_DYN_COL_WRONG_FORMAT
|
||
select column_delete("a", 1);
|
||
select hex(column_delete("", 1));
|
||
--error ER_DYN_COL_DATA
|
||
select hex(column_delete("", -1));
|
||
--error ER_DYN_COL_DATA
|
||
select hex(column_create(-1, 1));
|
||
--error ER_DYN_COL_DATA
|
||
select hex(column_create(65536, 1));
|
||
--error ER_DYN_COL_DATA
|
||
select hex(column_add("", -1, 1));
|
||
--error ER_DYN_COL_DATA
|
||
select hex(column_add("", 65536, 1));
|
||
select hex(column_get("", -1 as int));
|
||
|
||
--echo #
|
||
--echo # Test with table
|
||
--echo #
|
||
|
||
# create table with 'str' to hold a set of dynamic columns
|
||
create table t1 (id int primary key, str mediumblob);
|
||
insert into t1 values (1, ''), (2, ''), (3, ''), (4, ''), (5, null);
|
||
|
||
# Selecting a non existing column
|
||
select id, str, column_get(str, 1 as int) from t1;
|
||
|
||
# Add some dynamic columns. One and do it with create or add.
|
||
update t1 set str=column_create(1, id, 2, "a") where id < 3;
|
||
update t1 set str=column_add(str, 1, id, 2, "b") where id >= 4;
|
||
|
||
# Show some data, if it exists
|
||
select id, column_get(str, 1 as int), column_get(str, 2 as char) from t1 where column_exists(str,1) or column_exists(str,2);
|
||
|
||
# Add data to row 5 and 6
|
||
update t1 set str=column_create(1, id, 10, "test") where id = 5;
|
||
insert into t1 values (6, column_create(10, "test2"));
|
||
|
||
# Update some of the columns and add a new column 3
|
||
update t1 set str=column_add(str, 2, 'c', 1, column_get(str, 1 as int) + 1, 3, 100) where id > 2;
|
||
|
||
# Check data
|
||
--sorted_result
|
||
select id, length(str), column_get(str, 1 as int), column_get(str, 2 as char), column_get(str, 3 as int) from t1;
|
||
|
||
# You can do anything with the columns, like SUM() or GROUP
|
||
select column_get(str, 2 as char), sum(column_get(str, 1 as int)) from t1 group by column_get(str, 2 as char);
|
||
select column_get(str, 2 as char), sum(column_get(str, 1 as int)) from t1 where column_exists(str, 2) <> 0 group by 1;
|
||
select sum(column_get(str, 1 as int)) from t1 group by column_get(str, 2 as char) order by sum(column_get(str, 1 as int)) desc;
|
||
select sum(column_get(str, 1 as int)) from t1 group by column_get(str, 2 as char) having sum(column_get(str, 1 as int)) > 2;
|
||
select sum(column_get(str, 1 as int)) from t1 where column_get(str, 3 as int) > 50 group by column_get(str, 2 as char);
|
||
|
||
# Deleting of column
|
||
select id, column_list(str) from t1 where id= 5;
|
||
update t1 set str=column_delete(str, 3, 4, 2) where id= 5;
|
||
|
||
--sorted_result
|
||
select id, length(str), column_list(str), column_get(str, 1 as int), column_get(str, 2 as char), column_get(str, 3 as int) from t1;
|
||
|
||
update t1 set str=column_add(str, 4, 45 as char, 2, 'c') where id= 5;
|
||
select id, length(str), column_list(str), column_get(str, 1 as int), column_get(str, 2 as char), column_get(str, 3 as int) from t1 where id = 5;
|
||
|
||
# Check which column exists
|
||
--sorted_result
|
||
select id, length(str), column_list(str), column_exists(str, 4) from t1;
|
||
select sum(column_get(str, 1 as int)), column_list(str) from t1 group by 2;
|
||
--sorted_result
|
||
select id, hex(str) from t1;
|
||
|
||
# Check with a bit larger strings
|
||
|
||
update t1 set str=column_add(str, 4, repeat("a", 100000)) where id=5;
|
||
select id from t1 where column_get(str,4 as char(100000)) = repeat("a", 100000);
|
||
select id from t1 where column_get(str,4 as char(100)) = repeat("a", 100);
|
||
update t1 set str=column_add(str, 4, repeat("b", 10000)) where id=5;
|
||
select id from t1 where column_get(str,4 as char(100000)) = repeat("b", 10000);
|
||
update t1 set str=column_add(str, 4, repeat("c", 100)) where id=5;
|
||
select id from t1 where column_get(str,4 as char(100000)) = repeat("c", 100);
|
||
update t1 set str=column_add(str, 4, repeat("d", 10000)) where id=5;
|
||
select id from t1 where column_get(str,4 as char(100000)) = repeat("d", 10000);
|
||
update t1 set str=column_add(str, 4, repeat("e", 10), 5, repeat("f", 100000)) where id=5;
|
||
select id from t1 where column_get(str,5 as char(100000)) = repeat("f", 100000);
|
||
select id, column_list(str), length(str) from t1 where id=5;
|
||
update t1 set str=column_delete(str, 5) where id=5;
|
||
select id, column_list(str), length(str) from t1 where id=5;
|
||
|
||
drop table t1;
|
||
|
||
--echo #
|
||
--echo # LP#778905: Assertion `value->year <= 9999' failed in
|
||
--echo # dynamic_column_date_store
|
||
--echo #
|
||
|
||
--error ER_DYN_COL_WRONG_FORMAT
|
||
SELECT COLUMN_GET( 'a' , 2 AS DATE );
|
||
--error ER_DYN_COL_WRONG_FORMAT
|
||
SELECT COLUMN_CREATE( 1 , COLUMN_GET( 'a' , 2 AS DATE ) );
|
||
|
||
--echo #
|
||
--echo # LP#778912: Assertion `field_pos < field_count' failed in
|
||
--echo # Protocol_text::store in maria-5.3-mwl34
|
||
--echo #
|
||
|
||
CREATE TABLE t1 ( f1 blob );
|
||
INSERT INTO t1 VALUES (NULL);
|
||
INSERT INTO t1 SET f1 = COLUMN_CREATE( 2 , 'cde' );
|
||
SELECT HEX(COLUMN_ADD(f1, 1, 'abc')), COLUMN_LIST(f1) FROM t1;
|
||
|
||
# Don't print strange characters on screen
|
||
--disable_result_log
|
||
SELECT COLUMN_ADD(f1, 1, 'abc'), COLUMN_LIST(f1) FROM t1;
|
||
--enable_result_log
|
||
DROP TABLE t1;
|
||
|
||
--echo #
|
||
--echo # Some dynamic strings that caused crashes in the past
|
||
--echo #
|
||
|
||
set @a=0x0102000200030004000F0D086B74697A6A7176746F6B687563726A746E7A746A666163726C6F7A6B62636B6B756B666779666977617369796F67756C726D62677A72756E63626D78636D7077706A6F736C6D636464696770786B6371637A6A6A6463737A6A676879716462637178646C666E6B6C726A637677696E7271746C616D646368687A6C707869786D666F666261797470616A63797673737A796D74747475666B717573687A79696E7276706F796A6E767361796A6F6D646F6378677A667074746363736A796D67746C786F697873686464616265616A7A6F7168707A6B776B6376737A6B72666C6F666C69636163686F6B666D627166786A71616F;
|
||
--error ER_DYN_COL_WRONG_FORMAT
|
||
select column_add(@a, 3, "a");
|
||
|
||
--echo #
|
||
--echo # LP#781233 mysqld: decimal.c:1459: decimal_bin_size:
|
||
--echo # Assertion `scale >= 0 && precision > 0 && scale <= precision' ...
|
||
--echo #
|
||
|
||
set @a=0x00020008000009000C2C010080;
|
||
select COLUMN_GET(@a, 9 AS DECIMAL);
|
||
select hex(COLUMN_CREATE(0, COLUMN_GET(@a, 9 AS DECIMAL))) as ex;
|
||
select hex(COLUMN_CREATE(0, COLUMN_GET(@a, 9 AS DECIMAL(19,0)))) as ex;
|
||
|
||
select hex(COLUMN_CREATE(0, COLUMN_GET(COLUMN_CREATE(0, 0.0 as decimal), 0 as decimal))) as ex;
|
||
select hex(COLUMN_CREATE(0, 0.0 as decimal)) as ex;
|
||
|
||
--echo #
|
||
--echo # MDEV-4292: parse error when selecting on views using dynamic column
|
||
--echo #
|
||
create table t1 (i int, d blob);
|
||
|
||
create view v1 as select i, column_get(d, 1 as binary) as a from t1;
|
||
select * from v1;
|
||
show create view v1;
|
||
drop view v1;
|
||
|
||
create view v1 as select i, column_get(d, 1 as int) as a from t1;
|
||
select * from v1;
|
||
show create view v1;
|
||
drop view v1;
|
||
|
||
create view v1 as select i, column_get(d, 1 as unsigned int) as a from t1;
|
||
select * from v1;
|
||
show create view v1;
|
||
drop view v1;
|
||
|
||
create view v1 as select i, column_get(d, 1 as date) as a from t1;
|
||
select * from v1;
|
||
show create view v1;
|
||
drop view v1;
|
||
|
||
create view v1 as select i, column_get(d, 1 as time) as a from t1;
|
||
select * from v1;
|
||
show create view v1;
|
||
drop view v1;
|
||
|
||
create view v1 as select i, column_get(d, 1 as datetime) as a from t1;
|
||
select * from v1;
|
||
show create view v1;
|
||
drop view v1;
|
||
|
||
create view v1 as select i, column_get(d, 1 as decimal) as a from t1;
|
||
select * from v1;
|
||
show create view v1;
|
||
drop view v1;
|
||
|
||
create view v1 as select i, column_get(d, 1 as double) as a from t1;
|
||
select * from v1;
|
||
show create view v1;
|
||
drop view v1;
|
||
|
||
create view v1 as select i, column_get(d, 1 as char) as a from t1;
|
||
select * from v1;
|
||
show create view v1;
|
||
drop view v1;
|
||
|
||
drop table t1;
|
||
|
||
--echo #
|
||
--echo # MDEV-4811: Assertion `offset < 0x1f' fails in type_and_offset_store
|
||
--echo # on COLUMN_ADD
|
||
--echo #
|
||
|
||
CREATE TABLE t1 (dyn TINYBLOB) ENGINE=MyISAM;
|
||
INSERT INTO t1 SET dyn = COLUMN_CREATE( 40, REPEAT('a', 233), 4, REPEAT('b', 322) );
|
||
--error ER_DYN_COL_WRONG_FORMAT
|
||
SELECT COLUMN_ADD( dyn, 6, REPEAT('x',80), 4, REPEAT('y',215) AS INTEGER ) FROM t1;
|
||
delete from t1;
|
||
--echo #above test with 10.0 format
|
||
INSERT INTO t1 SET dyn = COLUMN_CREATE( 'a', REPEAT('a', 250), 'b', REPEAT('b', 322) );
|
||
--error ER_DYN_COL_WRONG_FORMAT
|
||
SELECT COLUMN_ADD( dyn, 'c', REPEAT('x',80), 'b', REPEAT('y',215) AS INTEGER ) FROM t1;
|
||
|
||
|
||
DROP table t1;
|
||
|
||
--echo #
|
||
--echo # MDEV-4812: Valgrind warnings (Invalid write) in
|
||
--echo # dynamic_column_update_many on COLUMN_ADD
|
||
--echo #
|
||
CREATE TABLE t1 (dyncol TINYBLOB) ENGINE=MyISAM;
|
||
|
||
INSERT INTO t1 SET dyncol = COLUMN_CREATE( 7, REPEAT('k',487), 209, REPEAT('x',464) );
|
||
--error 0,ER_DYN_COL_WRONG_FORMAT
|
||
SELECT COLUMN_ADD( dyncol, 7, '22:22:22', 8, REPEAT('x',270) AS CHAR ) as ex FROM t1;
|
||
delete from t1;
|
||
INSERT INTO t1 SET dyncol = COLUMN_CREATE( 'a', REPEAT('k',487), 'b', REPEAT('x',464) );
|
||
--error 0,ER_DYN_COL_WRONG_FORMAT
|
||
SELECT COLUMN_ADD( dyncol, 'a', '22:22:22', 'c', REPEAT('x',270) AS CHAR ) as ex FROM t1;
|
||
|
||
DROP table t1;
|
||
|
||
--echo #
|
||
--echo # MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column
|
||
--echo #
|
||
SELECT
|
||
column_get(column_create(1, -999999999999999 AS int), 1 AS TIME) AS t1,
|
||
column_get(column_create(1, -9223372036854775808 AS int), 1 AS TIME) AS t2;
|
||
|
||
--echo #
|
||
--echo # end of 5.3 tests
|
||
--echo #
|
||
|
||
#
|
||
# MySQL Bug#16997513 MY_STRTOLL10 ACCEPTING OVERFLOWED UNSIGNED LONG LONG VALUES AS NORMAL ONES
|
||
# (incorrect overflow check in my_strtoll10())
|
||
#
|
||
select column_get(column_create(1, "18446744073709552001" as char), 1 as int) as ex;
|
||
|
||
--echo #
|
||
--echo # MDEV-7505 - Too large scale in DECIMAL dynamic column getter crashes
|
||
--echo # mysqld
|
||
--echo #
|
||
--error ER_TOO_BIG_SCALE
|
||
SELECT COLUMN_GET(`x`, 'y' AS DECIMAL(5,50));
|
||
|
||
--echo #
|
||
--echo # test of symbolic names
|
||
--echo #
|
||
--echo # creation test (names)
|
||
set names utf8;
|
||
--disable_service_connection
|
||
select hex(column_create("адын", 1212));
|
||
--enable_service_connection
|
||
select hex(column_create("1212", 1212));
|
||
select hex(column_create(1212, 2, "www", 3));
|
||
select hex(column_create("1212", 2, "www", 3));
|
||
select hex(column_create("1212", 2, 3, 3));
|
||
--disable_service_connection
|
||
select hex(column_create("1212", 2, "адын", 1, 3, 3));
|
||
--enable_service_connection
|
||
set names latin1;
|
||
|
||
--echo # fetching column test (names)
|
||
set names utf8;
|
||
select column_get(column_create("адын", 1212), "адын" as int) as ex;
|
||
select column_get(column_create("1212", 2, "адын", 1, 3, 3), "адын" as int) as ex;
|
||
select column_get(column_create("1212", 2, "адын", 1, 3, 3), 1212 as int) as ex;
|
||
select column_get(column_create("1212", 2, "адын", 1, 3, 3), "3" as int) as ex;
|
||
select column_get(column_create("1212", 2, "адын", 1, 3, 3), 3 as int) as ex;
|
||
select column_get(column_create("1212", 2, "адын", 1, 3, 3), 4 as int) as ex;
|
||
select column_get(column_create("1212", 2, "адын", 1, 3, 3), "4" as int) as ex;
|
||
set names latin1;
|
||
|
||
--echo # column existence test (names)
|
||
set names utf8;
|
||
select column_exists(column_create("адын", 1212), "адын") as ex;
|
||
select column_exists(column_create("адын", 1212), "aады") as ex;
|
||
select column_exists(column_create("1212", 2, "адын", 1, 3, 3), "адын") as ex;
|
||
select column_exists(column_create("1212", 2, "адын", 1, 3, 3), 1212) as ex;
|
||
select column_exists(column_create("1212", 2, "адын", 1, 3, 3), "3") as ex;
|
||
select column_exists(column_create("1212", 2, "адын", 1, 3, 3), 3) as ex;
|
||
select column_exists(column_create("1212", 2, "адын", 1, 3, 3), 4) as ex;
|
||
select column_exists(column_create("1212", 2, "адын", 1, 3, 3), "4") as ex;
|
||
set names latin1;
|
||
|
||
--echo # column changing test (names)
|
||
--disable_service_connection
|
||
select hex(column_add(column_create(1, "AAA"), "b", "BBB")) as ex;
|
||
select hex(column_add(column_create("1", "AAA"), "b", "BBB")) as ex;
|
||
select column_get(column_add(column_create(1, "AAA"), "b", "BBB"), 1 as char) as ex;
|
||
select column_get(column_add(column_create(1, "AAA"), "b", "BBB"), "b" as char) as ex;
|
||
select hex(column_add(column_create("a", "AAA"), 1, "BBB")) as ex;
|
||
select hex(column_add(column_create("a", "AAA"), "1", "BBB")) as ex;
|
||
--enable_service_connection
|
||
select hex(column_add(column_create("a", 1212 as integer), "b", "1212" as integer)) as ex;
|
||
select hex(column_add(column_create("a", 1212 as integer), "a", "1212" as integer)) as ex;
|
||
select hex(column_add(column_create("a", 1212 as integer), "a", NULL as integer)) as ex;
|
||
select hex(column_add(column_create("a", 1212 as integer), "b", NULL as integer)) as ex;
|
||
select hex(column_add(column_create("a", 1212 as integer), "b", 1212 as integer, "a", 11 as integer)) as ex;
|
||
select column_get(column_add(column_create("a", 1212 as integer), "b", 1212 as integer, "a", 11 as integer), "a" as integer) as ex;
|
||
select column_get(column_add(column_create("a", 1212 as integer), "b", 1212 as integer, "a", 11 as integer), "b" as integer) as ex;
|
||
select hex(column_add(column_create("a", 1212 as integer), "a", 1212 as integer, "b", 11 as integer)) as ex;
|
||
select hex(column_add(column_create("a", NULL as integer), "a", 1212 as integer, "b", 11 as integer)) as ex;
|
||
select hex(column_add(column_create("a", 1212 as integer, "b", 1212 as integer), "a", 11 as integer)) as ex;
|
||
select hex(column_add(column_create("a", 1), "a", null)) as ex;
|
||
select column_list(column_add(column_create("a", 1), "a", null)) as ex;
|
||
select column_list(column_add(column_create("a", 1), "a", "")) as ex;
|
||
select hex(column_add("", "a", 1)) as ex;
|
||
|
||
-- echo # column delete (names)
|
||
select hex(column_delete(column_create("a", 1212 as integer, "b", 1212 as integer), "a")) as ex;
|
||
select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "b")) as ex;
|
||
select hex(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer)) as ex;
|
||
select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "c")) as ex;
|
||
select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "d")) as ex;
|
||
select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "b", "a")) as ex;
|
||
select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "b", "c")) as ex;
|
||
select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "a", "b", "c")) as ex;
|
||
select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "a", "b", "c", "e")) as ex;
|
||
select hex(column_delete(column_create("a", 1), "a")) as ex;
|
||
select hex(column_delete("", "a")) as ex;
|
||
|
||
--echo #
|
||
--echo # MDEV-458 DNAMES: Server crashes on using an unquoted string
|
||
--echo # as a dynamic column name
|
||
--echo #
|
||
--error ER_BAD_FIELD_ERROR
|
||
select COLUMN_CREATE(color, "black");
|
||
|
||
--echo #
|
||
--echo # MDEV-489 Assertion `offset < 0x1f' failed in
|
||
--echo # type_and_offset_store on COLUMN_ADD
|
||
--echo #
|
||
CREATE TABLE t1 (f1 tinyblob);
|
||
|
||
INSERT INTO t1 VALUES (COLUMN_CREATE('col1', REPEAT('a',30)));
|
||
select column_check(f1) from t1;
|
||
UPDATE t1 SET f1 = COLUMN_ADD( f1, REPEAT('b',211), 'val2' );
|
||
# we can't detect last string cut with 100% probability,
|
||
# because we detect it by string end
|
||
select column_check(f1) from t1;
|
||
UPDATE t1 SET f1 = COLUMN_ADD( f1, REPEAT('c',211), 'val3' );
|
||
select column_check(f1) from t1;
|
||
|
||
drop table t1;
|
||
|
||
--echo #
|
||
--echo # MDEV-490/MDEV-491 null as arguments
|
||
--echo #
|
||
SELECT COLUMN_GET( COLUMN_CREATE( 'col', 'val' ), NULL AS CHAR ) as ex;
|
||
SELECT COLUMN_GET( NULL, 'col' as char ) as ex;
|
||
SELECT COLUMN_EXISTS( COLUMN_CREATE( 'col', 'val' ), NULL) as ex;
|
||
SELECT COLUMN_EXISTS( NULL, 'col') as ex;
|
||
SELECT COLUMN_CREATE( NULL, 'val' ) as ex;
|
||
SELECT COLUMN_ADD( NULL, 'val', 'col') as ex;
|
||
|
||
--echo #
|
||
--echo # MDEV-488: Assertion `column_name->length < 255' failed on a
|
||
--echo # column name with length 255 (precisely)
|
||
--echo #
|
||
SELECT hex(COLUMN_CREATE(REPEAT('a',255),1));
|
||
--error ER_DYN_COL_DATA
|
||
SELECT hex(COLUMN_CREATE(REPEAT('a',65536),1));
|
||
|
||
--echo #
|
||
--echo # JSON conversion
|
||
--echo #
|
||
select column_json(column_create("int", -1212 as int, "uint", 12334 as unsigned int, "decimal", "23.344" as decimal, "double", 1.23444e50 as double, "string", 'gdgd\\dhdjh"dhdhd' as char, "time", "0:45:49.000001" AS time, "datetime", "2011-04-05 0:45:49.000001" AS datetime, "date", "2011-04-05" AS date)) as ex;
|
||
select column_json(column_create(1, -1212 as int, 2, 12334 as unsigned int, 3, "23.344" as decimal, 4, 1.23444e50 as double, 5, 'gdgd\\dhdjh"dhdhd' as char, 6, "0:45:49.000001" AS time, 7, "2011-04-05 0:45:49.000001" AS datetime, 8, "2011-04-05" AS date)) as ex;
|
||
|
||
--echo #
|
||
--echo # CHECK test
|
||
--echo #
|
||
SELECT COLUMN_CHECK(COLUMN_CREATE(1,'a'));
|
||
SELECT COLUMN_CHECK('abracadabra');
|
||
SELECT COLUMN_CHECK('');
|
||
SELECT COLUMN_CHECK(NULL);
|
||
|
||
--echo #
|
||
--echo # escaping check
|
||
--echo #
|
||
select column_json(column_create("string", "'\"/\\`.,whatever")),hex(column_create("string", "'\"/\\`.,whatever"));
|
||
|
||
--echo #
|
||
--echo # embedding test
|
||
--echo #
|
||
select column_json(column_create("val", "val", "emb", column_create("val2", "val2"))) as ex;
|
||
select column_json(column_create(1, "val", 2, column_create(3, "val2"))) as ex;
|
||
|
||
--echo #
|
||
--echo # Time encoding
|
||
--echo #
|
||
select hex(column_create("t", "800:46:06.23434" AS time)) as hex,
|
||
column_json(column_create("t", "800:46:06.23434" AS time)) as js;
|
||
select hex(column_create(1, "800:46:06.23434" AS time)) as hex,
|
||
column_json(column_create(1, "800:46:06.23434" AS time)) as js;
|
||
|
||
select hex(column_create("t", "800:46:06" AS time)) as hex,
|
||
column_json(column_create("t", "800:46:06" AS time)) as js;
|
||
select hex(column_create(1, "800:46:06" AS time)) as hex,
|
||
column_json(column_create(1, "800:46:06" AS time)) as js;
|
||
|
||
select hex(column_create("t", "2012-12-21 10:46:06.23434" AS datetime)) as hex,
|
||
column_json(column_create("t", "2012-12-21 10:46:06.23434" AS datetime)) as js;
|
||
select hex(column_create(1, "2012-12-21 10:46:06.23434" AS datetime)) as hex,
|
||
column_json(column_create(1, "2012-12-21 10:46:06.23434" AS datetime)) as js;
|
||
|
||
select hex(column_create("t", "2012-12-21 10:46:06" AS datetime)) as hex,
|
||
column_json(column_create("t", "2012-12-21 10:46:06" AS datetime)) as js;
|
||
select hex(column_create(1, "2012-12-21 10:46:06" AS datetime)) as hex,
|
||
column_json(column_create(1, "2012-12-21 10:46:06" AS datetime)) as js;
|
||
|
||
--echo #
|
||
--echo # MDEV-4849: Out of memory error and valgrind warnings on COLUMN_ADD
|
||
--echo #
|
||
CREATE TABLE t1 (dyncol tinyblob) ENGINE=MyISAM;
|
||
|
||
INSERT INTO t1 SET dyncol = COLUMN_CREATE( 3, REPEAT('a',330), 4, 'x' );
|
||
--error ER_DYN_COL_WRONG_FORMAT
|
||
SELECT COLUMN_ADD( COLUMN_ADD( dyncol, 1, REPEAT('b',130) ), 3, 'y' ) FROM t1;
|
||
|
||
DROP TABLE t1;
|
||
|
||
--echo #
|
||
--echo #MDEV-5840: group_concat( column_json(dynamic_column )) return empty
|
||
--echo #result
|
||
--echo #
|
||
create table t1 (dyn blob);
|
||
insert into t1 values (column_create('name1','value1','name2','value2'));
|
||
select group_concat(cast(column_json(dyn) as char)) as ex from t1;
|
||
|
||
drop table t1;
|
||
|
||
--echo #
|
||
--echo # MDEV-7116: Dynamic column hangs/segfaults
|
||
--echo #
|
||
create table t1 (
|
||
impressions mediumblob
|
||
);
|
||
|
||
insert into t1 values ("");
|
||
|
||
update t1
|
||
set impressions = column_add(impressions,
|
||
'total', 12,
|
||
'2014-10-28 16:00:00', 3,
|
||
'2014-10-30 15:00:00', 3,
|
||
'2014-11-04 09:00:00', 6
|
||
);
|
||
update t1
|
||
set impressions = column_add(impressions,
|
||
'total', "a12",
|
||
'2014-10-28 16:00:00', "a3",
|
||
'2014-10-30 15:00:00', "a3",
|
||
'2014-11-04 09:00:00', "a6"
|
||
);
|
||
|
||
drop table t1;
|
||
|
||
--echo #
|
||
--echo # MDEV-8565: COLUMN_CHECK fails on valid data
|
||
--echo #
|
||
|
||
SELECT COLUMN_CHECK(COLUMN_CREATE('a',0,'b','1'));
|
||
|
||
SELECT COLUMN_CHECK(COLUMN_CREATE('a',1,'b','1'));
|
||
|
||
SELECT COLUMN_JSON(COLUMN_CREATE('a',0,'b','1'));
|
||
|
||
SELECT COLUMN_JSON(COLUMN_CREATE('a',1,'b','1'));
|
||
|
||
--echo #
|
||
--echo # MDEV-8401: COLUMN_CREATE(name, value as DOUBLE) results in string
|
||
--echo #
|
||
SELECT COLUMN_JSON(
|
||
COLUMN_CREATE(
|
||
'one', 123.456,
|
||
'two', 123.456 as DOUBLE
|
||
)
|
||
) as ex;
|
||
|
||
--echo #
|
||
--echo # MDEV-8521: Drastic loss of precision in COLUMN_JSON() on DOUBLEs
|
||
--echo #
|
||
|
||
select column_get(column_create('float', 1.23456789012345E+100 as double), 'float' as double) as ex;
|
||
select column_json(column_create('float', 1.23456789012345E+100 as double)) as ex;
|
||
select column_json(column_create('float', 1.23456789012345E+10 as double)) as ex;
|
||
|
||
--echo #
|
||
--echo # MDEV-9147: Character set is ignored in Dynamic Column for saved string
|
||
--echo #
|
||
SET NAMES utf8;
|
||
SELECT COLUMN_GET(COLUMN_CREATE(1, 0xC2A2 AS CHAR CHARACTER SET latin1), 1 AS CHAR CHARACTER SET utf8) AS a;
|
||
SELECT COLUMN_GET(COLUMN_CREATE(1, 0xC2A2 AS CHAR CHARACTER SET utf8), 1 AS CHAR CHARACTER SET utf8) AS a;
|
||
|
||
--echo #
|
||
--echo # MDEV-9167: COLUMN_CHECK fails on valid decimal data
|
||
--echo #
|
||
|
||
SELECT COLUMN_CHECK(COLUMN_CREATE('a',0 AS DECIMAL,'b',1 AS DECIMAL)) as ex;
|
||
|
||
SELECT COLUMN_CHECK(COLUMN_CREATE('a',1 AS DECIMAL,'b',1 AS DECIMAL)) as ex;
|
||
|
||
SELECT COLUMN_JSON(COLUMN_CREATE('a',0 AS DECIMAL,'b',1 AS DECIMAL)) as ex;
|
||
|
||
SELECT COLUMN_JSON(COLUMN_CREATE('a',1 AS DECIMAL,'b',1 AS DECIMAL)) as ex;
|
||
|
||
|
||
--echo #
|
||
--echo # MDEV-7533: COLUMN_JSON() doesn't escape control characters
|
||
--echo # in string values
|
||
--echo #
|
||
SELECT COLUMN_JSON(COLUMN_CREATE('test','"\\\t\n\Z')) AS json;
|
||
SELECT COLUMN_JSON(COLUMN_CREATE('test','First line\nSecond line')) AS json;
|
||
|
||
--echo #
|
||
--echo # MDEV-15230: column_json breaks cyrillic in 10.1.31
|
||
--echo #
|
||
set names utf8;
|
||
create table t1 (b blob);
|
||
insert into t1 values (column_create('description',column_create('title','Описание')));
|
||
select column_json(b) from t1;
|
||
drop table t1;
|
||
|
||
--echo #
|
||
--echo # end of 10.0 tests
|
||
--echo #
|
||
|
||
--echo #
|
||
--echo # Start of 10.2 tests
|
||
--echo #
|
||
|
||
#
|
||
# Item_func_dyncol_add::print
|
||
#
|
||
create view v1 as select column_get(column_add(column_create(1 , 'blue' as char), 2, 'ttt'), 1 as char);
|
||
show create view v1;
|
||
select * from v1;
|
||
drop view v1;
|
||
|
||
--echo #
|
||
--echo # MDEV-10134 Add full support for DEFAULT
|
||
--echo #
|
||
CREATE TABLE t1 (
|
||
name VARCHAR(10),
|
||
value VARCHAR(10),
|
||
dyncol0 BLOB DEFAULT COLUMN_CREATE(name, value),
|
||
value_dyncol0_name0 VARCHAR(10) DEFAULT COLUMN_GET(dyncol0, 'name0' AS CHAR),
|
||
dyncol1 BLOB DEFAULT COLUMN_ADD(dyncol0, 'name1', 'value1'),
|
||
value_dyncol1_name1 VARCHAR(10) DEFAULT COLUMN_GET(dyncol1, 'name1' AS CHAR),
|
||
dyncol2 BLOB DEFAULT COLUMN_DELETE(dyncol1, 'name1'),
|
||
dyncol2_exists_name0 INT DEFAULT COLUMN_EXISTS(dyncol2, 'name0'),
|
||
dyncol2_exists_name1 INT DEFAULT COLUMN_EXISTS(dyncol2, 'name1'),
|
||
dyncol2_check INT DEFAULT COLUMN_CHECK(dyncol2),
|
||
dyncol1_list TEXT DEFAULT COLUMN_LIST(dyncol1),
|
||
dyncol1_json TEXT DEFAULT COLUMN_JSON(dyncol1)
|
||
);
|
||
SHOW CREATE TABLE t1;
|
||
INSERT INTO t1 (name,value) VALUES ('name0', 'value0');
|
||
SELECT value_dyncol0_name0, value_dyncol1_name1 FROM t1;
|
||
SELECT dyncol2_check, dyncol2_exists_name0, dyncol2_exists_name1 FROM t1;
|
||
SELECT dyncol1_list FROM t1;
|
||
SELECT dyncol1_json FROM t1;
|
||
DROP TABLE t1;
|
||
|
||
|
||
--echo #
|
||
--echo # End of 10.2 tests
|
||
--echo #
|
||
|
||
--echo #
|
||
--echo # MDEV-32140: Valgrind/MSAN warnings in dynamic_column_update_move_left
|
||
--echo #
|
||
SELECT COLUMN_GET(COLUMN_ADD(COLUMN_CREATE(1,10),2,NULL,1,NULL),3 AS INTEGER) as ex;
|
||
SELECT HEX(COLUMN_ADD(COLUMN_CREATE(1,10),2,NULL,1,NULL)) as ex;
|
||
|
||
--echo #
|
||
--echo # End of 10.4 tests
|
||
--echo #
|
||
|
||
--echo #
|
||
--echo # MDEV-33788 HEX(COLUMN_CREATE(.. AS CHAR ...)) fails with --view-protocol
|
||
--echo #
|
||
|
||
SELECT hex(column_create(1,'a' AS CHAR CHARACTER SET utf8mb3 COLLATE utf8mb3_bin)) AS ex;
|
||
SELECT hex(column_add(column_create(
|
||
1, 'a' AS CHAR CHARACTER SET utf8mb3 COLLATE utf8mb3_bin),
|
||
2, 'b' AS CHAR CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci)) AS ex;
|
||
|
||
--echo #
|
||
--echo # MDEV-31566 Fix buffer overrun of column_json function
|
||
--echo #
|
||
|
||
|
||
create table t1 (
|
||
c1 varchar(32) primary key,
|
||
d1 blob
|
||
);
|
||
insert into t1 values ('var', 0x0402000A0000000300030023076A736E7375626A6563742E0005006C0027000200290002002B0002002D0002002F0002000C31000C3B000C4B000C51000F62006631663266336634663509E5A79AE8BF9CE6B48B0FE8819AE9809AE98791E6A1A5E5BA970537343530301031313634332F393634352F31313630300C080000000000EFBFBDEFBFBD192E), ('zzz', 0x0402000900000003000300740C6A736E766F6C756D652E000900EFBFBD004300020045000200470003004A0004004E00050053000500580005005D000500620005000C67000C6A000C6D000C7000052C00051B00052C000CEFBFBD0007EFBFBD006638663966313070696332626F785F63626F785F67626F785F6B626F785F7666355F696402343402343402333241687474703A2F2F6F73732E68646238382E636F6D2F302F70686F746F2F30373865653765376336343634616236386130343833373333323636613532612E67696608302E303532323732244F1E00030180C106);
|
||
|
||
select c1,column_json(d1) as not_crashing from t1 order by c1;
|
||
|
||
drop table t1;
|
||
|
||
--echo # End of 10.5 tests
|