diff options
Diffstat (limited to 'mysql-test/main/dyncol.result')
| -rw-r--r-- | mysql-test/main/dyncol.result | 1958 |
1 files changed, 1958 insertions, 0 deletions
diff --git a/mysql-test/main/dyncol.result b/mysql-test/main/dyncol.result new file mode 100644 index 00000000000..7a5eeac67cc --- /dev/null +++ b/mysql-test/main/dyncol.result @@ -0,0 +1,1958 @@ +# +# column create +# +select hex(COLUMN_CREATE(1, NULL AS char character set utf8)); +hex(COLUMN_CREATE(1, NULL AS char character set utf8)) +000000 +select hex(COLUMN_CREATE(1, "afaf" AS char character set utf8)); +hex(COLUMN_CREATE(1, "afaf" AS char character set utf8)) +0001000100032161666166 +select hex(COLUMN_CREATE(1, 1212 AS char character set utf8)); +hex(COLUMN_CREATE(1, 1212 AS char character set utf8)) +0001000100032131323132 +select hex(COLUMN_CREATE(1, 12.12 AS char character set utf8)); +hex(COLUMN_CREATE(1, 12.12 AS char character set utf8)) +0001000100032131322E3132 +select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS char character set utf8)); +hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS char character set utf8)) +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)); +hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS unsigned int)) +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)); +hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS int)) +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)); +hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS double)) +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)); +hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS decimal)) +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)); +hex(COLUMN_CREATE(1, "2011-04-05 0:45:49.000001" AS datetime)) +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)); +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)) +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)); +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 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 `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))` +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); +column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned int) +1212 +explain extended +select column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned int); +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 `column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned int)` +explain extended +select column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned); +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 `column_get(column_create(1, 1212 AS unsigned int), 1 as unsigned)` +select column_get(column_create(1, 1212 AS decimal), 1 as unsigned int); +column_get(column_create(1, 1212 AS decimal), 1 as unsigned int) +1212 +select column_get(column_create(1, 1212 AS double), 1 as unsigned int); +column_get(column_create(1, 1212 AS double), 1 as unsigned int) +1212 +select column_get(column_create(1, 1212 AS int), 1 as unsigned int); +column_get(column_create(1, 1212 AS int), 1 as unsigned int) +1212 +select column_get(column_create(1, "1212" AS char), 1 as unsigned int); +column_get(column_create(1, "1212" AS char), 1 as unsigned int) +1212 +select column_get(column_create(1, "2011-04-05" AS date), 1 as unsigned int); +column_get(column_create(1, "2011-04-05" AS date), 1 as unsigned int) +20110405 +select column_get(column_create(1, "8:46:06.23434" AS time), 1 as unsigned int); +column_get(column_create(1, "8:46:06.23434" AS time), 1 as unsigned int) +84606 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as unsigned int); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as unsigned int) +20110405084606 +select column_get(column_create(1, NULL AS unsigned int), 1 as unsigned int); +column_get(column_create(1, NULL AS unsigned int), 1 as unsigned int) +NULL +# column geint truncation & warnings +select column_get(column_create(1, -1212 AS int), 1 as unsigned int); +column_get(column_create(1, -1212 AS int), 1 as unsigned int) +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); +column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as unsigned int) +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); +column_get(column_create(1, 999.9999999999999999 AS decimal), 1 as unsigned int) +1000 +select column_get(column_create(1, -1 AS decimal), 1 as unsigned int); +column_get(column_create(1, -1 AS decimal), 1 as unsigned int) +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); +column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as unsigned int) +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); +column_get(column_create(1, 999.9 AS double), 1 as unsigned int) +1000 +select column_get(column_create(1, -1 AS double), 1 as unsigned int); +column_get(column_create(1, -1 AS double), 1 as unsigned int) +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); +column_get(column_create(1, "1212III" AS char), 1 as unsigned int) +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); +column_get(column_create(1, 1212 AS int), 1 as int) +1212 +explain extended +select column_get(column_create(1, 1212 AS int), 1 as int); +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 `column_get(column_create(1, 1212 AS int), 1 as int)` +explain extended +select column_get(column_create(1, 1212 AS int), 1 as signed int); +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 `column_get(column_create(1, 1212 AS int), 1 as signed int)` +select column_get(column_create(1, -1212 AS int), 1 as int); +column_get(column_create(1, -1212 AS int), 1 as int) +-1212 +select column_get(column_create(1, 1212 AS decimal), 1 as int); +column_get(column_create(1, 1212 AS decimal), 1 as int) +1212 +select column_get(column_create(1, 1212 AS double), 1 as int); +column_get(column_create(1, 1212 AS double), 1 as int) +1212 +select column_get(column_create(1, 1212 AS unsigned int), 1 as int); +column_get(column_create(1, 1212 AS unsigned int), 1 as int) +1212 +select column_get(column_create(1, "1212" AS char), 1 as int); +column_get(column_create(1, "1212" AS char), 1 as int) +1212 +select column_get(column_create(1, "-1212" AS char), 1 as int); +column_get(column_create(1, "-1212" AS char), 1 as int) +-1212 +select column_get(column_create(1, "2011-04-05" AS date), 1 as int); +column_get(column_create(1, "2011-04-05" AS date), 1 as int) +20110405 +select column_get(column_create(1, "8:46:06.23434" AS time), 1 as int); +column_get(column_create(1, "8:46:06.23434" AS time), 1 as int) +84606 +select column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as int); +column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as int) +84606 +select column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as int); +column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as int) +-8084606 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(6)), 1 as int); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(6)), 1 as int) +20110405084606 +select column_get(column_create(1, NULL AS int), 1 as int); +column_get(column_create(1, NULL AS int), 1 as int) +NULL +#column gett truncation & warnings +select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as int); +column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as int) +-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); +column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as int) +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); +column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as int) +-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); +column_get(column_create(1, 999.9999999999999999 AS decimal), 1 as int) +1000 +select column_get(column_create(1, 999.9 AS double), 1 as int); +column_get(column_create(1, 999.9 AS double), 1 as int) +1000 +select column_get(column_create(1, -99999999999999999999999999999 AS double), 1 as int); +column_get(column_create(1, -99999999999999999999999999999 AS double), 1 as int) +-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); +column_get(column_create(1, "-1212III" AS char), 1 as int) +-1212 +Warnings: +Warning 1918 Encountered illegal value '-1212III' when converting to INT +select column_get(column_create(1, "1212III" AS char), 1 as int); +column_get(column_create(1, "1212III" AS char), 1 as int) +1212 +Warnings: +Warning 1918 Encountered illegal value '1212III' when converting to INT +select column_get(COLUMN_CREATE(1, ~0), 1 as signed); +column_get(COLUMN_CREATE(1, ~0), 1 as signed) +-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); +column_get(COLUMN_CREATE(1, ~0), 1 as unsigned) +18446744073709551615 +select column_get(COLUMN_CREATE(1, -1), 1 as signed); +column_get(COLUMN_CREATE(1, -1), 1 as signed) +-1 +select column_get(COLUMN_CREATE(1, -1), 1 as unsigned); +column_get(COLUMN_CREATE(1, -1), 1 as unsigned) +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); +column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset utf8) +1212 +explain extended +select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset utf8); +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 utf8 ),1 as char charset utf8) AS `column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset utf8)` +select column_get(column_create(1, 1212 AS unsigned int), 1 as char charset utf8); +column_get(column_create(1, 1212 AS unsigned int), 1 as char charset utf8) +1212 +select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as char charset utf8); +column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as char charset utf8) +18446744073709551615 +select column_get(column_create(1, 1212 AS int), 1 as char charset utf8); +column_get(column_create(1, 1212 AS int), 1 as char charset utf8) +1212 +select column_get(column_create(1, -1212 AS int), 1 as char charset utf8); +column_get(column_create(1, -1212 AS int), 1 as char charset utf8) +-1212 +select column_get(column_create(1, 9223372036854775807 AS int), 1 as char charset utf8); +column_get(column_create(1, 9223372036854775807 AS int), 1 as char charset utf8) +9223372036854775807 +select column_get(column_create(1, -9223372036854775808 AS int), 1 as char charset utf8); +column_get(column_create(1, -9223372036854775808 AS int), 1 as char charset utf8) +-9223372036854775808 +select column_get(column_create(1, 1212.12 AS decimal), 1 as char charset utf8); +column_get(column_create(1, 1212.12 AS decimal), 1 as char charset utf8) +1212.12 +select column_get(column_create(1, 1212.12 AS double), 1 as char charset utf8); +column_get(column_create(1, 1212.12 AS double), 1 as char charset utf8) +1212.12 +select column_get(column_create(1, "2011-04-05" AS date), 1 as char charset utf8); +column_get(column_create(1, "2011-04-05" AS date), 1 as char charset utf8) +2011-04-05 +select column_get(column_create(1, "8:46:06.23434" AS time), 1 as char charset utf8); +column_get(column_create(1, "8:46:06.23434" AS time), 1 as char charset utf8) +08:46:06.234340 +select column_get(column_create(1, "8:46:06.23434" AS time(0)), 1 as char charset utf8); +column_get(column_create(1, "8:46:06.23434" AS time(0)), 1 as char charset utf8) +08:46:06.234340 +select column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as char charset utf8); +column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as char charset utf8) +08:46:06.234340 +select column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as char charset utf8); +column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as char charset utf8) +-808:46:06.234340 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as char charset utf8); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as char charset utf8) +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); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(0)), 1 as char charset utf8) +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); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(6)), 1 as char charset utf8) +2011-04-05 08:46:06.234340 +select column_get(column_create(1, NULL AS char charset utf8), 1 as char charset utf8); +column_get(column_create(1, NULL AS char charset utf8), 1 as char charset utf8) +NULL +select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset binary); +column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset binary) +1212 +explain extended +select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset binary); +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 utf8 ),1 as char charset binary) AS `column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset binary)` +# +# column get real +# +select column_get(column_create(1, 1212.12 AS double), 1 as double); +column_get(column_create(1, 1212.12 AS double), 1 as double) +1212.12 +explain extended +select column_get(column_create(1, 1212.12 AS double), 1 as double); +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 `column_get(column_create(1, 1212.12 AS double), 1 as double)` +explain extended +select column_get(column_create(1, 1212.12 AS double), 1 as double(6,2)); +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 `column_get(column_create(1, 1212.12 AS double), 1 as double(6,2))` +select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as double); +column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as double) +1.8446744073709552e19 +select column_get(column_create(1, 9223372036854775807 AS int), 1 as double); +column_get(column_create(1, 9223372036854775807 AS int), 1 as double) +9.223372036854776e18 +select column_get(column_create(1, -9223372036854775808 AS int), 1 as double); +column_get(column_create(1, -9223372036854775808 AS int), 1 as double) +-9.223372036854776e18 +select column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as double); +column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as double) +1e29 +select column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as double); +column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as double) +-1e29 +select column_get(column_create(1, "2011-04-05" AS date), 1 as double); +column_get(column_create(1, "2011-04-05" AS date), 1 as double) +20110405 +select column_get(column_create(1, "8:46:06.23434" AS time), 1 as double); +column_get(column_create(1, "8:46:06.23434" AS time), 1 as double) +84606.23434 +select column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as double); +column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as double) +84606.23434 +select column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as double); +column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as double) +-8084606.23434 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as double); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as double) +20110405084606.234 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(6)), 1 as double); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime(6)), 1 as double) +20110405084606.234 +select round(column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as double(20,6)),3); +round(column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as double(20,6)),3) +20110405084606.234 +select column_get(column_create(1, NULL AS double), 1 as double); +column_get(column_create(1, NULL AS double), 1 as double) +NULL +# column get real truncation & warnings +select column_get(column_create(1, "1223.5aa" AS char), 1 as double); +column_get(column_create(1, "1223.5aa" AS char), 1 as double) +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); +column_get(column_create(1, "aa" AS char), 1 as double) +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)); +column_get(column_create(1, "1223.5555" AS double), 1 as double(5,2)) +999.99 +Warnings: +Warning 1264 Out of range value for column 'column_get(column_create(1, "1223.5555" AS double), 1 as double(5,2))' at row 1 +select column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2)); +column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2)) +9.99 +Warnings: +Warning 1264 Out of range value for column 'column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2))' at row 1 +# +# column get decimal +# +select column_get(column_create(1, 1212.12 AS double), 1 as decimal); +column_get(column_create(1, 1212.12 AS double), 1 as decimal) +1212 +select column_get(column_create(1, 1212.12 AS double), 1 as decimal(6,2)); +column_get(column_create(1, 1212.12 AS double), 1 as decimal(6,2)) +1212.12 +explain extended +select column_get(column_create(1, 1212.12 AS double), 1 as decimal); +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 `column_get(column_create(1, 1212.12 AS double), 1 as decimal)` +explain extended +select column_get(column_create(1, 1212.12 AS double), 1 as decimal(6,2)); +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 `column_get(column_create(1, 1212.12 AS double), 1 as decimal(6,2))` +select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as decimal(20,0)); +column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as decimal(20,0)) +18446744073709551615 +select column_get(column_create(1, 9223372036854775807 AS int), 1 as decimal(32,0)); +column_get(column_create(1, 9223372036854775807 AS int), 1 as decimal(32,0)) +9223372036854775807 +select column_get(column_create(1, -9223372036854775808 AS int), 1 as decimal(32,0)); +column_get(column_create(1, -9223372036854775808 AS int), 1 as decimal(32,0)) +-9223372036854775808 +select column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as decimal(40,10)); +column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as decimal(40,10)) +-99999999999999999999999999999.0000000000 +select column_get(column_create(1, "2011-04-05" AS date), 1 as decimal(32,6)); +column_get(column_create(1, "2011-04-05" AS date), 1 as decimal(32,6)) +20110405.000000 +select column_get(column_create(1, "8:46:06.23434" AS time), 1 as decimal(32,6)); +column_get(column_create(1, "8:46:06.23434" AS time), 1 as decimal(32,6)) +84606.234340 +select column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as decimal(32,6)); +column_get(column_create(1, "8:46:06.23434" AS time(6)), 1 as decimal(32,6)) +84606.234340 +select column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as decimal(32,6)); +column_get(column_create(1, "-808:46:06.23434" AS time(6)), 1 as decimal(32,6)) +-8084606.234340 +select column_get(column_create(1, "2011-04-05 8:46:06.123456" AS datetime), 1 as decimal(32,6)); +column_get(column_create(1, "2011-04-05 8:46:06.123456" AS datetime), 1 as decimal(32,6)) +20110405084606.123456 +select column_get(column_create(1, "2011-04-05 8:46:06.123456" AS datetime(6)), 1 as decimal(32,6)); +column_get(column_create(1, "2011-04-05 8:46:06.123456" AS datetime(6)), 1 as decimal(32,6)) +20110405084606.123456 +select column_get(column_create(1, "2011-04-05 8:46:06.12345678" AS datetime(6)), 1 as decimal(32,8)); +column_get(column_create(1, "2011-04-05 8:46:06.12345678" AS datetime(6)), 1 as decimal(32,8)) +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)); +column_get(column_create(1, NULL as decimal), 1 as decimal(32,10)) +NULL +select column_get(column_create(1, "1223.5555" as decimal(10,5)), 1 as decimal(6,2)); +column_get(column_create(1, "1223.5555" as decimal(10,5)), 1 as decimal(6,2)) +1223.56 +# column get decimal truncation & warnings +select column_get(column_create(1, "1223.5aa" AS char), 1 as decimal(32,10)); +column_get(column_create(1, "1223.5aa" AS char), 1 as decimal(32,10)) +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)); +column_get(column_create(1, "aa" AS char), 1 as decimal(32,10)) +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); +column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as decimal) +9999999999 +Warnings: +Warning 1264 Out of range value for column 'column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as decimal)' at row 1 +select column_get(column_create(1, 9223372036854775807 AS int), 1 as decimal); +column_get(column_create(1, 9223372036854775807 AS int), 1 as decimal) +9999999999 +Warnings: +Warning 1264 Out of range value for column 'column_get(column_create(1, 9223372036854775807 AS int), 1 as decimal)' at row 1 +select column_get(column_create(1, -9223372036854775808 AS int), 1 as decimal); +column_get(column_create(1, -9223372036854775808 AS int), 1 as decimal) +-9999999999 +Warnings: +Warning 1264 Out of range value for column 'column_get(column_create(1, -9223372036854775808 AS int), 1 as decimal)' at row 1 +select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as decimal); +column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as decimal) +9999999999 +Warnings: +Warning 1264 Out of range value for column 'column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as decimal)' at row 1 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as decimal); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as decimal) +9999999999 +Warnings: +Warning 1264 Out of range value for column 'column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as decimal)' at row 1 +select column_get(column_create(1, "1223.5555" as double), 1 as decimal(5,2)); +column_get(column_create(1, "1223.5555" as double), 1 as decimal(5,2)) +999.99 +Warnings: +Warning 1264 Out of range value for column 'column_get(column_create(1, "1223.5555" as double), 1 as decimal(5,2))' at row 1 +select column_get(column_create(1, "-1223.5555" as double), 1 as decimal(5,2)); +column_get(column_create(1, "-1223.5555" as double), 1 as decimal(5,2)) +-999.99 +Warnings: +Warning 1264 Out of range value for column 'column_get(column_create(1, "-1223.5555" as double), 1 as decimal(5,2))' at row 1 +select column_get(column_create(1, "1223.5555" AS double), 1 as decimal(3,2)); +column_get(column_create(1, "1223.5555" AS double), 1 as decimal(3,2)) +9.99 +Warnings: +Warning 1264 Out of range value for column 'column_get(column_create(1, "1223.5555" AS double), 1 as decimal(3,2))' at row 1 +select column_get(column_create(1, "1223.5555" AS decimal(10,5)), 1 as decimal(3,2)); +column_get(column_create(1, "1223.5555" AS decimal(10,5)), 1 as decimal(3,2)) +9.99 +Warnings: +Warning 1264 Out of range value for column 'column_get(column_create(1, "1223.5555" AS decimal(10,5)), 1 as decimal(3,2))' at row 1 +select column_get(column_create(1, 0.0 AS decimal,2, 0.0 as decimal), 1 as decimal); +column_get(column_create(1, 0.0 AS decimal,2, 0.0 as decimal), 1 as decimal) +0 +# +# column get datetime +# +select column_get(column_create(1, 20010203101112.121314 as double), 1 as datetime); +column_get(column_create(1, 20010203101112.121314 as double), 1 as datetime) +2001-02-03 10:11:12 +select column_get(column_create(1, 20010203101112.121314 as decimal), 1 as datetime); +column_get(column_create(1, 20010203101112.121314 as decimal), 1 as datetime) +2001-02-03 10:11:12 +select column_get(column_create(1, 20010203101112 as unsigned int), 1 as datetime); +column_get(column_create(1, 20010203101112 as unsigned int), 1 as datetime) +2001-02-03 10:11:12 +select column_get(column_create(1, 20010203101112 as int), 1 as datetime); +column_get(column_create(1, 20010203101112 as int), 1 as datetime) +2001-02-03 10:11:12 +select column_get(column_create(1, "20010203101112" as char), 1 as datetime); +column_get(column_create(1, "20010203101112" as char), 1 as datetime) +2001-02-03 10:11:12 +select column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as datetime); +column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as datetime) +2001-02-03 10:11:12 +select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as datetime); +column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as datetime) +2001-02-03 10:11:12 +select column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as datetime); +column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as datetime) +2001-02-03 10:11:12 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as datetime); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as datetime) +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)); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as datetime(0)) +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)); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as datetime(6)) +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); +column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as datetime) +2011-00-00 08:46:06 +select column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as datetime); +column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as datetime) +2011-00-01 08:46:06 +select column_get(column_create(1, 20010203 as unsigned int), 1 as datetime); +column_get(column_create(1, 20010203 as unsigned int), 1 as datetime) +2001-02-03 00:00:00 +select column_get(column_create(1, 20010203 as int), 1 as datetime); +column_get(column_create(1, 20010203 as int), 1 as datetime) +2001-02-03 00:00:00 +select column_get(column_create(1, 20010203), 1 as datetime); +column_get(column_create(1, 20010203), 1 as datetime) +2001-02-03 00:00:00 +select column_get(column_create(1, 20010203.0), 1 as datetime); +column_get(column_create(1, 20010203.0), 1 as datetime) +2001-02-03 00:00:00 +select column_get(column_create(1, 20010203.0 as double), 1 as datetime); +column_get(column_create(1, 20010203.0 as double), 1 as datetime) +2001-02-03 00:00:00 +select column_get(column_create(1, "2001-02-03"), 1 as datetime); +column_get(column_create(1, "2001-02-03"), 1 as datetime) +2001-02-03 00:00:00 +select column_get(column_create(1, "20010203"), 1 as datetime); +column_get(column_create(1, "20010203"), 1 as datetime) +2001-02-03 00:00:00 +select column_get(column_create(1, 0), 1 as datetime); +column_get(column_create(1, 0), 1 as datetime) +0000-00-00 00:00:00 +select column_get(column_create(1, "2001021"), 1 as datetime); +column_get(column_create(1, "2001021"), 1 as datetime) +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); +column_get(column_create(1, "8:46:06.23434" AS time), 1 as datetime) +2001-02-03 08:46:06 +select column_get(column_create(1, "-808:46:06.23434" AS time), 1 as datetime); +column_get(column_create(1, "-808:46:06.23434" AS time), 1 as datetime) +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); +column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as datetime) +2011-02-30 18:46:06 +select column_get(column_create(1, "0000-00-000" AS CHAR), 1 as datetime); +column_get(column_create(1, "0000-00-000" AS CHAR), 1 as datetime) +0000-00-00 00:00:00 +select column_get(column_create(1, "2001-00-02" AS CHAR), 1 as datetime); +column_get(column_create(1, "2001-00-02" AS CHAR), 1 as datetime) +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); +column_get(column_create(1, "1223.5aa" AS char), 1 as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '1223.5aa' +select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as datetime); +column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '1.8446744073709552e19' +select column_get(column_create(1, 9223372036854775807 AS int), 1 as datetime); +column_get(column_create(1, 9223372036854775807 AS int), 1 as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '9223372036854775807' +select column_get(column_create(1, -9223372036854775808 AS int), 1 as datetime); +column_get(column_create(1, -9223372036854775808 AS int), 1 as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '-9223372036854775808' +select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as datetime); +column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '99999999999999999999999999999' +select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as datetime); +column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as datetime) +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); +column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as datetime) +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); +column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as datetime) +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); +column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2011-02-30 8:46:06.23434' +select column_get(column_create(1, "20010231"), 1 as datetime); +column_get(column_create(1, "20010231"), 1 as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '20010231' +select column_get(column_create(1, "0" AS CHAR), 1 as datetime); +column_get(column_create(1, "0" AS CHAR), 1 as datetime) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '0' +# +# column get date +# +select column_get(column_create(1, 20010203101112.121314 as double), 1 as date); +column_get(column_create(1, 20010203101112.121314 as double), 1 as date) +2001-02-03 +select column_get(column_create(1, 20010203101112.121314 as decimal), 1 as date); +column_get(column_create(1, 20010203101112.121314 as decimal), 1 as date) +2001-02-03 +select column_get(column_create(1, 20010203101112 as unsigned int), 1 as date); +column_get(column_create(1, 20010203101112 as unsigned int), 1 as date) +2001-02-03 +select column_get(column_create(1, 20010203101112 as int), 1 as date); +column_get(column_create(1, 20010203101112 as int), 1 as date) +2001-02-03 +select column_get(column_create(1, "20010203101112" as char), 1 as date); +column_get(column_create(1, "20010203101112" as char), 1 as date) +2001-02-03 +select column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as date); +column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as date) +2001-02-03 +select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as date); +column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as date) +2001-02-03 +select column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as date); +column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as date) +2001-02-03 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as date); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as date) +2011-04-05 +select column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as date); +column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as date) +2011-00-00 +select column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as date); +column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as date) +2011-00-01 +select column_get(column_create(1, 20010203 as unsigned int), 1 as date); +column_get(column_create(1, 20010203 as unsigned int), 1 as date) +2001-02-03 +select column_get(column_create(1, 20010203 as int), 1 as date); +column_get(column_create(1, 20010203 as int), 1 as date) +2001-02-03 +select column_get(column_create(1, 20010203), 1 as date); +column_get(column_create(1, 20010203), 1 as date) +2001-02-03 +select column_get(column_create(1, 20010203.0), 1 as date); +column_get(column_create(1, 20010203.0), 1 as date) +2001-02-03 +select column_get(column_create(1, 20010203.0 as double), 1 as date); +column_get(column_create(1, 20010203.0 as double), 1 as date) +2001-02-03 +select column_get(column_create(1, "2001-02-03"), 1 as date); +column_get(column_create(1, "2001-02-03"), 1 as date) +2001-02-03 +select column_get(column_create(1, "20010203"), 1 as date); +column_get(column_create(1, "20010203"), 1 as date) +2001-02-03 +select column_get(column_create(1, 0), 1 as date); +column_get(column_create(1, 0), 1 as date) +0000-00-00 +select column_get(column_create(1, "2001021"), 1 as date); +column_get(column_create(1, "2001021"), 1 as date) +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); +column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as date) +2011-02-30 +select column_get(column_create(1, "0000-00-000" AS CHAR), 1 as date); +column_get(column_create(1, "0000-00-000" AS CHAR), 1 as date) +0000-00-00 +select column_get(column_create(1, "2001-00-02" AS CHAR), 1 as date); +column_get(column_create(1, "2001-00-02" AS CHAR), 1 as date) +2001-00-02 +set @@sql_mode=""; +# column get date truncation & warnings +select column_get(column_create(1, "1223.5aa" AS char), 1 as date); +column_get(column_create(1, "1223.5aa" AS char), 1 as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '1223.5aa' +select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as date); +column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '1.8446744073709552e19' +select column_get(column_create(1, 9223372036854775807 AS int), 1 as date); +column_get(column_create(1, 9223372036854775807 AS int), 1 as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '9223372036854775807' +select column_get(column_create(1, -9223372036854775808 AS int), 1 as date); +column_get(column_create(1, -9223372036854775808 AS int), 1 as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '-9223372036854775808' +select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as date); +column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '99999999999999999999999999999' +select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as date); +column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as date) +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); +column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as date) +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); +column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as date) +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); +column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2011-02-30 8:46:06.23434' +select column_get(column_create(1, "20010231"), 1 as date); +column_get(column_create(1, "20010231"), 1 as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '20010231' +select column_get(column_create(1, "0" AS CHAR), 1 as date); +column_get(column_create(1, "0" AS CHAR), 1 as date) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '0' +# +# column get time +# +select column_get(column_create(1, 20010203101112.121314 as double), 1 as time); +column_get(column_create(1, 20010203101112.121314 as double), 1 as time) +10:11:12 +select column_get(column_create(1, 20010203101112.121314 as decimal), 1 as time); +column_get(column_create(1, 20010203101112.121314 as decimal), 1 as time) +10:11:12 +select column_get(column_create(1, 20010203101112 as unsigned int), 1 as time); +column_get(column_create(1, 20010203101112 as unsigned int), 1 as time) +10:11:12 +select column_get(column_create(1, 8080102 as unsigned int), 1 as time); +column_get(column_create(1, 8080102 as unsigned int), 1 as time) +808:01:02 +select column_get(column_create(1, 20010203101112 as int), 1 as time); +column_get(column_create(1, 20010203101112 as int), 1 as time) +10:11:12 +select column_get(column_create(1, -8080102 as int), 1 as time); +column_get(column_create(1, -8080102 as int), 1 as time) +-808:01:02 +select column_get(column_create(1, "20010203101112" as char), 1 as time); +column_get(column_create(1, "20010203101112" as char), 1 as time) +10:11:12 +select column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as time); +column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as time) +10:11:12 +select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as time); +column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as time) +10:11:12 +select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as time(6)); +column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as time(6)) +10:11:12.121314 +select column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as time); +column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as time) +10:11:12 +select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as time(6)); +column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as time(6)) +08:46:06.234340 +select column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as time(6)); +column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as time(6)) +08:46:06.234340 +select column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as time(6)); +column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as time(6)) +08:46:06.234340 +select column_get(column_create(1, "830:46:06.23434" AS CHAR), 1 as time(6)); +column_get(column_create(1, "830:46:06.23434" AS CHAR), 1 as time(6)) +830:46:06.234340 +select column_get(column_create(1, "830:46:06" AS CHAR), 1 as time(6)); +column_get(column_create(1, "830:46:06" AS CHAR), 1 as time(6)) +830:46:06.000000 +select cast("-830:46:06.23434" AS time(6)); +cast("-830:46:06.23434" AS time(6)) +-830:46:06.234340 +select 1,cast("-830:46:06.23434" AS time(6)); +1 cast("-830:46:06.23434" AS time(6)) +1 -830:46:06.234340 +select hex(column_create(1, "-830:46:06.23434" AS CHAR)); +hex(column_create(1, "-830:46:06.23434" AS CHAR)) +000100010003082D3833303A34363A30362E3233343334 +select column_get(column_create(1, "-830:46:06.23434" AS CHAR), 1 as time(6)); +column_get(column_create(1, "-830:46:06.23434" AS CHAR), 1 as time(6)) +-830:46:06.234340 +select column_get(column_create(1, "0" AS CHAR), 1 as time); +column_get(column_create(1, "0" AS CHAR), 1 as time) +00:00:00 +select column_get(column_create(1, "6" AS CHAR), 1 as time); +column_get(column_create(1, "6" AS CHAR), 1 as time) +00:00:06 +select column_get(column_create(1, "1:6" AS CHAR), 1 as time); +column_get(column_create(1, "1:6" AS CHAR), 1 as time) +01:06:00 +select column_get(column_create(1, "2:1:6" AS CHAR), 1 as time); +column_get(column_create(1, "2:1:6" AS CHAR), 1 as time) +02:01:06 +select column_get(column_create(1, 0), 1 as time); +column_get(column_create(1, 0), 1 as time) +00:00:00 +select column_get(column_create(1, "2001021"), 1 as time); +column_get(column_create(1, "2001021"), 1 as time) +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); +column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as time) +18:46:06 +set @@sql_mode=""; +# column get date truncation & warnings +select column_get(column_create(1, "1223.5aa" AS char), 1 as time); +column_get(column_create(1, "1223.5aa" AS char), 1 as time) +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)); +column_get(column_create(1, "1223.5aa" AS char), 1 as time(3)) +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); +column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as time) +838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '1.8446744073709552e19' +select column_get(column_create(1, 9223372036854775807 AS int), 1 as time); +column_get(column_create(1, 9223372036854775807 AS int), 1 as time) +838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '9223372036854775807' +select column_get(column_create(1, -9223372036854775808 AS int), 1 as time); +column_get(column_create(1, -9223372036854775808 AS int), 1 as time) +-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); +column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as time) +838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '99999999999999999999999999999' +select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as time); +column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as time) +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); +column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as time) +NULL +Warnings: +Warning 1292 Truncated 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); +column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as time) +NULL +Warnings: +Warning 1292 Truncated 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); +column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as time) +08:46:06 +select column_get(column_create(1, "2001-02-03"), 1 as time); +column_get(column_create(1, "2001-02-03"), 1 as time) +00:20:01 +Warnings: +Warning 1292 Truncated incorrect time value: '2001-02-03' +select column_get(column_create(1, "20010203"), 1 as time); +column_get(column_create(1, "20010203"), 1 as time) +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)); +hex(column_add(column_create(1, 1212 as integer), 2, 1212 as integer)) +00020001000002001078097809 +select hex(column_add(column_create(1, 1212 as integer), 1, 1212 as integer)); +hex(column_add(column_create(1, 1212 as integer), 1, 1212 as integer)) +0001000100007809 +select hex(column_add(column_create(1, 1212 as integer), 1, NULL as integer)); +hex(column_add(column_create(1, 1212 as integer), 1, NULL as integer)) +000000 +select hex(column_add(column_create(1, 1212 as integer), 2, NULL as integer)); +hex(column_add(column_create(1, 1212 as integer), 2, NULL as integer)) +0001000100007809 +select hex(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer)); +hex(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer)) +000200010000020008167809 +select column_get(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer), 1 as integer); +column_get(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer), 1 as integer) +11 +select column_get(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer), 2 as integer); +column_get(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer), 2 as integer) +1212 +select hex(column_add(column_create(1, 1212 as integer), 1, 1212 as integer, 2, 11 as integer)); +hex(column_add(column_create(1, 1212 as integer), 1, 1212 as integer, 2, 11 as integer)) +000200010000020010780916 +select hex(column_add(column_create(1, NULL as integer), 1, 1212 as integer, 2, 11 as integer)); +hex(column_add(column_create(1, NULL as integer), 1, 1212 as integer, 2, 11 as integer)) +000200010000020010780916 +select hex(column_add(column_create(1, 1212 as integer, 2, 1212 as integer), 1, 11 as integer)); +hex(column_add(column_create(1, 1212 as integer, 2, 1212 as integer), 1, 11 as integer)) +000200010000020008167809 +select hex(column_add(column_create(1, 1), 1, null)); +hex(column_add(column_create(1, 1), 1, null)) +000000 +select column_list(column_add(column_create(1, 1), 1, null)); +column_list(column_add(column_create(1, 1), 1, null)) + +select column_list(column_add(column_create(1, 1), 1, "")); +column_list(column_add(column_create(1, 1), 1, "")) +`1` +select hex(column_add("", 1, 1)); +hex(column_add("", 1, 1)) +00010001000002 +# column delete +select hex(column_delete(column_create(1, 1212 as integer, 2, 1212 as integer), 1)); +hex(column_delete(column_create(1, 1212 as integer, 2, 1212 as integer), 1)) +0001000200007809 +select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2)); +hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2)) +0002000100000300080206 +select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 3)); +hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 3)) +0002000100000200080204 +select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 4)); +hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 4)) +000300010000020008030010020406 +select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2, 1)); +hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2, 1)) +00010003000006 +select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2, 3)); +hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2, 3)) +00010001000002 +select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 1, 2, 3)); +hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 1, 2, 3)) +000000 +select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 1, 2, 3, 10)); +hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 1, 2, 3, 10)) +000000 +select hex(column_delete(column_create(1, 1), 1)); +hex(column_delete(column_create(1, 1), 1)) +000000 +select hex(column_delete("", 1)); +hex(column_delete("", 1)) + +# column exists +select column_exists(column_create(1, 1212 as integer, 2, 1212 as integer), 1); +column_exists(column_create(1, 1212 as integer, 2, 1212 as integer), 1) +1 +select column_exists(column_create(1, 1212 as integer, 2, 1212 as integer), 4); +column_exists(column_create(1, 1212 as integer, 2, 1212 as integer), 4) +0 +# column list +select column_list(column_create(1, 1212 as integer, 2, 1212 as integer)); +column_list(column_create(1, 1212 as integer, 2, 1212 as integer)) +`1`,`2` +select column_list(column_create(1, 1212 as integer)); +column_list(column_create(1, 1212 as integer)) +`1` +select column_list(column_create(1, NULL as integer)); +column_list(column_create(1, NULL as integer)) + +# +# 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: 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' +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))); +hex(COLUMN_CREATE(0, COLUMN_GET(@a, 9 AS DECIMAL))) +000100000004 +select hex(COLUMN_CREATE(0, COLUMN_GET(@a, 9 AS DECIMAL(19,0)))); +hex(COLUMN_CREATE(0, COLUMN_GET(@a, 9 AS DECIMAL(19,0)))) +000100000004 +select hex(COLUMN_CREATE(0, COLUMN_GET(COLUMN_CREATE(0, 0.0 as decimal), 0 as decimal))); +hex(COLUMN_CREATE(0, COLUMN_GET(COLUMN_CREATE(0, 0.0 as decimal), 0 as decimal))) +000100000004 +select hex(COLUMN_CREATE(0, 0.0 as decimal)); +hex(COLUMN_CREATE(0, 0.0 as decimal)) +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 ) 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 ) 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); +column_get(column_create(1, "18446744073709552001" as char), 1 as int) +-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 50 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 default; +# fetching column test (names) +set names utf8; +select column_get(column_create("адын", 1212), "адын" as int); +column_get(column_create("адын", 1212), "адын" as int) +1212 +select column_get(column_create("1212", 2, "адын", 1, 3, 3), "адын" as int); +column_get(column_create("1212", 2, "адын", 1, 3, 3), "адын" as int) +1 +select column_get(column_create("1212", 2, "адын", 1, 3, 3), 1212 as int); +column_get(column_create("1212", 2, "адын", 1, 3, 3), 1212 as int) +2 +select column_get(column_create("1212", 2, "адын", 1, 3, 3), "3" as int); +column_get(column_create("1212", 2, "адын", 1, 3, 3), "3" as int) +3 +select column_get(column_create("1212", 2, "адын", 1, 3, 3), 3 as int); +column_get(column_create("1212", 2, "адын", 1, 3, 3), 3 as int) +3 +select column_get(column_create("1212", 2, "адын", 1, 3, 3), 4 as int); +column_get(column_create("1212", 2, "адын", 1, 3, 3), 4 as int) +NULL +select column_get(column_create("1212", 2, "адын", 1, 3, 3), "4" as int); +column_get(column_create("1212", 2, "адын", 1, 3, 3), "4" as int) +NULL +set names default; +# column existance test (names) +set names utf8; +select column_exists(column_create("адын", 1212), "адын"); +column_exists(column_create("адын", 1212), "адын") +1 +select column_exists(column_create("адын", 1212), "aады"); +column_exists(column_create("адын", 1212), "aады") +0 +select column_exists(column_create("1212", 2, "адын", 1, 3, 3), "адын"); +column_exists(column_create("1212", 2, "адын", 1, 3, 3), "адын") +1 +select column_exists(column_create("1212", 2, "адын", 1, 3, 3), 1212); +column_exists(column_create("1212", 2, "адын", 1, 3, 3), 1212) +1 +select column_exists(column_create("1212", 2, "адын", 1, 3, 3), "3"); +column_exists(column_create("1212", 2, "адын", 1, 3, 3), "3") +1 +select column_exists(column_create("1212", 2, "адын", 1, 3, 3), 3); +column_exists(column_create("1212", 2, "адын", 1, 3, 3), 3) +1 +select column_exists(column_create("1212", 2, "адын", 1, 3, 3), 4); +column_exists(column_create("1212", 2, "адын", 1, 3, 3), 4) +0 +select column_exists(column_create("1212", 2, "адын", 1, 3, 3), "4"); +column_exists(column_create("1212", 2, "адын", 1, 3, 3), "4") +0 +set names default; +# column changing test (names) +select hex(column_add(column_create(1, "AAA"), "b", "BBB")); +hex(column_add(column_create(1, "AAA"), "b", "BBB")) +0402000200000003000100430031620841414108424242 +select hex(column_add(column_create("1", "AAA"), "b", "BBB")); +hex(column_add(column_create("1", "AAA"), "b", "BBB")) +0402000200000003000100430031620841414108424242 +select column_get(column_add(column_create(1, "AAA"), "b", "BBB"), 1 as char); +column_get(column_add(column_create(1, "AAA"), "b", "BBB"), 1 as char) +AAA +select column_get(column_add(column_create(1, "AAA"), "b", "BBB"), "b" as char); +column_get(column_add(column_create(1, "AAA"), "b", "BBB"), "b" as char) +BBB +select hex(column_add(column_create("a", "AAA"), 1, "BBB")); +hex(column_add(column_create("a", "AAA"), 1, "BBB")) +0402000200000003000100430031610842424208414141 +select hex(column_add(column_create("a", "AAA"), "1", "BBB")); +hex(column_add(column_create("a", "AAA"), "1", "BBB")) +0402000200000003000100430031610842424208414141 +select hex(column_add(column_create("a", 1212 as integer), "b", "1212" as integer)); +hex(column_add(column_create("a", 1212 as integer), "b", "1212" as integer)) +04020002000000000001002000616278097809 +select hex(column_add(column_create("a", 1212 as integer), "a", "1212" as integer)); +hex(column_add(column_create("a", 1212 as integer), "a", "1212" as integer)) +040100010000000000617809 +select hex(column_add(column_create("a", 1212 as integer), "a", NULL as integer)); +hex(column_add(column_create("a", 1212 as integer), "a", NULL as integer)) +0400000000 +select hex(column_add(column_create("a", 1212 as integer), "b", NULL as integer)); +hex(column_add(column_create("a", 1212 as integer), "b", NULL as integer)) +040100010000000000617809 +select hex(column_add(column_create("a", 1212 as integer), "b", 1212 as integer, "a", 11 as integer)); +hex(column_add(column_create("a", 1212 as integer), "b", 1212 as integer, "a", 11 as integer)) +040200020000000000010010006162167809 +select column_get(column_add(column_create("a", 1212 as integer), "b", 1212 as integer, "a", 11 as integer), "a" as integer); +column_get(column_add(column_create("a", 1212 as integer), "b", 1212 as integer, "a", 11 as integer), "a" as integer) +11 +select column_get(column_add(column_create("a", 1212 as integer), "b", 1212 as integer, "a", 11 as integer), "b" as integer); +column_get(column_add(column_create("a", 1212 as integer), "b", 1212 as integer, "a", 11 as integer), "b" as integer) +1212 +select hex(column_add(column_create("a", 1212 as integer), "a", 1212 as integer, "b", 11 as integer)); +hex(column_add(column_create("a", 1212 as integer), "a", 1212 as integer, "b", 11 as integer)) +040200020000000000010020006162780916 +select hex(column_add(column_create("a", NULL as integer), "a", 1212 as integer, "b", 11 as integer)); +hex(column_add(column_create("a", NULL as integer), "a", 1212 as integer, "b", 11 as integer)) +040200020000000000010020006162780916 +select hex(column_add(column_create("a", 1212 as integer, "b", 1212 as integer), "a", 11 as integer)); +hex(column_add(column_create("a", 1212 as integer, "b", 1212 as integer), "a", 11 as integer)) +040200020000000000010010006162167809 +select hex(column_add(column_create("a", 1), "a", null)); +hex(column_add(column_create("a", 1), "a", null)) +0400000000 +select column_list(column_add(column_create("a", 1), "a", null)); +column_list(column_add(column_create("a", 1), "a", null)) + +select column_list(column_add(column_create("a", 1), "a", "")); +column_list(column_add(column_create("a", 1), "a", "")) +`a` +select hex(column_add("", "a", 1)); +hex(column_add("", "a", 1)) +0401000100000000006102 +# column delete (names) +select hex(column_delete(column_create("a", 1212 as integer, "b", 1212 as integer), "a")); +hex(column_delete(column_create("a", 1212 as integer, "b", 1212 as integer), "a")) +040100010000000000627809 +select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "b")); +hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "b")) +0402000200000000000100100061630206 +select hex(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer)); +hex(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer)) +0403000300000000000100100002002000616263020406 +select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "c")); +hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "c")) +0402000200000000000100100061620204 +select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "d")); +hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "d")) +0403000300000000000100100002002000616263020406 +select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "b", "a")); +hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "b", "a")) +0401000100000000006306 +select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "b", "c")); +hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "b", "c")) +0401000100000000006102 +select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "a", "b", "c")); +hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "a", "b", "c")) +0400000000 +select hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "a", "b", "c", "e")); +hex(column_delete(column_create("a", 1 as integer, "b", 2 as integer, "c", 3 as integer), "a", "b", "c", "e")) +0400000000 +select hex(column_delete(column_create("a", 1), "a")); +hex(column_delete(column_create("a", 1), "a")) +0400000000 +select hex(column_delete("", "a")); +hex(column_delete("", "a")) + +# +# 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 ); +COLUMN_GET( COLUMN_CREATE( 'col', 'val' ), NULL AS CHAR ) +NULL +SELECT COLUMN_GET( NULL, 'col' as char ); +COLUMN_GET( NULL, 'col' as char ) +NULL +SELECT COLUMN_EXISTS( COLUMN_CREATE( 'col', 'val' ), NULL); +COLUMN_EXISTS( COLUMN_CREATE( 'col', 'val' ), NULL) +NULL +SELECT COLUMN_EXISTS( NULL, 'col'); +COLUMN_EXISTS( NULL, 'col') +NULL +SELECT COLUMN_CREATE( NULL, 'val' ); +COLUMN_CREATE( NULL, 'val' ) +NULL +SELECT COLUMN_ADD( NULL, 'val', 'col'); +COLUMN_ADD( NULL, 'val', 'col') +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)); +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" +{"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)); +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)) +{"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"))); +column_json(column_create("val", "val", "emb", column_create("val2", "val2"))) +{"emb":{"val2":"val2"},"val":"val"} +select column_json(column_create(1, "val", 2, column_create(3, "val2"))); +column_json(column_create(1, "val", 2, column_create(3, "val2"))) +{"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)) from t1; +group_concat(cast(column_json(dyn) as char)) +{"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 +) +); +COLUMN_JSON( +COLUMN_CREATE( +'one', 123.456, +'two', 123.456 as DOUBLE +) +) +{"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); +column_get(column_create('float', 1.23456789012345E+100 as double), 'float' as double) +1.23456789012345e100 +select column_json(column_create('float', 1.23456789012345E+100 as double)); +column_json(column_create('float', 1.23456789012345E+100 as double)) +{"float":1.23456789012345e100} +select column_json(column_create('float', 1.23456789012345E+10 as double)); +column_json(column_create('float', 1.23456789012345E+10 as double)) +{"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)); +COLUMN_CHECK(COLUMN_CREATE('a',0 AS DECIMAL,'b',1 AS DECIMAL)) +1 +SELECT COLUMN_CHECK(COLUMN_CREATE('a',1 AS DECIMAL,'b',1 AS DECIMAL)); +COLUMN_CHECK(COLUMN_CREATE('a',1 AS DECIMAL,'b',1 AS DECIMAL)) +1 +SELECT COLUMN_JSON(COLUMN_CREATE('a',0 AS DECIMAL,'b',1 AS DECIMAL)); +COLUMN_JSON(COLUMN_CREATE('a',0 AS DECIMAL,'b',1 AS DECIMAL)) +{"a":0,"b":1} +SELECT COLUMN_JSON(COLUMN_CREATE('a',1 AS DECIMAL,'b',1 AS DECIMAL)); +COLUMN_JSON(COLUMN_CREATE('a',1 AS DECIMAL,'b',1 AS DECIMAL)) +{"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 utf8 ),2,'ttt'),1 as char charset utf8) AS `Name_exp_1` utf8 utf8_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 utf8)), + `dyncol1` blob DEFAULT column_add(`dyncol0`,'name1','value1'), + `value_dyncol1_name1` varchar(10) DEFAULT (column_get(`dyncol1`,'name1' as char charset utf8)), + `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 +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 +# |