path: root/mysql-test/main/dyncol.result

#
# column create
#
select hex(COLUMN_CREATE(1, NULL AS char character set utf8)) as exp;
exp
000000
select hex(COLUMN_CREATE(1, "afaf" AS char character set utf8)) as ex;
ex
0001000100032161666166
select hex(COLUMN_CREATE(1, 1212 AS char character set utf8)) as ex;
ex
0001000100032131323132
select hex(COLUMN_CREATE(1, 12.12 AS char character set utf8)) as ex;
ex
0001000100032131322E3132
select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS char character set utf8)) as ex;
ex
000100010003213939393939393939393939393939393939393939393939393939393939
select hex(COLUMN_CREATE(1, NULL AS unsigned int));
hex(COLUMN_CREATE(1, NULL AS unsigned int))
000000
select hex(COLUMN_CREATE(1, 1212 AS unsigned int));
hex(COLUMN_CREATE(1, 1212 AS unsigned int))
000100010001BC04
select hex(COLUMN_CREATE(1, 7 AS unsigned int));
hex(COLUMN_CREATE(1, 7 AS unsigned int))
00010001000107
select hex(COLUMN_CREATE(1, 8 AS unsigned int));
hex(COLUMN_CREATE(1, 8 AS unsigned int))
00010001000108
select hex(COLUMN_CREATE(1, 127 AS unsigned int));
hex(COLUMN_CREATE(1, 127 AS unsigned int))
0001000100017F
select hex(COLUMN_CREATE(1, 128 AS unsigned int));
hex(COLUMN_CREATE(1, 128 AS unsigned int))
00010001000180
select hex(COLUMN_CREATE(1, 12.12 AS unsigned int));
hex(COLUMN_CREATE(1, 12.12 AS unsigned int))
0001000100010C
select hex(COLUMN_CREATE(1, ~0));
hex(COLUMN_CREATE(1, ~0))
000100010001FFFFFFFFFFFFFFFF
select hex(COLUMN_CREATE(1, -1));
hex(COLUMN_CREATE(1, -1))
00010001000001
select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS unsigned int)) as ex;
ex
000100010001FFFFFFFFFFFFFF7F
Warnings:
Warning	1916	Got overflow when converting '99999999999999999999999999999' to INT. Value truncated
select hex(COLUMN_CREATE(1, NULL AS int));
hex(COLUMN_CREATE(1, NULL AS int))
000000
select hex(COLUMN_CREATE(1, 1212 AS int));
hex(COLUMN_CREATE(1, 1212 AS int))
0001000100007809
select hex(COLUMN_CREATE(1, 7 AS int));
hex(COLUMN_CREATE(1, 7 AS int))
0001000100000E
select hex(COLUMN_CREATE(1, 8 AS int));
hex(COLUMN_CREATE(1, 8 AS int))
00010001000010
select hex(COLUMN_CREATE(1, 127 AS int));
hex(COLUMN_CREATE(1, 127 AS int))
000100010000FE
select hex(COLUMN_CREATE(1, 128 AS int));
hex(COLUMN_CREATE(1, 128 AS int))
0001000100000001
select hex(COLUMN_CREATE(1, 12.12 AS int));
hex(COLUMN_CREATE(1, 12.12 AS int))
00010001000018
select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS int)) as ex;
ex
000100010000FEFFFFFFFFFFFFFF
Warnings:
Warning	1916	Got overflow when converting '99999999999999999999999999999' to INT. Value truncated
select hex(COLUMN_CREATE(1, NULL AS double));
hex(COLUMN_CREATE(1, NULL AS double))
000000
select hex(COLUMN_CREATE(1, 1212 AS double));
hex(COLUMN_CREATE(1, 1212 AS double))
0001000100020000000000F09240
select hex(COLUMN_CREATE(1, 12.12 AS double));
hex(COLUMN_CREATE(1, 12.12 AS double))
0001000100023D0AD7A3703D2840
select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS double)) as ex;
ex
00010001000221D7E6FAE031F445
select hex(COLUMN_CREATE(1, NULL AS decimal));
hex(COLUMN_CREATE(1, NULL AS decimal))
000000
select hex(COLUMN_CREATE(1, 1212 AS decimal));
hex(COLUMN_CREATE(1, 1212 AS decimal))
000100010004040084BC
select hex(COLUMN_CREATE(1, 7 AS decimal));
hex(COLUMN_CREATE(1, 7 AS decimal))
000100010004010087
select hex(COLUMN_CREATE(1, 8 AS decimal));
hex(COLUMN_CREATE(1, 8 AS decimal))
000100010004010088
select hex(COLUMN_CREATE(1, 127 AS decimal));
hex(COLUMN_CREATE(1, 127 AS decimal))
0001000100040300807F
select hex(COLUMN_CREATE(1, 128 AS decimal));
hex(COLUMN_CREATE(1, 128 AS decimal))
00010001000403008080
select hex(COLUMN_CREATE(1, 12.12 AS decimal));
hex(COLUMN_CREATE(1, 12.12 AS decimal))
00010001000402028C0C
select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS decimal)) as ex;
ex
0001000100041D00E33B9AC9FF3B9AC9FF3B9AC9FF
select hex(COLUMN_CREATE(1, NULL AS date));
hex(COLUMN_CREATE(1, NULL AS date))
000000
select hex(COLUMN_CREATE(1, "2011-04-05" AS date));
hex(COLUMN_CREATE(1, "2011-04-05" AS date))
00010001000685B60F
select hex(COLUMN_CREATE(1, NULL AS time));
hex(COLUMN_CREATE(1, NULL AS time))
000000
select hex(COLUMN_CREATE(1, "0:45:49.000001" AS time));
hex(COLUMN_CREATE(1, "0:45:49.000001" AS time))
000100010007010010B70000
select hex(COLUMN_CREATE(1, NULL AS datetime));
hex(COLUMN_CREATE(1, NULL AS datetime))
000000
select hex(COLUMN_CREATE(1, "2011-04-05 0:45:49.000001" AS datetime)) as ex;
ex
00010001000585B60F010010B70000
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;
ex
01080001000300020029000300380004004A0005008C000600AE000700C7000800F5002161666166BC0478093D0AD7A3703D284002028C0C85B60F010010B7000485B60F010010B70000
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;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select hex(column_create(1,'afaf' AS char charset utf8mb3 ,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));
hex(column_create(1, 0.0 AS decimal))
000100010004
select hex(column_create(1, 1.0 AS decimal));
hex(column_create(1, 1.0 AS decimal))
00010001000401018100
#
# column get uint
#
select column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned int) as ex;
ex
1212
explain extended
select column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned int) as ex;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select column_get(column_create(1,1212 AS unsigned int),1 as unsigned) AS `ex`
explain extended
select column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned) as ex;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	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;
ex
1212
select column_get(column_create(1, 1212 AS double), 1 as unsigned int) as ex;
ex
1212
select column_get(column_create(1, 1212 AS int), 1 as unsigned int) as ex;
ex
1212
select column_get(column_create(1, "1212" AS char), 1 as unsigned int) as ex;
ex
1212
select column_get(column_create(1, "2011-04-05" AS date), 1 as unsigned int) as ex;
ex
20110405
select column_get(column_create(1, "8:46:06.23434" AS time), 1 as unsigned int) as ex;
ex
84606
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as unsigned int) as ex;
ex
20110405084606
select column_get(column_create(1, NULL AS unsigned int), 1 as unsigned int) as ex;
ex
NULL
# column geint truncation & warnings
select column_get(column_create(1, -1212 AS int), 1 as unsigned int) as ex;
ex
18446744073709550404
Warnings:
Note	1105	Cast to unsigned converted negative integer to it's positive complement
select column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as unsigned int) as ex;
ex
18446744073709551615
Warnings:
Warning	1916	Got overflow when converting '99999999999999999999999999999' to UNSIGNED INT. Value truncated
select column_get(column_create(1, 999.9999999999999999 AS decimal), 1 as unsigned int) as ex;
ex
1000
select column_get(column_create(1, -1 AS decimal), 1 as unsigned int) as ex;
ex
0
Warnings:
Warning	1916	Got overflow when converting '-1' to UNSIGNED INT. Value truncated
select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as unsigned int) as ex;
ex
18446744073709551615
Warnings:
Warning	1916	Got overflow when converting '1e29' to UNSIGNED INT. Value truncated
select column_get(column_create(1, 999.9 AS double), 1 as unsigned int) as ex;
ex
1000
select column_get(column_create(1, -1 AS double), 1 as unsigned int) as ex;
ex
0
Warnings:
Warning	1916	Got overflow when converting '-1' to UNSIGNED INT. Value truncated
select column_get(column_create(1, "1212III" AS char), 1 as unsigned int) as ex;
ex
1212
Warnings:
Warning	1918	Encountered illegal value '1212III' when converting to UNSIGNED INT
#
# column get int
#
select column_get(column_create(1, 1212 AS int), 1 as int) as ex;
ex
1212
explain extended
select column_get(column_create(1, 1212 AS int), 1 as int) as ex;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select column_get(column_create(1,1212 AS int),1 as signed) AS `ex`
explain extended
select column_get(column_create(1, 1212 AS int), 1 as signed int) as ex;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select column_get(column_create(1,1212 AS int),1 as signed) AS `ex`
select column_get(column_create(1, -1212 AS int), 1 as int) as ex;
ex
-1212
select column_get(column_create(1, 1212 AS decimal), 1 as int) as ex;
ex
1212
select column_get(column_create(1, 1212 AS double), 1 as int) as ex;
ex
1212
select column_get(column_create(1, 1212 AS unsigned int), 1 as int) as ex;
ex
1212
select column_get(column_create(1, "1212" AS char), 1 as int) as ex;
ex
1212
select column_get(column_create(1, "-1212" AS char), 1 as int) as ex;
ex
-1212
select column_get(column_create(1, "2011-04-05" AS date), 1 as int) as ex;
ex
20110405
select column_get(column_create(1, "8:46:06.23434" AS time), 1 as int) as ex;
ex
84606
select column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as int) as ex;
ex
84606
select column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as int) as ex;
ex
-8084606
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(6)), 1 as int) as ex;
ex
20110405084606
select column_get(column_create(1, NULL AS int), 1 as int) as ex;
ex
NULL
#column gett truncation & warnings
select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as int) as ex;
ex
-1
Warnings:
Note	1105	Cast to signed converted positive out-of-range integer to it's negative complement
select column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as int) as ex;
ex
9223372036854775807
Warnings:
Warning	1916	Got overflow when converting '99999999999999999999999999999' to INT. Value truncated
select column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as int) as ex;
ex
-9223372036854775808
Warnings:
Warning	1916	Got overflow when converting '-99999999999999999999999999999' to INT. Value truncated
select column_get(column_create(1, 999.9999999999999999 AS decimal), 1 as int) as ex;
ex
1000
select column_get(column_create(1, 999.9 AS double), 1 as int) as ex;
ex
1000
select column_get(column_create(1, -99999999999999999999999999999 AS double), 1 as int) as ex;
ex
-9223372036854775808
Warnings:
Warning	1916	Got overflow when converting '-1e29' to INT. Value truncated
select column_get(column_create(1, "-1212III" AS char), 1 as int) as ex;
ex
-1212
Warnings:
Warning	1918	Encountered illegal value '-1212III' when converting to INT
select column_get(column_create(1, "1212III" AS char), 1 as int) as ex;
ex
1212
Warnings:
Warning	1918	Encountered illegal value '1212III' when converting to INT
select column_get(COLUMN_CREATE(1, ~0), 1 as signed) as ex;
ex
-1
Warnings:
Note	1105	Cast to signed converted positive out-of-range integer to it's negative complement
select column_get(COLUMN_CREATE(1, ~0), 1 as unsigned) as ex;
ex
18446744073709551615
select column_get(COLUMN_CREATE(1, -1), 1 as signed) as ex;
ex
-1
select column_get(COLUMN_CREATE(1, -1), 1 as unsigned) as ex;
ex
18446744073709551615
Warnings:
Note	1105	Cast to unsigned converted negative integer to it's positive complement
#
#column get char
#
select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset utf8) as ex;
ex
1212
explain extended
select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset utf8) as ex;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select column_get(column_create(1,'1212' AS char charset utf8mb3 ),1 as char charset utf8mb3) AS `ex`
select column_get(column_create(1, 1212 AS unsigned int), 1 as char charset utf8) as ex;
ex
1212
select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as char charset utf8) as ex;
ex
18446744073709551615
select column_get(column_create(1, 1212 AS int), 1 as char charset utf8) as ex;
ex
1212
select column_get(column_create(1, -1212 AS int), 1 as char charset utf8) as ex;
ex
-1212
select column_get(column_create(1, 9223372036854775807 AS int), 1 as char charset utf8) as ex;
ex
9223372036854775807
select column_get(column_create(1, -9223372036854775808 AS int), 1 as char charset utf8) as ex;
ex
-9223372036854775808
select column_get(column_create(1, 1212.12 AS decimal), 1 as char charset utf8) as ex;
ex
1212.12
select column_get(column_create(1, 1212.12 AS double), 1 as char charset utf8) as ex;
ex
1212.12
select column_get(column_create(1, "2011-04-05" AS date), 1 as char charset utf8) as ex;
ex
2011-04-05
select column_get(column_create(1, "8:46:06.23434" AS time), 1 as char charset utf8) as ex;
ex
08:46:06.234340
select column_get(column_create(1, "8:46:06.23434" AS time(0)), 1 as char charset utf8) as ex;
ex
08:46:06.234340
select column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as char charset utf8) as ex;
ex
08:46:06.234340
select column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as char charset utf8) as ex;
ex
-808:46:06.234340
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as char charset utf8) as ex;
ex
2011-04-05 08:46:06.234340
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(0)), 1 as char charset utf8) as ex;
ex
2011-04-05 08:46:06.234340
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(6)), 1 as char charset utf8) as ex;
ex
2011-04-05 08:46:06.234340
select column_get(column_create(1, NULL AS char charset utf8), 1 as char charset utf8) as ex;
ex
NULL
select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset binary) as ex;
ex
1212
explain extended
select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset binary) as ex;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select column_get(column_create(1,'1212' AS char charset utf8mb3 ),1 as char charset binary) AS `ex`
#
# column get real
#
select column_get(column_create(1, 1212.12 AS double), 1 as double) as ex;
ex
1212.12
explain extended
select column_get(column_create(1, 1212.12 AS double), 1 as double) as ex;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	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;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	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;
ex
1.8446744073709552e19
select column_get(column_create(1, 9223372036854775807 AS int), 1 as double) as ex;
ex
9.223372036854776e18
select column_get(column_create(1, -9223372036854775808 AS int), 1 as double) as ex;
ex
-9.223372036854776e18
select column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as double) as ex;
ex
1e29
select column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as double) as ex;
ex
-1e29
select column_get(column_create(1, "2011-04-05" AS date), 1 as double) as ex;
ex
20110405
select column_get(column_create(1, "8:46:06.23434" AS time), 1 as double) as ex;
ex
84606.23434
select column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as double) as ex;
ex
84606.23434
select column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as double) as ex;
ex
-8084606.23434
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as double) as ex;
ex
20110405084606.234
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(6)), 1 as double) as ex;
ex
20110405084606.234
select round(column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as double(20,6)),3) as ex;
ex
20110405084606.234
select column_get(column_create(1, NULL AS double), 1 as double) as ex;
ex
NULL
# column get real truncation & warnings
select column_get(column_create(1, "1223.5aa" AS char), 1 as double) as ex;
ex
1223.5
Warnings:
Warning	1918	Encountered illegal value '1223.5aa' when converting to DOUBLE
select column_get(column_create(1, "aa" AS char), 1 as double) as ex;
ex
0
Warnings:
Warning	1918	Encountered illegal value 'aa' when converting to DOUBLE
select column_get(column_create(1, "1223.5555" AS double), 1 as double(5,2)) as ex;
ex
999.99
Warnings:
Note	1264	Out of range value for column 'ex' at row 1
select column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2)) as ex;
ex
9.99
Warnings:
Note	1264	Out of range value for column 'ex' at row 1
#
# column get decimal
#
select column_get(column_create(1, 1212.12 AS double), 1 as decimal) as ex;
ex
1212
select column_get(column_create(1, 1212.12 AS double), 1 as decimal(6,2)) as ex;
ex
1212.12
explain extended
select column_get(column_create(1, 1212.12 AS double), 1 as decimal) as ex;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select column_get(column_create(1,1212.12 AS double),1 as decimal(10,0)) AS `ex`
explain extended
select column_get(column_create(1, 1212.12 AS double), 1 as decimal(6,2)) as ex;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	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;
ex
18446744073709551615
select column_get(column_create(1, 9223372036854775807 AS int), 1 as decimal(32,0)) as ex;
ex
9223372036854775807
select column_get(column_create(1, -9223372036854775808 AS int), 1 as decimal(32,0)) as ex;
ex
-9223372036854775808
select column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as decimal(40,10)) as ex;
ex
-99999999999999999999999999999.0000000000
select column_get(column_create(1, "2011-04-05" AS date), 1 as decimal(32,6)) as ex;
ex
20110405.000000
select column_get(column_create(1, "8:46:06.23434" AS time), 1 as decimal(32,6)) as ex;
ex
84606.234340
select column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as decimal(32,6)) as ex;
ex
84606.234340
select column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as decimal(32,6)) as ex;
ex
-8084606.234340
select column_get(column_create(1, "2011-04-05 8:46:06.123456" AS datetime), 1 as decimal(32,6)) as ex;
ex
20110405084606.123456
select column_get(column_create(1, "2011-04-05 8:46:06.123456" AS datetime(6)), 1 as decimal(32,6)) as ex;
ex
20110405084606.123456
select column_get(column_create(1, "2011-04-05 8:46:06.12345678" AS datetime(6)), 1 as decimal(32,8)) as ex;
ex
20110405084606.12345600
Warnings:
Note	1292	Truncated incorrect datetime value: '2011-04-05 8:46:06.12345678'
select column_get(column_create(1, NULL as decimal), 1 as decimal(32,10)) as ex;
ex
NULL
select column_get(column_create(1, "1223.5555" as decimal(10,5)), 1 as decimal(6,2)) as ex;
ex
1223.56
# column get decimal truncation & warnings
select column_get(column_create(1, "1223.5aa" AS char), 1 as decimal(32,10)) as ex;
ex
1223.5000000000
Warnings:
Warning	1918	Encountered illegal value '1223.5aa' when converting to DECIMAL
select column_get(column_create(1, "aa" AS char), 1 as decimal(32,10)) as ex;
ex
0.0000000000
Warnings:
Warning	1918	Encountered illegal value 'aa' when converting to DECIMAL
select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as decimal) as ex;
ex
9999999999
Warnings:
Warning	1264	Out of range value for column 'ex' at row 1
select column_get(column_create(1, 9223372036854775807 AS int), 1 as decimal) as ex;
ex
9999999999
Warnings:
Warning	1264	Out of range value for column 'ex' at row 1
select column_get(column_create(1, -9223372036854775808 AS int), 1 as decimal) as ex;
ex
-9999999999
Warnings:
Warning	1264	Out of range value for column 'ex' at row 1
select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as decimal) as ex;
ex
9999999999
Warnings:
Warning	1264	Out of range value for column 'ex' at row 1
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as decimal) as ex;
ex
9999999999
Warnings:
Warning	1264	Out of range value for column 'ex' at row 1
select column_get(column_create(1, "1223.5555" as double), 1 as decimal(5,2)) as ex;
ex
999.99
Warnings:
Warning	1264	Out of range value for column 'ex' at row 1
select column_get(column_create(1, "-1223.5555" as double), 1 as decimal(5,2)) as ex;
ex
-999.99
Warnings:
Warning	1264	Out of range value for column 'ex' at row 1
select column_get(column_create(1, "1223.5555" AS double), 1 as decimal(3,2)) as ex;
ex
9.99
Warnings:
Warning	1264	Out of range value for column 'ex' at row 1
select column_get(column_create(1, "1223.5555" AS decimal(10,5)), 1 as decimal(3,2)) as ex;
ex
9.99
Warnings:
Warning	1264	Out of range value for column 'ex' at row 1
select column_get(column_create(1, 0.0 AS decimal,2, 0.0 as decimal), 1 as decimal) as ex;
ex
0
#
# column get datetime
#
select column_get(column_create(1, 20010203101112.121314 as double), 1 as datetime) as ex;
ex
2001-02-03 10:11:12
select column_get(column_create(1, 20010203101112.121314 as decimal), 1 as datetime) as ex;
ex
2001-02-03 10:11:12
select column_get(column_create(1, 20010203101112 as unsigned int), 1 as datetime) as ex;
ex
2001-02-03 10:11:12
select column_get(column_create(1, 20010203101112 as int), 1 as datetime) as ex;
ex
2001-02-03 10:11:12
select column_get(column_create(1, "20010203101112" as char), 1 as datetime) as ex;
ex
2001-02-03 10:11:12
select column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as datetime) as ex;
ex
2001-02-03 10:11:12
select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as datetime) as ex;
ex
2001-02-03 10:11:12
select column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as datetime) as ex;
ex
2001-02-03 10:11:12
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as datetime) as ex;
ex
2011-04-05 08:46:06
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as datetime(0)) as ex;
ex
2011-04-05 08:46:06
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as datetime(6)) as ex;
ex
2011-04-05 08:46:06.234340
select column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as datetime) as ex;
ex
2011-00-00 08:46:06
select column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as datetime) as ex;
ex
2011-00-01 08:46:06
select column_get(column_create(1, 20010203 as unsigned int), 1 as datetime) as ex;
ex
2001-02-03 00:00:00
select column_get(column_create(1, 20010203 as int), 1 as datetime) as ex;
ex
2001-02-03 00:00:00
select column_get(column_create(1, 20010203), 1 as datetime) as ex;
ex
2001-02-03 00:00:00
select column_get(column_create(1, 20010203.0), 1 as datetime) as ex;
ex
2001-02-03 00:00:00
select column_get(column_create(1, 20010203.0 as double), 1 as datetime) as ex;
ex
2001-02-03 00:00:00
select column_get(column_create(1, "2001-02-03"), 1 as datetime) as ex;
ex
2001-02-03 00:00:00
select column_get(column_create(1, "20010203"), 1 as datetime) as ex;
ex
2001-02-03 00:00:00
select column_get(column_create(1, 0), 1 as datetime) as ex;
ex
0000-00-00 00:00:00
select column_get(column_create(1, "2001021"), 1 as datetime) as ex;
ex
2020-01-02 01:00:00
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;
ex
2001-02-03 08:46:06
select column_get(column_create(1, "-808:46:06.23434" AS time), 1 as datetime) as ex;
ex
2000-12-31 07:13:53
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;
ex
2011-02-30 18:46:06
select column_get(column_create(1, "0000-00-000" AS CHAR), 1 as datetime) as ex;
ex
0000-00-00 00:00:00
select column_get(column_create(1, "2001-00-02" AS CHAR), 1 as datetime) as ex;
ex
2001-00-02 00:00:00
set @@sql_mode="";
# column get datetime truncation & warnings
select column_get(column_create(1, "1223.5aa" AS char), 1 as datetime) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '1223.5aa'
select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as datetime) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '1.8446744073709552e19'
select column_get(column_create(1, 9223372036854775807 AS int), 1 as datetime) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '9223372036854775807'
select column_get(column_create(1, -9223372036854775808 AS int), 1 as datetime) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '-9223372036854775808'
select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as datetime) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '99999999999999999999999999999'
select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as datetime) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '1e29'
select column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as datetime) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '2011-02-32 8:46:06.23434'
select column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as datetime) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '2011-13-01 8:46:06.23434'
select column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as datetime) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '2011-02-30 8:46:06.23434'
select column_get(column_create(1, "20010231"), 1 as datetime) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '20010231'
select column_get(column_create(1, "0" AS CHAR), 1 as datetime) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '0'
#
# column get date
#
select column_get(column_create(1, 20010203101112.121314 as double), 1 as date) as ex;
ex
2001-02-03
select column_get(column_create(1, 20010203101112.121314 as decimal), 1 as date) as ex;
ex
2001-02-03
select column_get(column_create(1, 20010203101112 as unsigned int), 1 as date) as ex;
ex
2001-02-03
select column_get(column_create(1, 20010203101112 as int), 1 as date) as ex;
ex
2001-02-03
select column_get(column_create(1, "20010203101112" as char), 1 as date) as ex;
ex
2001-02-03
select column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as date) as ex;
ex
2001-02-03
select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as date) as ex;
ex
2001-02-03
select column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as date) as ex;
ex
2001-02-03
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as date) as ex;
ex
2011-04-05
select column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as date) as ex;
ex
2011-00-00
select column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as date) as ex;
ex
2011-00-01
select column_get(column_create(1, 20010203 as unsigned int), 1 as date) as ex;
ex
2001-02-03
select column_get(column_create(1, 20010203 as int), 1 as date) as ex;
ex
2001-02-03
select column_get(column_create(1, 20010203), 1 as date) as ex;
ex
2001-02-03
select column_get(column_create(1, 20010203.0), 1 as date) as ex;
ex
2001-02-03
select column_get(column_create(1, 20010203.0 as double), 1 as date) as ex;
ex
2001-02-03
select column_get(column_create(1, "2001-02-03"), 1 as date) as ex;
ex
2001-02-03
select column_get(column_create(1, "20010203"), 1 as date) as ex;
ex
2001-02-03
select column_get(column_create(1, 0), 1 as date) as ex;
ex
0000-00-00
select column_get(column_create(1, "2001021"), 1 as date) as ex;
ex
2020-01-02
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;
ex
2011-02-30
select column_get(column_create(1, "0000-00-000" AS CHAR), 1 as date) as ex;
ex
0000-00-00
select column_get(column_create(1, "2001-00-02" AS CHAR), 1 as date) as ex;
ex
2001-00-02
set @@sql_mode="";
# column get date truncation & warnings
select column_get(column_create(1, "1223.5aa" AS char), 1 as date) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '1223.5aa'
select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as date) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '1.8446744073709552e19'
select column_get(column_create(1, 9223372036854775807 AS int), 1 as date) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '9223372036854775807'
select column_get(column_create(1, -9223372036854775808 AS int), 1 as date) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '-9223372036854775808'
select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as date) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '99999999999999999999999999999'
select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as date) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '1e29'
select column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as date) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '2011-02-32 8:46:06.23434'
select column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as date) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '2011-13-01 8:46:06.23434'
select column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as date) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '2011-02-30 8:46:06.23434'
select column_get(column_create(1, "20010231"), 1 as date) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '20010231'
select column_get(column_create(1, "0" AS CHAR), 1 as date) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect datetime value: '0'
#
# column get time
#
select column_get(column_create(1, 20010203101112.121314 as double), 1 as time) as ex;
ex
10:11:12
select column_get(column_create(1, 20010203101112.121314 as decimal), 1 as time) as ex;
ex
10:11:12
select column_get(column_create(1, 20010203101112 as unsigned int), 1 as time) as ex;
ex
10:11:12
select column_get(column_create(1, 8080102 as unsigned int), 1 as time) as ex;
ex
808:01:02
select column_get(column_create(1, 20010203101112 as int), 1 as time) as ex;
ex
10:11:12
select column_get(column_create(1, -8080102 as int), 1 as time) as ex;
ex
-808:01:02
select column_get(column_create(1, "20010203101112" as char), 1 as time) as ex;
ex
10:11:12
select column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as time) as ex;
ex
10:11:12
select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as time) as ex;
ex
10:11:12
select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as time(6)) as ex;
ex
10:11:12.121314
select column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as time) as ex;
ex
10:11:12
select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as time(6)) as ex;
ex
08:46:06.234340
select column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as time(6)) as ex;
ex
08:46:06.234340
select column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as time(6)) as ex;
ex
08:46:06.234340
select column_get(column_create(1, "830:46:06.23434" AS CHAR), 1 as time(6)) as ex;
ex
830:46:06.234340
select column_get(column_create(1, "830:46:06" AS CHAR), 1 as time(6)) as ex;
ex
830:46:06.000000
select cast("-830:46:06.23434" AS time(6)) as ex;
ex
-830:46:06.234340
select 1,cast("-830:46:06.23434" AS time(6)) as ex;
1	ex
1	-830:46:06.234340
select hex(column_create(1, "-830:46:06.23434" AS CHAR)) as ex;
ex
000100010003082D3833303A34363A30362E3233343334
select column_get(column_create(1, "-830:46:06.23434" AS CHAR), 1 as time(6)) as ex;
ex
-830:46:06.234340
select column_get(column_create(1, "0" AS CHAR), 1 as time) as ex;
ex
00:00:00
select column_get(column_create(1, "6" AS CHAR), 1 as time) as ex;
ex
00:00:06
select column_get(column_create(1, "1:6" AS CHAR), 1 as time) as ex;
ex
01:06:00
select column_get(column_create(1, "2:1:6" AS CHAR), 1 as time) as ex;
ex
02:01:06
select column_get(column_create(1, 0), 1 as time) as ex;
ex
00:00:00
select column_get(column_create(1, "2001021"), 1 as time) as ex;
ex
200:10:21
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;
ex
18:46:06
set @@sql_mode="";
# column get date truncation & warnings
select column_get(column_create(1, "1223.5aa" AS char), 1 as time) as ex;
ex
00:12:23
Warnings:
Warning	1292	Truncated incorrect time value: '1223.5aa'
select column_get(column_create(1, "1223.5aa" AS char), 1 as time(3)) as ex;
ex
00:12:23.500
Warnings:
Warning	1292	Truncated incorrect time value: '1223.5aa'
select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as time) as ex;
ex
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '1.8446744073709552e19'
select column_get(column_create(1, 9223372036854775807 AS int), 1 as time) as ex;
ex
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '9223372036854775807'
select column_get(column_create(1, -9223372036854775808 AS int), 1 as time) as ex;
ex
-838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '-9223372036854775808'
select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as time) as ex;
ex
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '99999999999999999999999999999'
select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as time) as ex;
ex
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '1e29'
select column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as time) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect time value: '2011-02-32 8:46:06.23434'
select column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as time) as ex;
ex
NULL
Warnings:
Warning	1292	Incorrect time value: '2011-13-01 8:46:06.23434'
select column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as time) as ex;
ex
08:46:06
select column_get(column_create(1, "2001-02-03"), 1 as time) as ex;
ex
00:20:01
Warnings:
Warning	1292	Truncated incorrect time value: '2001-02-03'
select column_get(column_create(1, "20010203"), 1 as time) as ex;
ex
838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '20010203'
# column add
select hex(column_add(column_create(1, 1212 as integer), 2, 1212 as integer)) as ex;
ex
00020001000002001078097809
select hex(column_add(column_create(1, 1212 as integer), 1, 1212 as integer)) as ex;
ex
0001000100007809
select hex(column_add(column_create(1, 1212 as integer), 1, NULL as integer)) as ex;
ex
000000
select hex(column_add(column_create(1, 1212 as integer), 2, NULL as integer)) as ex;
ex
0001000100007809
select hex(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer)) as ex;
ex
000200010000020008167809
select column_get(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer), 1 as integer) as ex;
ex
11
select column_get(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer), 2 as integer) as ex;
ex
1212
select hex(column_add(column_create(1, 1212 as integer), 1, 1212 as integer, 2, 11 as integer)) as ex;
ex
000200010000020010780916
select hex(column_add(column_create(1, NULL as integer), 1, 1212 as integer, 2, 11 as integer)) as ex;
ex
000200010000020010780916
select hex(column_add(column_create(1, 1212 as integer, 2, 1212 as integer), 1, 11 as integer)) as ex;
ex
000200010000020008167809
select hex(column_add(column_create(1, 1), 1, null)) as ex;
ex
000000
select column_list(column_add(column_create(1, 1), 1, null)) as ex;
ex

select column_list(column_add(column_create(1, 1), 1, "")) as ex;
ex
`1`
select hex(column_add("", 1, 1)) as ex;
ex
00010001000002
# column delete
select hex(column_delete(column_create(1, 1212 as integer, 2, 1212 as integer), 1)) as ex;
ex
0001000200007809
select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2)) as ex;
ex
0002000100000300080206
select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 3)) as ex;
ex
0002000100000200080204
select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 4)) as ex;
ex
000300010000020008030010020406
select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2, 1)) as ex;
ex
00010003000006
select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2, 3)) as ex;
ex
00010001000002
select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 1, 2, 3)) as ex;
ex
000000
select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 1, 2, 3, 10)) as ex;
ex
000000
select hex(column_delete(column_create(1, 1), 1)) as ex;
ex
000000
select hex(column_delete("", 1)) as ex;
ex

# column exists
select column_exists(column_create(1, 1212 as integer, 2, 1212 as integer), 1) as ex;
ex
1
select column_exists(column_create(1, 1212 as integer, 2, 1212 as integer), 4) as ex;
ex
0
# column list
select column_list(column_create(1, 1212 as integer, 2, 1212 as integer)) as ex;
ex
`1`,`2`
select column_list(column_create(1, 1212 as integer)) as ex;
ex
`1`
select column_list(column_create(1, NULL as integer)) as ex;
ex

#
# check error handling
#
select HEX(COLUMN_CREATE(1, 5, 1, 5));
ERROR 22007: Illegal value used as argument of dynamic column function
select HEX(COLUMN_CREATE("", 1, 5, 1, 5));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1
select COLUMN_LIST("a");
ERROR HY000: Encountered illegal format of dynamic column string
select column_delete("a", 1);
ERROR HY000: Encountered illegal format of dynamic column string
select hex(column_delete("", 1));
hex(column_delete("", 1))

select hex(column_delete("", -1));
ERROR 22007: Illegal value used as argument of dynamic column function
select hex(column_create(-1, 1));
ERROR 22007: Illegal value used as argument of dynamic column function
select hex(column_create(65536, 1));
ERROR 22007: Illegal value used as argument of dynamic column function
select hex(column_add("", -1, 1));
ERROR 22007: Illegal value used as argument of dynamic column function
select hex(column_add("", 65536, 1));
ERROR 22007: Illegal value used as argument of dynamic column function
select hex(column_get("", -1 as int));
hex(column_get("", -1 as int))
NULL
#
# Test with table
#
create table t1 (id int primary key, str mediumblob);
insert into t1 values (1, ''), (2, ''), (3, ''), (4, ''), (5, null);
select id, str, column_get(str, 1 as int) from t1;
id	str	column_get(str, 1 as int)
1		NULL
2		NULL
3		NULL
4		NULL
5	NULL	NULL
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;
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);
id	column_get(str, 1 as int)	column_get(str, 2 as char)
1	1	a
2	2	a
4	4	b
update t1 set str=column_create(1, id, 10, "test") where id = 5;
insert into t1 values (6, column_create(10, "test2"));
update t1 set str=column_add(str, 2, 'c', 1, column_get(str, 1 as int) + 1, 3, 100) where id > 2;
select id, length(str), column_get(str, 1 as int),  column_get(str, 2 as char), column_get(str, 3 as int) from t1;
id	length(str)	column_get(str, 1 as int)	column_get(str, 2 as char)	column_get(str, 3 as int)
1	12	1	a	NULL
2	12	2	a	NULL
3	12	NULL	c	100
4	16	5	c	100
5	24	6	c	100
6	21	NULL	c	100
select column_get(str, 2 as char), sum(column_get(str, 1 as int)) from t1 group by column_get(str, 2 as char);
column_get(str, 2 as char)	sum(column_get(str, 1 as int))
a	3
c	11
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;
column_get(str, 2 as char)	sum(column_get(str, 1 as int))
a	3
c	11
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;
sum(column_get(str, 1 as int))
11
3
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;
sum(column_get(str, 1 as int))
3
11
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);
sum(column_get(str, 1 as int))
11
select id, column_list(str) from t1 where id= 5;
id	column_list(str)
5	`1`,`2`,`3`,`10`
update t1 set str=column_delete(str, 3, 4, 2) 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;
id	length(str)	column_list(str)	column_get(str, 1 as int)	column_get(str, 2 as char)	column_get(str, 3 as int)
1	12	`1`,`2`	1	a	NULL
2	12	`1`,`2`	2	a	NULL
3	12	`2`,`3`	NULL	c	100
4	16	`1`,`2`,`3`	5	c	100
5	15	`1`,`10`	6	NULL	NULL
6	21	`2`,`3`,`10`	NULL	c	100
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;
id	length(str)	column_list(str)	column_get(str, 1 as int)	column_get(str, 2 as char)	column_get(str, 3 as int)
5	26	`1`,`2`,`4`,`10`	6	c	NULL
select id, length(str), column_list(str), column_exists(str, 4) from t1;
id	length(str)	column_list(str)	column_exists(str, 4)
1	12	`1`,`2`	0
2	12	`1`,`2`	0
3	12	`2`,`3`	0
4	16	`1`,`2`,`3`	0
5	26	`1`,`2`,`4`,`10`	1
6	21	`2`,`3`,`10`	0
select sum(column_get(str, 1 as int)), column_list(str) from t1 group by 2;
sum(column_get(str, 1 as int))	column_list(str)
3	`1`,`2`
5	`1`,`2`,`3`
6	`1`,`2`,`4`,`10`
NULL	`2`,`3`
NULL	`2`,`3`,`10`
select id, hex(str) from t1;
id	hex(str)
1	00020001000002000B020861
2	00020001000002000B040861
3	0002000200030300100863C8
4	00030001000002000B0300180A0863C8
5	00040001000002000B04001B0A00330C08630834350874657374
6	0003000200030300100A001B0863C8087465737432
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);
id
5
select id from t1 where column_get(str,4 as char(100)) = repeat("a", 100);
id
5
Warnings:
Warning	1292	Truncated incorrect CHAR(100) value: 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...'
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);
id
5
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);
id
5
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);
id
5
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);
id
5
select id, column_list(str), length(str) from t1 where id=5;
id	column_list(str)	length(str)
5	`1`,`2`,`4`,`5`,`10`	100048
update t1 set str=column_delete(str, 5) where id=5;
select id, column_list(str), length(str) from t1 where id=5;
id	column_list(str)	length(str)
5	`1`,`2`,`4`,`10`	34
drop table t1;
#
# LP#778905: Assertion `value->year <= 9999' failed in
# dynamic_column_date_store
#
SELECT COLUMN_GET( 'a' , 2 AS DATE );
ERROR HY000: Encountered illegal format of dynamic column string
SELECT COLUMN_CREATE( 1 , COLUMN_GET( 'a' , 2 AS DATE ) );
ERROR HY000: Encountered illegal format of dynamic column string
#
# LP#778912: Assertion `field_pos < field_count' failed in
# Protocol_text::store in maria-5.3-mwl34
#
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;
HEX(COLUMN_ADD(f1, 1, 'abc'))	COLUMN_LIST(f1)
NULL	NULL
0002000100030200230861626308636465	`2`
SELECT COLUMN_ADD(f1, 1, 'abc'), COLUMN_LIST(f1) FROM t1;
DROP TABLE t1;
#
# Some dynamic strings that caused crashes in the past
#
set @a=0x0102000200030004000F0D086B74697A6A7176746F6B687563726A746E7A746A666163726C6F7A6B62636B6B756B666779666977617369796F67756C726D62677A72756E63626D78636D7077706A6F736C6D636464696770786B6371637A6A6A6463737A6A676879716462637178646C666E6B6C726A637677696E7271746C616D646368687A6C707869786D666F666261797470616A63797673737A796D74747475666B717573687A79696E7276706F796A6E767361796A6F6D646F6378677A667074746363736A796D67746C786F697873686464616265616A7A6F7168707A6B776B6376737A6B72666C6F666C69636163686F6B666D627166786A71616F;
select column_add(@a, 3, "a");
ERROR HY000: Encountered illegal format of dynamic column string
#
# LP#781233 mysqld: decimal.c:1459: decimal_bin_size: 
# Assertion `scale >= 0 && precision > 0 && scale <= precision' ...
#
set @a=0x00020008000009000C2C010080;
select COLUMN_GET(@a, 9 AS DECIMAL);
COLUMN_GET(@a, 9 AS DECIMAL)
0
select hex(COLUMN_CREATE(0, COLUMN_GET(@a, 9 AS DECIMAL))) as ex;
ex
000100000004
select hex(COLUMN_CREATE(0, COLUMN_GET(@a, 9 AS DECIMAL(19,0)))) as ex;
ex
000100000004
select hex(COLUMN_CREATE(0, COLUMN_GET(COLUMN_CREATE(0, 0.0 as decimal), 0 as decimal))) as ex;
ex
000100000004
select hex(COLUMN_CREATE(0, 0.0 as decimal)) as ex;
ex
000100000004
#
# MDEV-4292: parse error when selecting on views using dynamic column
#
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;
i	a
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i`,column_get(`t1`.`d`,1 as char charset binary) AS `a` from `t1`	latin1	latin1_swedish_ci
drop view v1;
create view v1 as select i, column_get(d, 1 as int) as a from t1;
select * from v1;
i	a
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i`,column_get(`t1`.`d`,1 as signed) AS `a` from `t1`	latin1	latin1_swedish_ci
drop view v1;
create view v1 as select i, column_get(d, 1 as unsigned int) as a from t1;
select * from v1;
i	a
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i`,column_get(`t1`.`d`,1 as unsigned) AS `a` from `t1`	latin1	latin1_swedish_ci
drop view v1;
create view v1 as select i, column_get(d, 1 as date) as a from t1;
select * from v1;
i	a
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i`,column_get(`t1`.`d`,1 as date) AS `a` from `t1`	latin1	latin1_swedish_ci
drop view v1;
create view v1 as select i, column_get(d, 1 as time) as a from t1;
select * from v1;
i	a
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i`,column_get(`t1`.`d`,1 as time) AS `a` from `t1`	latin1	latin1_swedish_ci
drop view v1;
create view v1 as select i, column_get(d, 1 as datetime) as a from t1;
select * from v1;
i	a
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i`,column_get(`t1`.`d`,1 as datetime) AS `a` from `t1`	latin1	latin1_swedish_ci
drop view v1;
create view v1 as select i, column_get(d, 1 as decimal) as a from t1;
select * from v1;
i	a
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i`,column_get(`t1`.`d`,1 as decimal(10,0)) AS `a` from `t1`	latin1	latin1_swedish_ci
drop view v1;
create view v1 as select i, column_get(d, 1 as double) as a from t1;
select * from v1;
i	a
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i`,column_get(`t1`.`d`,1 as double) AS `a` from `t1`	latin1	latin1_swedish_ci
drop view v1;
create view v1 as select i, column_get(d, 1 as char) as a from t1;
select * from v1;
i	a
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i`,column_get(`t1`.`d`,1 as char charset latin1) AS `a` from `t1`	latin1	latin1_swedish_ci
drop view v1;
drop table t1;
#
# MDEV-4811: Assertion `offset < 0x1f' fails in type_and_offset_store 
# on COLUMN_ADD
#
CREATE TABLE t1 (dyn TINYBLOB) ENGINE=MyISAM;
INSERT INTO t1 SET dyn = COLUMN_CREATE( 40, REPEAT('a', 233), 4, REPEAT('b', 322) );
Warnings:
Warning	1265	Data truncated for column 'dyn' at row 1
SELECT COLUMN_ADD( dyn, 6, REPEAT('x',80), 4, REPEAT('y',215) AS INTEGER ) FROM t1;
ERROR HY000: Encountered illegal format of dynamic column string
delete from t1;
#above test with 10.0 format
INSERT INTO t1 SET dyn = COLUMN_CREATE( 'a', REPEAT('a', 250), 'b', REPEAT('b', 322) );
Warnings:
Warning	1265	Data truncated for column 'dyn' at row 1
SELECT COLUMN_ADD( dyn, 'c', REPEAT('x',80), 'b', REPEAT('y',215) AS INTEGER ) FROM t1;
ERROR HY000: Encountered illegal format of dynamic column string
DROP table t1;
#
# MDEV-4812: Valgrind warnings (Invalid write) in
# dynamic_column_update_many on COLUMN_ADD
#
CREATE TABLE t1 (dyncol TINYBLOB) ENGINE=MyISAM;
INSERT INTO t1 SET dyncol = COLUMN_CREATE( 7, REPEAT('k',487), 209, REPEAT('x',464) );
Warnings:
Warning	1265	Data truncated for column 'dyncol' at row 1
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) );
Warnings:
Warning	1265	Data truncated for column 'dyncol' at row 1
SELECT COLUMN_ADD( dyncol, 'a', '22:22:22', 'c', REPEAT('x',270) AS CHAR ) as ex FROM t1;
DROP table t1;
#
# MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column
#
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;
t1	t2
-838:59:59	-838:59:59
Warnings:
Warning	1292	Truncated incorrect time value: '-999999999999999'
Warning	1292	Truncated incorrect time value: '-9223372036854775808'
#
# end of 5.3 tests
#
select column_get(column_create(1, "18446744073709552001" as char), 1 as int) as ex;
ex
-1
Warnings:
Warning	1918	Encountered illegal value '18446744073709552001' when converting to INT
Note	1105	Cast to signed converted positive out-of-range integer to it's negative complement
#
# MDEV-7505 - Too large scale in DECIMAL dynamic column getter crashes
#             mysqld
#
SELECT COLUMN_GET(`x`, 'y' AS DECIMAL(5,50));
ERROR 42000: Too big scale specified for ''y''. Maximum is 38
#
# test of symbolic names
#
# creation test (names)
set names utf8;
select hex(column_create("адын", 1212));
hex(column_create("адын", 1212))
040100080000000000D0B0D0B4D18BD0BD7809
select hex(column_create("1212", 1212));
hex(column_create("1212", 1212))
040100040000000000313231327809
select hex(column_create(1212, 2, "www", 3));
hex(column_create(1212, 2, "www", 3))
04020007000000000003001000777777313231320604
select hex(column_create("1212", 2, "www", 3));
hex(column_create("1212", 2, "www", 3))
04020007000000000003001000777777313231320604
select hex(column_create("1212", 2, 3, 3));
hex(column_create("1212", 2, 3, 3))
0402000500000000000100100033313231320604
select hex(column_create("1212", 2, "адын", 1, 3, 3));
hex(column_create("1212", 2, "адын", 1, 3, 3))
0403000D000000000001001000050020003331323132D0B0D0B4D18BD0BD060402
set names latin1;
# fetching column test (names)
set names utf8;
select column_get(column_create("адын", 1212), "адын" as int) as ex;
ex
1212
select column_get(column_create("1212", 2, "адын", 1, 3, 3), "адын" as int) as ex;
ex
1
select column_get(column_create("1212", 2, "адын", 1, 3, 3), 1212 as int) as ex;
ex
2
select column_get(column_create("1212", 2, "адын", 1, 3, 3), "3" as int) as ex;
ex
3
select column_get(column_create("1212", 2, "адын", 1, 3, 3), 3 as int) as ex;
ex
3
select column_get(column_create("1212", 2, "адын", 1, 3, 3), 4 as int) as ex;
ex
NULL
select column_get(column_create("1212", 2, "адын", 1, 3, 3), "4" as int) as ex;
ex
NULL
set names latin1;
# column existence test (names)
set names utf8;
select column_exists(column_create("адын", 1212), "адын") as ex;
ex
1
select column_exists(column_create("адын", 1212), "aады") as ex;
ex
0
select column_exists(column_create("1212", 2, "адын", 1, 3, 3), "адын") as ex;
ex
1
select column_exists(column_create("1212", 2, "адын", 1, 3, 3), 1212) as ex;
ex
1
select column_exists(column_create("1212", 2, "адын", 1, 3, 3), "3") as ex;
ex
1
select column_exists(column_create("1212", 2, "адын", 1, 3, 3), 3) as ex;
ex
1
select column_exists(column_create("1212", 2, "адын", 1, 3, 3), 4) as ex;
ex
0
select column_exists(column_create("1212", 2, "адын", 1, 3, 3), "4") as ex;
ex
0
set names latin1;
# column changing test (names)
select hex(column_add(column_create(1, "AAA"), "b", "BBB")) as ex;
ex
0402000200000003000100430031620841414108424242
select hex(column_add(column_create("1", "AAA"), "b", "BBB")) as ex;
ex
0402000200000003000100430031620841414108424242
select column_get(column_add(column_create(1, "AAA"), "b", "BBB"), 1 as char) as ex;
ex
AAA
select column_get(column_add(column_create(1, "AAA"), "b", "BBB"), "b" as char) as ex;
ex
BBB
select hex(column_add(column_create("a", "AAA"), 1, "BBB")) as ex;
ex
0402000200000003000100430031610842424208414141
select hex(column_add(column_create("a", "AAA"), "1", "BBB")) as ex;
ex
0402000200000003000100430031610842424208414141
select hex(column_add(column_create("a", 1212 as integer), "b", "1212" as integer)) as ex;
ex
04020002000000000001002000616278097809
select hex(column_add(column_create("a", 1212 as integer), "a", "1212" as integer)) as ex;
ex
040100010000000000617809
select hex(column_add(column_create("a", 1212 as integer), "a", NULL as integer)) as ex;
ex
0400000000
select hex(column_add(column_create("a", 1212 as integer), "b", NULL as integer)) as ex;
ex
040100010000000000617809
select hex(column_add(column_create("a", 1212 as integer), "b", 1212 as integer, "a", 11 as integer)) as ex;
ex
040200020000000000010010006162167809
select column_get(column_add(column_create("a", 1212 as integer), "b", 1212 as integer, "a", 11 as integer), "a" as integer) as ex;
ex
11
select column_get(column_add(column_create("a", 1212 as integer), "b", 1212 as integer, "a", 11 as integer), "b" as integer) as ex;
ex
1212
select hex(column_add(column_create("a", 1212 as integer), "a", 1212 as integer, "b", 11 as integer)) as ex;
ex
040200020000000000010020006162780916
select hex(column_add(column_create("a", NULL as integer), "a", 1212 as integer, "b", 11 as integer)) as ex;
ex
040200020000000000010020006162780916
select hex(column_add(column_create("a", 1212 as integer, "b", 1212 as integer), "a", 11 as integer)) as ex;
ex
040200020000000000010010006162167809
select hex(column_add(column_create("a", 1), "a", null)) as ex;
ex
0400000000
select column_list(column_add(column_create("a", 1), "a", null)) as ex;
ex

select column_list(column_add(column_create("a", 1), "a", "")) as ex;
ex
`a`
select hex(column_add("", "a", 1)) as ex;
ex
0401000100000000006102
# column delete (names)
select hex(column_delete(column_create("a", 1212 as integer, "b", 1212 as integer), "a")) as ex;
ex
040100010000000000627809
select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "b")) as ex;
ex
0402000200000000000100100061630206
select hex(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer)) as ex;
ex
0403000300000000000100100002002000616263020406
select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "c")) as ex;
ex
0402000200000000000100100061620204
select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "d")) as ex;
ex
0403000300000000000100100002002000616263020406
select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "b", "a")) as ex;
ex
0401000100000000006306
select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "b", "c")) as ex;
ex
0401000100000000006102
select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "a", "b", "c")) as ex;
ex
0400000000
select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "a", "b", "c", "e")) as ex;
ex
0400000000
select hex(column_delete(column_create("a", 1), "a")) as ex;
ex
0400000000
select hex(column_delete("", "a")) as ex;
ex

#
# MDEV-458 DNAMES: Server crashes on using an unquoted string
# as a dynamic column name
#
select COLUMN_CREATE(color, "black");
ERROR 42S22: Unknown column 'color' in 'field list'
#
# MDEV-489 Assertion `offset < 0x1f' failed in
# type_and_offset_store on COLUMN_ADD
#
CREATE TABLE t1 (f1 tinyblob);
INSERT INTO t1 VALUES (COLUMN_CREATE('col1', REPEAT('a',30)));
select column_check(f1) from t1;
column_check(f1)
1
UPDATE t1 SET f1 = COLUMN_ADD( f1, REPEAT('b',211), 'val2' );
Warnings:
Warning	1265	Data truncated for column 'f1' at row 1
select column_check(f1) from t1;
column_check(f1)
0
UPDATE t1 SET f1 = COLUMN_ADD( f1, REPEAT('c',211), 'val3' );
Warnings:
Warning	1265	Data truncated for column 'f1' at row 1
select column_check(f1) from t1;
column_check(f1)
0
drop table t1;
#
# MDEV-490/MDEV-491 null as arguments
#
SELECT COLUMN_GET( COLUMN_CREATE( 'col', 'val' ), NULL AS CHAR ) as ex;
ex
NULL
SELECT COLUMN_GET( NULL, 'col' as char ) as ex;
ex
NULL
SELECT COLUMN_EXISTS( COLUMN_CREATE( 'col', 'val' ), NULL) as ex;
ex
NULL
SELECT COLUMN_EXISTS( NULL, 'col') as ex;
ex
NULL
SELECT COLUMN_CREATE( NULL, 'val' ) as ex;
ex
NULL
SELECT COLUMN_ADD( NULL, 'val', 'col') as ex;
ex
NULL
#
# MDEV-488: Assertion `column_name->length < 255' failed on a
# column name with length 255 (precisely)
#
SELECT hex(COLUMN_CREATE(REPEAT('a',255),1));
hex(COLUMN_CREATE(REPEAT('a',255),1))
040100FF000000000061616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616102
SELECT hex(COLUMN_CREATE(REPEAT('a',65536),1));
ERROR 22007: Illegal value used as argument of dynamic column function
#
# JSON conversion
#
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;
ex
{"int":-1212,"date":"2011-04-05","time":"00:45:49.000001","uint":12334,"double":1.23444e50,"string":"gdgd\\dhdjh\"dhdhd","decimal":23.344,"datetime":"2011-04-05 00:45:49.000001"}
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;
ex
{"1":-1212,"2":12334,"3":23.344,"4":1.23444e50,"5":"gdgd\\dhdjh\"dhdhd","6":"00:45:49.000001","7":"2011-04-05 00:45:49.000001","8":"2011-04-05"}
#
# CHECK test
#
SELECT COLUMN_CHECK(COLUMN_CREATE(1,'a'));
COLUMN_CHECK(COLUMN_CREATE(1,'a'))
1
SELECT COLUMN_CHECK('abracadabra');
COLUMN_CHECK('abracadabra')
0
SELECT COLUMN_CHECK('');
COLUMN_CHECK('')
1
SELECT COLUMN_CHECK(NULL);
COLUMN_CHECK(NULL)
NULL
#
# escaping check
#
select column_json(column_create("string", "'\"/\\`.,whatever")),hex(column_create("string", "'\"/\\`.,whatever"));
column_json(column_create("string", "'\"/\\`.,whatever"))	hex(column_create("string", "'\"/\\`.,whatever"))
{"string":"'\"/\\`.,whatever"}	040100060000000300737472696E670827222F5C602E2C7768617465766572
#
# embedding test
#
select column_json(column_create("val", "val", "emb", column_create("val2", "val2"))) as ex;
ex
{"emb":{"val2":"val2"},"val":"val"}
select column_json(column_create(1, "val", 2, column_create(3, "val2"))) as ex;
ex
{"1":"val","2":{"3":"val2"}}
#
# Time encoding
#
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;
hex	js
04010001000000070074649363B82003	{"t":"800:46:06.234340"}
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;
hex	js
000100010007649363B82003	{"1":"800:46:06.234340"}
select hex(column_create("t", "800:46:06" AS time)) as hex,
column_json(column_create("t", "800:46:06" AS time)) as js;
hex	js
04010001000000070074860B32	{"t":"800:46:06"}
select hex(column_create(1, "800:46:06" AS time)) as hex,
column_json(column_create(1, "800:46:06" AS time)) as js;
hex	js
000100010007000060B82003	{"1":"800:46:06"}
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;
hex	js
0401000100000005007495B90F649363B80A00	{"t":"2012-12-21 10:46:06.234340"}
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;
hex	js
00010001000595B90F649363B80A00	{"1":"2012-12-21 10:46:06.234340"}
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;
hex	js
0401000100000005007495B90F86AB00	{"t":"2012-12-21 10:46:06"}
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;
hex	js
00010001000595B90F000060B80A00	{"1":"2012-12-21 10:46:06"}
#
# MDEV-4849: Out of memory error and valgrind warnings on COLUMN_ADD
#
CREATE TABLE t1 (dyncol tinyblob) ENGINE=MyISAM;
INSERT INTO t1 SET dyncol = COLUMN_CREATE( 3, REPEAT('a',330), 4, 'x' );
Warnings:
Warning	1265	Data truncated for column 'dyncol' at row 1
SELECT COLUMN_ADD( COLUMN_ADD( dyncol, 1, REPEAT('b',130) ), 3, 'y' ) FROM t1;
ERROR HY000: Encountered illegal format of dynamic column string
DROP TABLE t1;
#
#MDEV-5840: group_concat( column_json(dynamic_column )) return empty
#result
#
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;
ex
{"name1":"value1","name2":"value2"}
drop table t1;
#
# MDEV-7116: Dynamic column hangs/segfaults
#
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;
#
# MDEV-8565: COLUMN_CHECK fails on valid data
#
SELECT COLUMN_CHECK(COLUMN_CREATE('a',0,'b','1'));
COLUMN_CHECK(COLUMN_CREATE('a',0,'b','1'))
1
SELECT COLUMN_CHECK(COLUMN_CREATE('a',1,'b','1'));
COLUMN_CHECK(COLUMN_CREATE('a',1,'b','1'))
1
SELECT COLUMN_JSON(COLUMN_CREATE('a',0,'b','1'));
COLUMN_JSON(COLUMN_CREATE('a',0,'b','1'))
{"a":0,"b":"1"}
SELECT COLUMN_JSON(COLUMN_CREATE('a',1,'b','1'));
COLUMN_JSON(COLUMN_CREATE('a',1,'b','1'))
{"a":1,"b":"1"}
#
# MDEV-8401: COLUMN_CREATE(name, value as DOUBLE) results in string
#
SELECT COLUMN_JSON(
COLUMN_CREATE(
'one', 123.456,
'two', 123.456 as DOUBLE
)
) as ex;
ex
{"one":123.456,"two":123.456}
#
# MDEV-8521: Drastic loss of precision in COLUMN_JSON() on DOUBLEs
#
select column_get(column_create('float', 1.23456789012345E+100 as double), 'float' as double) as ex;
ex
1.23456789012345e100
select column_json(column_create('float', 1.23456789012345E+100 as double)) as ex;
ex
{"float":1.23456789012345e100}
select column_json(column_create('float', 1.23456789012345E+10 as double)) as ex;
ex
{"float":12345678901.2345}
#
# MDEV-9147: Character set is ignored in Dynamic Column for saved string
#
SET NAMES utf8;
SELECT COLUMN_GET(COLUMN_CREATE(1, 0xC2A2 AS CHAR CHARACTER SET latin1), 1 AS CHAR CHARACTER SET utf8) AS a;
a
¢
SELECT COLUMN_GET(COLUMN_CREATE(1, 0xC2A2 AS CHAR CHARACTER SET utf8), 1 AS CHAR CHARACTER SET utf8) AS a;
a
¢
#
# MDEV-9167: COLUMN_CHECK fails on valid decimal data
#
SELECT COLUMN_CHECK(COLUMN_CREATE('a',0 AS DECIMAL,'b',1 AS DECIMAL)) as ex;
ex
1
SELECT COLUMN_CHECK(COLUMN_CREATE('a',1 AS DECIMAL,'b',1 AS DECIMAL)) as ex;
ex
1
SELECT COLUMN_JSON(COLUMN_CREATE('a',0 AS DECIMAL,'b',1 AS DECIMAL)) as ex;
ex
{"a":0,"b":1}
SELECT COLUMN_JSON(COLUMN_CREATE('a',1 AS DECIMAL,'b',1 AS DECIMAL)) as ex;
ex
{"a":1,"b":1}
#
# MDEV-7533: COLUMN_JSON() doesn't escape control characters
# in string values
#
SELECT COLUMN_JSON(COLUMN_CREATE('test','"\\\t\n\Z')) AS json;
json
{"test":"\"\\\u0009\u000A\u001A"}
SELECT COLUMN_JSON(COLUMN_CREATE('test','First line\nSecond line')) AS json;
json
{"test":"First line\u000ASecond line"}
#
# MDEV-15230: column_json breaks cyrillic in 10.1.31
#
set names utf8;
create table t1 (b blob);
insert into t1 values (column_create('description',column_create('title','Описание')));
select column_json(b) from t1;
column_json(b)
{"description":{"title":"Описание"}}
drop table t1;
#
# end of 10.0 tests
#
#
# Start of 10.2 tests
#
create view v1 as select column_get(column_add(column_create(1 , 'blue' as char), 2, 'ttt'), 1 as char);
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select column_get(column_add(column_create(1,'blue' AS char charset utf8mb3 ),2,'ttt'),1 as char charset utf8mb3) AS `Name_exp_1`	utf8mb3	utf8mb3_general_ci
select * from v1;
Name_exp_1
blue
drop view v1;
#
# MDEV-10134 Add full support for DEFAULT
#
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;
Table	Create Table
t1	CREATE TABLE `t1` (
  `name` varchar(10) DEFAULT NULL,
  `value` varchar(10) DEFAULT NULL,
  `dyncol0` blob DEFAULT column_create(`name`,`value`),
  `value_dyncol0_name0` varchar(10) DEFAULT (column_get(`dyncol0`,'name0' as char charset utf8mb3)),
  `dyncol1` blob DEFAULT column_add(`dyncol0`,'name1','value1'),
  `value_dyncol1_name1` varchar(10) DEFAULT (column_get(`dyncol1`,'name1' as char charset utf8mb3)),
  `dyncol2` blob DEFAULT column_add(`dyncol1`,'name1',NULL AS int),
  `dyncol2_exists_name0` int(11) DEFAULT column_exists(`dyncol2`,'name0'),
  `dyncol2_exists_name1` int(11) DEFAULT column_exists(`dyncol2`,'name1'),
  `dyncol2_check` int(11) DEFAULT column_check(`dyncol2`),
  `dyncol1_list` text DEFAULT column_list(`dyncol1`),
  `dyncol1_json` text DEFAULT column_json(`dyncol1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
INSERT INTO t1 (name,value) VALUES ('name0', 'value0');
SELECT value_dyncol0_name0, value_dyncol1_name1 FROM t1;
value_dyncol0_name0	value_dyncol1_name1
value0	value1
SELECT dyncol2_check, dyncol2_exists_name0, dyncol2_exists_name1 FROM t1;
dyncol2_check	dyncol2_exists_name0	dyncol2_exists_name1
1	1	0
SELECT dyncol1_list FROM t1;
dyncol1_list
`name0`,`name1`
SELECT dyncol1_json FROM t1;
dyncol1_json
{"name0":"value0","name1":"value1"}
DROP TABLE t1;
#
# End of 10.2 tests
#
#
# MDEV-32140: Valgrind/MSAN warnings in dynamic_column_update_move_left
#
SELECT COLUMN_GET(COLUMN_ADD(COLUMN_CREATE(1,10),2,NULL,1,NULL),3 AS INTEGER) as ex;
ex
NULL
SELECT HEX(COLUMN_ADD(COLUMN_CREATE(1,10),2,NULL,1,NULL)) as ex;
ex
000000
#
# End of 10.4 tests
#