mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-10-31 10:56:12 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/dyncol.result
Oleksandr Byelkin 3d0fb15028 Merge branch '10.6' into 10.11
2024-10-29 15:24:38 +01:00

1976 lines
72 KiB
Text
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

#
# 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 collate utf8mb3_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`
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 collate utf8mb3_general_ci ),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 collate utf8mb3_general_ci ),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 'SELECT'
#
# 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 collate utf8mb3_general_ci ),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
#
#
# MDEV-33788 HEX(COLUMN_CREATE(.. AS CHAR ...)) fails with --view-protocol
#
SELECT hex(column_create(1,'a' AS CHAR CHARACTER SET utf8mb3 COLLATE utf8mb3_bin)) AS ex;
ex
0001000100035361
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;
ex
00020001000302001353612162
#
# MDEV-31566 Fix buffer overrun of column_json function
#
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;
c1 not_crashing
var {"jsn":"\u0000\u0005\u0000l\u0000'\u0000\u0002\u0000)\u0000\u0002\u0000+\u0000\u0002\u0000-\u0000\u0002\u0000/\u0000\u0002\u0000\u000C1\u0000\u000C;\u0000\u000CK\u0000\u000CQ\u0000\u000Fb\u0000f1f2f3f4f5\u0009姚远洋\u000F聚通金桥店\u000574500\u001011643/9645/11600\u000C\u0008\u0000\u0000\u0000\u0000\u0000<30><30>\u0019","subject":""}
zzz {"jsn":"\u0000\u0009\u0000<30>\u0000C\u0000\u0002\u0000E\u0000\u0002\u0000G\u0000\u0003\u0000J\u0000\u0004\u0000N\u0000\u0005\u0000S\u0000\u0005\u0000X\u0000\u0005\u0000]\u0000\u0005\u0000b\u0000\u0005\u0000\u000Cg\u0000\u000Cj\u0000\u000Cm\u0000\u000Cp\u0000\u0005,\u0000\u0005\u001B\u0000\u0005,\u0000\u000C<30>\u0000\u0007<30>\u0000f8f9f10pic2box_cbox_gbox_kbox_vf5_id\u000244\u000244\u000232Ahttp://oss.hdb88.com/0/photo/078ee7e7c6464ab68a0483733266a52a.gif\u00080.052272$O\u001E\u0000","volume":193.6}
drop table t1;
# End of 10.5 tests
Reference in a new issue View git blame Copy permalink