Adding support for Dynamic columns (WL#34):

- COLUMN_CREATE(column_nr, value, [column_nr,value]...)
- COLUMN_ADD(blob,column_nr, value, column_nr,value]...)
- COLUMN_DELETE(blob, column_nr, column_nr...)
- COLUMN_EXISTS(blob, column_nr)
- COLUMN_LIST(blob, column_nr)
- COLUMN_GET(string, column_nr AS type)

Added cast(X as DOUBLE) and cast(x as INT)
Better warning and error messages for wrong cast's
Created some sub functions to simplify and reuse code.
Added a lot of conversation functions with error/warnings for what went wrong.
Fixed some issues when casting time to datetime.
Added functions to dynamic strings and Strings to allow one to move a string buffer from dynamic strings to String (to save malloc+ copy)
Added dynamic columns library to libmysqlclient


include/Makefile.am:
  Added ma_dyncol.h
include/decimal.h:
  Added 'const' to arguments for some functions.
include/my_sys.h:
  Added dynstr_reassociate()
include/my_time.h:
  Added TIME_SUBSECOND_RANGE
  Added double_to_datetime()
  Added flag argument to str_to_time()
libmysql/CMakeLists.txt:
  Added mysys/ma_dyncol.c
libmysql/Makefile.shared:
  Added ma_dyncol
libmysql/libmysql.c:
  Added argument to str_to_time()
mysql-test/r/bigint.result:
  Better error messages
mysql-test/r/cast.result:
  Better warning and error messages
  A lot of new cast() tests
mysql-test/r/func_math.result:
  Better warning messages
mysql-test/r/func_str.result:
  Better warning messages
mysql-test/r/func_time.result:
  Better warning messages
mysql-test/r/sp-vars.result:
  Better warning messages
mysql-test/r/strict.result:
  Better warning messages
  New test result
mysql-test/r/type_newdecimal.result:
  Better warning messages
mysql-test/r/warnings.result:
  Better warning messages
mysql-test/suite/funcs_1/r/innodb_func_view.result:
  Updated results after better cast warnings
mysql-test/suite/funcs_1/r/memory_func_view.result:
  Updated results after better cast warnings
mysql-test/suite/funcs_1/r/myisam_func_view.result:
  Updated results after better cast warnings
mysql-test/suite/optimizer_unfixed_bugs/t/bug43448.test:
  Added begin...commit to speed up test.
mysql-test/suite/parts/inc/part_supported_sql_funcs_delete.inc:
  Added begin...commit to speed up test.
mysql-test/suite/parts/inc/partition_supported_sql_funcs.inc:
  Added begin...commit to speed up test.
mysql-test/suite/parts/r/part_supported_sql_func_innodb.result:
  Added begin...commit to speed up test.
mysql-test/suite/parts/r/part_supported_sql_func_myisam.result:
  Added begin...commit to speed up test.
mysql-test/suite/parts/r/rpl_partition.result:
  Added begin...commit to speed up test.
mysql-test/suite/parts/t/part_supported_sql_func_innodb.test:
  Removed duplicated --big_test
mysql-test/suite/parts/t/rpl_partition.test:
  Added begin...commit to speed up test.
mysql-test/suite/pbxt/r/cast.result:
  Updated results after better cast warnings
mysql-test/suite/pbxt/r/func_str.result:
  Updated results after better cast warnings
mysql-test/suite/pbxt/r/type_newdecimal.result:
  Updated results after better cast warnings
mysql-test/suite/rpl/r/rpl_innodb_bug28430.result:
  Added begin...commit to speed up test.
mysql-test/suite/rpl/t/rpl_innodb_bug28430.test:
  Added begin...commit to speed up test.
mysql-test/suite/vcol/r/vcol_supported_sql_funcs_innodb.result:
  More warnings
mysql-test/suite/vcol/r/vcol_supported_sql_funcs_myisam.result:
  More warnings
mysql-test/t/cast.test:
  A lot of new cast() tests
mysql-test/t/strict.test:
  Added new test
mysys/CMakeLists.txt:
  Added ma_dyncol.c
mysys/Makefile.am:
  Added ma_dyncol.c
mysys/string.c:
  Added dynstr_reassociate() to move a buffer from dynamic_strings to some other allocator
sql-common/my_time.c:
  Added 'fuzzydate' flag to str_to_time()
  Added support for microseconds to my_time_to_str() and my_datetime_to_str()
  Reset second_parts in number_to_datetime()
  Added double_to_datetime()
sql/field.cc:
  Added double_to_longlong() and truncate_double() to simplify and reuse code
sql/field.h:
  New prototypes
sql/item.cc:
  Changed Item::get_date(MYSQL_TIME *ltime,uint fuzzydate) to be aware of type of argument.
  (Needed to make it microsecond safe and get better warnings).
  Updated call to str_to_time_with_warn()
sql/item.h:
  Added struct st_dyncall_create_def used by dynamic columns
  Added virtual bool dynamic_result() to tell if type of argument may change over calls.
sql/item_cmpfunc.cc:
  Added Item_func_dyncol_exists()
sql/item_cmpfunc.h:
  Added class Item_func_dyncol_exists
sql/item_create.cc:
  Added get_length_and_scale() to simplify other functions
  Simplified and extended create_func_cast()
  Added support for cast(X as double(X,Y))
  Added functions to create dynamic column functions.
sql/item_create.h:
  Added prototypes
sql/item_func.cc:
  Extended cast functions Item_func_signed() and Item_func_unsigned() to work with dynamic types
  Added Item_double_typecast()
sql/item_func.h:
  Added class Item_double_typecast()
sql/item_strfunc.cc:
  Added functions for COLUMN_CREATE(), COLUMN_ADD(), COLUMN_GET() and COLUMN_LIST()
sql/item_strfunc.h:
  Added classes for COLUMN_CREATE(), COLUMN_ADD(), COLUMN_GET() and COLUMN_LIST()
sql/item_timefunc.cc:
  Added flag argument to str_to_time_with_warn()
  Updated Item_char_typecast() to handle result type that may change between calls (for dynamic columns)
  Added Item_time_typecast::get_date() to ensure that we cast a datetime to time properly.
sql/item_timefunc.h:
  Added get_date() to Item_time_typecast() to allow proper results for casting time to datetime
sql/lex.h:
  Added new SQL function names
sql/my_decimal.cc:
  Added 'const' to some arguments.
  Better error message in case of errors (we now print out the wrong value)
  Added my_decimal2int()
sql/my_decimal.h:
  Moved some constants to my_decimal_limits.h
  Updated prototypes.
  Made my_decimal2int() a function as it's rather long (no reason to have it inline)
  Added decimal2my_decimal() function.
sql/mysql_priv.h:
  Prototypes for new functions
sql/share/errmsg.txt:
  New error messages for wrong casts and dynamic columns
sql/sql_acl.cc:
  Fixed indentation
sql/sql_base.cc:
  Added dynamic_column_error_message()
sql/sql_string.h:
  Added reassociate() to move a buffer to be owned by String object.
sql/sql_yacc.yy:
  Added syntax for COLUMN_ functions.
sql/time.cc:
  Updated str_to_datetime_with_warn() flag argument to same type as other functions
  Added conversion flag to str_to_time_with_warn() (Similar to all datetime functions)
  Added conversion functions with warnings: double_to_datetime_with_warn() and decimal_to_datetime_with_warn()
strings/decimal.c:
  Added 'const' to arguments for some functions.
unittest/mysys/Makefile.am:
  Added test for dynamic columns code

1 files changed, 462 insertions, 0 deletions

diff --git a/mysql-test/t/dyncol.test b/mysql-test/t/dyncol.test
new file mode 100644
index 00000000000..8e1c99a48a4
--- /dev/null
+++ b/mysql-test/t/dyncol.test
@@ -0,0 +1,462 @@
+#
+# Dynamic column function test
+#
+
+--echo #
+--echo # column create
+--echo #
+select hex(COLUMN_CREATE(1, NULL AS char character set utf8));
+select hex(COLUMN_CREATE(1, "afaf" AS char character set utf8));
+select hex(COLUMN_CREATE(1, 1212 AS char character set utf8));
+select hex(COLUMN_CREATE(1, 12.12 AS char character set utf8));
+select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS char character set utf8));
+select hex(COLUMN_CREATE(1, NULL AS unsigned int));
+select hex(COLUMN_CREATE(1, 1212 AS unsigned int));
+select hex(COLUMN_CREATE(1, 7 AS unsigned int));
+select hex(COLUMN_CREATE(1, 8 AS unsigned int));
+select hex(COLUMN_CREATE(1, 127 AS unsigned int));
+select hex(COLUMN_CREATE(1, 128 AS unsigned int));
+select hex(COLUMN_CREATE(1, 12.12 AS unsigned int));
+select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS unsigned int));
+select hex(COLUMN_CREATE(1, NULL AS int));
+select hex(COLUMN_CREATE(1, 1212 AS int));
+select hex(COLUMN_CREATE(1, 7 AS int));
+select hex(COLUMN_CREATE(1, 8 AS int));
+select hex(COLUMN_CREATE(1, 127 AS int));
+select hex(COLUMN_CREATE(1, 128 AS int));
+select hex(COLUMN_CREATE(1, 12.12 AS int));
+select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS int));
+select hex(COLUMN_CREATE(1, NULL AS double));
+select hex(COLUMN_CREATE(1, 1212 AS double));
+select hex(COLUMN_CREATE(1, 12.12 AS double));
+select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS double));
+select hex(COLUMN_CREATE(1, NULL AS decimal));
+select hex(COLUMN_CREATE(1, 1212 AS decimal));
+select hex(COLUMN_CREATE(1, 7 AS decimal));
+select hex(COLUMN_CREATE(1, 8 AS decimal));
+select hex(COLUMN_CREATE(1, 127 AS decimal));
+select hex(COLUMN_CREATE(1, 128 AS decimal));
+select hex(COLUMN_CREATE(1, 12.12 AS decimal));
+select hex(COLUMN_CREATE(1, 99999999999999999999999999999 AS decimal));
+select hex(COLUMN_CREATE(1, NULL AS date));
+select hex(COLUMN_CREATE(1, "2011-04-05" AS date)); 
+select hex(COLUMN_CREATE(1, NULL AS time));
+select hex(COLUMN_CREATE(1, "0:45:49.000001" AS time)); 
+select hex(COLUMN_CREATE(1, NULL AS datetime));
+select hex(COLUMN_CREATE(1, "2011-04-05 0:45:49.000001" AS datetime)); 
+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));
+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));
+
+
+--echo #
+--echo # column get uint
+--echo #
+select 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 int);
+explain extended
+select 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);
+select column_get(column_create(1, 1212 AS double), 1 as unsigned int);
+select column_get(column_create(1, 1212 AS int), 1 as unsigned int);
+select column_get(column_create(1, "1212" AS char), 1 as unsigned int);
+select column_get(column_create(1, "2011-04-05" AS date), 1 as unsigned int);
+select column_get(column_create(1, "8:46:06.23434" AS time), 1 as unsigned int);
+select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as unsigned int);
+select column_get(column_create(1, NULL AS unsigned int), 1 as unsigned int);
+--echo # column geint truncation & warnings
+select column_get(column_create(1, -1212 AS int), 1 as unsigned int);
+select column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as unsigned int);
+select column_get(column_create(1, 999.9999999999999999 AS decimal), 1 as unsigned int);
+select column_get(column_create(1, -1 AS decimal), 1 as unsigned int);
+select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as unsigned int);
+select column_get(column_create(1, 999.9 AS double), 1 as unsigned int);
+select column_get(column_create(1, -1 AS double), 1 as unsigned int);
+select column_get(column_create(1, "1212III" AS char), 1 as unsigned int);
+
+--echo #
+--echo # column get int
+--echo #
+select column_get(column_create(1, 1212 AS int), 1 as int);
+explain extended
+select 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);
+select column_get(column_create(1, -1212 AS int), 1 as int);
+select column_get(column_create(1, 1212 AS decimal), 1 as int);
+select column_get(column_create(1, 1212 AS double), 1 as int);
+select column_get(column_create(1, 1212 AS unsigned int), 1 as int);
+select column_get(column_create(1, "1212" AS char), 1 as int);
+select column_get(column_create(1, "-1212" AS char), 1 as int);
+select column_get(column_create(1, "2011-04-05" AS date), 1 as int);
+select column_get(column_create(1, "8:46:06.23434" AS time), 1 as int);
+select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as int);
+select column_get(column_create(1, NULL AS int), 1 as int);
+--echo #column gett truncation & warnings
+select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as int);
+select column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as int);
+select column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as int);
+select column_get(column_create(1, 999.9999999999999999 AS decimal), 1 as int);
+select column_get(column_create(1, 999.9 AS double), 1 as int);
+select column_get(column_create(1, -99999999999999999999999999999 AS double), 1 as int);
+select column_get(column_create(1, "-1212III" AS char), 1 as int);
+select column_get(column_create(1, "1212III" AS char), 1 as int);
+
+--echo #
+--echo #column get char
+--echo #
+select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset utf8);
+explain extended
+select 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);
+select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as char charset utf8);
+select column_get(column_create(1, 1212 AS int), 1 as char charset utf8);
+select column_get(column_create(1, -1212 AS int), 1 as char charset utf8);
+select column_get(column_create(1, 9223372036854775807 AS int), 1 as char charset utf8);
+select column_get(column_create(1, -9223372036854775808 AS int), 1 as char charset utf8);
+select column_get(column_create(1, 1212.12 AS decimal), 1 as char charset utf8);
+select column_get(column_create(1, 1212.12 AS double), 1 as char charset utf8);
+select column_get(column_create(1, "2011-04-05" AS date), 1 as char charset utf8);
+select column_get(column_create(1, "8:46:06.23434" AS time), 1 as char charset utf8);
+select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as char charset utf8);
+select column_get(column_create(1, NULL AS char charset utf8), 1 as char charset utf8);
+select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset binary);
+explain extended
+select column_get(column_create(1, "1212" AS char charset utf8), 1 as char charset binary);
+
+--echo #
+--echo # column get real
+--echo #
+select 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);
+explain extended
+select 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);
+select column_get(column_create(1, 9223372036854775807 AS int), 1 as double);
+select column_get(column_create(1, -9223372036854775808 AS int), 1 as double); 
+select column_get(column_create(1, 99999999999999999999999999999 AS decimal), 1 as double);
+select column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as double);
+select column_get(column_create(1, "2011-04-05" AS date), 1 as double);
+select column_get(column_create(1, "8:46:06.23434" AS time), 1 as double);
+select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as double);
+select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as double(20,6));
+select column_get(column_create(1, NULL AS double), 1 as double);
+
+-- echo # column get real truncation & warnings
+select column_get(column_create(1, "1223.5aa" AS char), 1 as double);
+select column_get(column_create(1, "aa" AS char), 1 as double);
+select column_get(column_create(1, "1223.5555" AS double), 1 as double(5,2));
+select column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2));
+
+--echo #
+--echo # column get decimal
+--echo #
+select column_get(column_create(1, 1212.12 AS double), 1 as decimal);
+select column_get(column_create(1, 1212.12 AS double), 1 as decimal(6,2));
+explain extended
+select 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));
+select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as decimal(20,0));
+select column_get(column_create(1, 9223372036854775807 AS int), 1 as decimal(32,0));
+select column_get(column_create(1, -9223372036854775808 AS int), 1 as decimal(32,0)); 
+select column_get(column_create(1, -99999999999999999999999999999 AS decimal), 1 as decimal(40,10));
+select column_get(column_create(1, "2011-04-05" AS date), 1 as decimal(32,6));
+select column_get(column_create(1, "8:46:06.23434" AS time), 1 as decimal(32,6));
+select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as decimal(32,6));
+select column_get(column_create(1, NULL as decimal), 1 as decimal(32,10));
+select column_get(column_create(1, "1223.5555" as decimal(10,5)), 1 as decimal(6,2));
+
+-- echo # column get decimal truncation & warnings
+select column_get(column_create(1, "1223.5aa" AS char), 1 as decimal(32,10));
+select column_get(column_create(1, "aa" AS char), 1 as decimal(32,10));
+select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as decimal);
+select column_get(column_create(1, 9223372036854775807 AS int), 1 as decimal);
+select column_get(column_create(1, -9223372036854775808 AS int), 1 as decimal); 
+select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as decimal);
+select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as decimal);
+select column_get(column_create(1, "1223.5555" as double), 1 as decimal(5,2));
+select column_get(column_create(1, "-1223.5555" as double), 1 as decimal(5,2));
+select column_get(column_create(1, "1223.5555" AS double), 1 as decimal(3,2));
+select column_get(column_create(1, "1223.5555" AS decimal(10,5)), 1 as decimal(3,2));
+
+--echo #
+--echo # column get datetime
+--echo #
+select column_get(column_create(1, 20010203101112.121314 as double), 1 as datetime);
+select column_get(column_create(1, 20010203101112.121314 as decimal), 1 as datetime);
+select column_get(column_create(1, 20010203101112 as unsigned int), 1 as datetime);
+select column_get(column_create(1, 20010203101112 as int), 1 as datetime);
+select column_get(column_create(1, "20010203101112" as char), 1 as datetime);
+select column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as datetime);
+select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as datetime);
+select column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as datetime);
+select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as datetime);
+select column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as datetime);
+select column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as datetime);
+
+select column_get(column_create(1, 20010203 as unsigned int), 1 as datetime);
+select column_get(column_create(1, 20010203 as int), 1 as datetime);
+select column_get(column_create(1, 20010203), 1 as datetime);
+select column_get(column_create(1, 20010203.0), 1 as datetime);
+select column_get(column_create(1, 20010203.0 as double), 1 as datetime);
+select column_get(column_create(1, "2001-02-03"), 1 as datetime);
+select column_get(column_create(1, "20010203"), 1 as datetime);
+select column_get(column_create(1, 0), 1 as datetime);
+select column_get(column_create(1, "2001021"), 1 as datetime);
+
+select column_get(column_create(1, "8:46:06.23434" AS time), 1 as datetime);
+
+set @@sql_mode="allow_invalid_dates";
+select column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as datetime);
+select column_get(column_create(1, "0000-00-000" AS CHAR), 1 as datetime);
+select column_get(column_create(1, "2001-00-02" AS CHAR), 1 as datetime);
+set @@sql_mode="";
+
+-- echo # column get datetime truncation & warnings
+select column_get(column_create(1, "1223.5aa" AS char), 1 as datetime);
+select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as datetime);
+select column_get(column_create(1, 9223372036854775807 AS int), 1 as datetime);
+select column_get(column_create(1, -9223372036854775808 AS int), 1 as datetime); 
+select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as datetime);
+select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as datetime);
+select column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as datetime);
+select column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as datetime);
+select column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as datetime);
+select column_get(column_create(1, "20010231"), 1 as datetime);
+select column_get(column_create(1, "0" AS CHAR), 1 as datetime);
+
+
+--echo #
+--echo # column get date
+--echo #
+select column_get(column_create(1, 20010203101112.121314 as double), 1 as date);
+select column_get(column_create(1, 20010203101112.121314 as decimal), 1 as date);
+select column_get(column_create(1, 20010203101112 as unsigned int), 1 as date);
+select column_get(column_create(1, 20010203101112 as int), 1 as date);
+select column_get(column_create(1, "20010203101112" as char), 1 as date);
+select column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as date);
+select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as date);
+select column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as date);
+select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as date);
+select column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as date);
+select column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as date);
+
+select column_get(column_create(1, 20010203 as unsigned int), 1 as date);
+select column_get(column_create(1, 20010203 as int), 1 as date);
+select column_get(column_create(1, 20010203), 1 as date);
+select column_get(column_create(1, 20010203.0), 1 as date);
+select column_get(column_create(1, 20010203.0 as double), 1 as date);
+select column_get(column_create(1, "2001-02-03"), 1 as date);
+select column_get(column_create(1, "20010203"), 1 as date);
+select column_get(column_create(1, 0), 1 as date);
+select column_get(column_create(1, "2001021"), 1 as date);
+
+set @@sql_mode="allow_invalid_dates";
+select column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as date);
+select column_get(column_create(1, "0000-00-000" AS CHAR), 1 as date);
+select column_get(column_create(1, "2001-00-02" AS CHAR), 1 as date);
+set @@sql_mode="";
+
+-- echo # column get date truncation & warnings
+select column_get(column_create(1, "1223.5aa" AS char), 1 as date);
+select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as date);
+select column_get(column_create(1, 9223372036854775807 AS int), 1 as date);
+select column_get(column_create(1, -9223372036854775808 AS int), 1 as date); 
+select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as date);
+select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as date);
+select column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as date);
+select column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as date);
+select column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as date);
+select column_get(column_create(1, "20010231"), 1 as date);
+select column_get(column_create(1, "0" AS CHAR), 1 as date);
+
+--echo #
+--echo # column get time
+--echo #
+select column_get(column_create(1, 20010203101112.121314 as double), 1 as time);
+select column_get(column_create(1, 20010203101112.121314 as decimal), 1 as time);
+select column_get(column_create(1, 20010203101112 as unsigned int), 1 as time);
+select column_get(column_create(1, 20010203101112 as int), 1 as time);
+select column_get(column_create(1, "20010203101112" as char), 1 as time);
+select column_get(column_create(1, "2001-02-03 10:11:12" as char), 1 as time);
+select column_get(column_create(1, "2001-02-03 10:11:12.121314" as char), 1 as time);
+select column_get(column_create(1, "2001-02-03 10:11:12.121314"), 1 as time);
+select column_get(column_create(1, "2011-04-05 8:46:06.23434" AS datetime), 1 as time);
+select column_get(column_create(1, "2011-00-00 8:46:06.23434" AS CHAR), 1 as time);
+select column_get(column_create(1, "2011-00-01 8:46:06.23434" AS CHAR), 1 as time);
+select column_get(column_create(1, "830:46:06.23434" AS CHAR), 1 as time);
+select cast("-830:46:06.23434" AS time);
+select 1,cast("-830:46:06.23434" AS time);
+select hex(column_create(1, "-830:46:06.23434" AS CHAR));
+select column_get(column_create(1, "-830:46:06.23434" AS CHAR), 1 as time);
+select column_get(column_create(1, "0" AS CHAR), 1 as time);
+select column_get(column_create(1, "6" AS CHAR), 1 as time);
+select column_get(column_create(1, "1:6" AS CHAR), 1 as time);
+select column_get(column_create(1, "2:1:6" AS CHAR), 1 as time);
+
+select column_get(column_create(1, 0), 1 as time);
+select column_get(column_create(1, "2001021"), 1 as time);
+
+set @@sql_mode="allow_invalid_dates";
+select column_get(column_create(1, "2011-02-30 18:46:06.23434" AS CHAR), 1 as time);
+set @@sql_mode="";
+
+-- echo # column get date truncation & warnings
+select column_get(column_create(1, "1223.5aa" AS char), 1 as time);
+select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as time);
+select column_get(column_create(1, 9223372036854775807 AS int), 1 as time);
+select column_get(column_create(1, -9223372036854775808 AS int), 1 as time); 
+select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as time);
+select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as time);
+select column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as time);
+select column_get(column_create(1, "2011-13-01 8:46:06.23434" AS CHAR), 1 as time);
+select column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as time);
+select column_get(column_create(1, "2001-02-03"), 1 as time);
+select column_get(column_create(1, "20010203"), 1 as time);
+
+
+-- echo # column add
+select hex(column_add(column_create(1, 1212 as integer), 2, 1212 as integer));
+select hex(column_add(column_create(1, 1212 as integer), 1, 1212 as integer));
+select hex(column_add(column_create(1, 1212 as integer), 1, NULL as integer));
+select hex(column_add(column_create(1, 1212 as integer), 2, NULL as integer));
+select hex(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer));
+select column_get(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer), 1 as integer);
+select column_get(column_add(column_create(1, 1212 as integer), 2, 1212 as integer, 1, 11 as integer), 2 as integer);
+select hex(column_add(column_create(1, 1212 as integer), 1, 1212 as integer, 2, 11 as integer));
+select hex(column_add(column_create(1, NULL as integer), 1, 1212 as integer, 2, 11 as integer));
+select hex(column_add(column_create(1, 1212 as integer, 2, 1212 as integer), 1, 11 as integer));
+select hex(column_add(column_create(1, 1), 1, null));
+select column_list(column_add(column_create(1, 1), 1, null));
+select column_list(column_add(column_create(1, 1), 1, ""));
+select hex(column_add("", 1, 1));
+
+-- echo # column delete
+select hex(column_delete(column_create(1, 1212 as integer, 2, 1212 as integer), 1));
+select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2));
+select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 3));
+select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 4));
+select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2, 1));
+select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 2, 3));
+select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 1, 2, 3));
+select hex(column_delete(column_create(1, 1 as integer, 2, 2 as integer, 3, 3 as integer), 1, 2, 3, 10));
+select hex(column_delete(column_create(1, 1), 1));
+select hex(column_delete("", 1));
+
+-- echo # column exists
+select column_exists(column_create(1, 1212 as integer, 2, 1212 as integer), 1);
+select column_exists(column_create(1, 1212 as integer, 2, 1212 as integer), 4);
+
+-- echo # column list
+select column_list(column_create(1, 1212 as integer, 2, 1212 as integer));
+select column_list(column_create(1, 1212 as integer));
+select column_list(column_create(1, NULL as integer));
+
+--echo #
+--echo # check error handling
+--echo #
+--error ER_DYN_COL_DATA
+select HEX(COLUMN_CREATE(1, 5, 1, 5));
+--error 1064
+select HEX(COLUMN_CREATE("", 1, 5, 1, 5));
+--error ER_DYN_COL_WRONG_FORMAT
+select COLUMN_LIST("a");
+--error ER_DYN_COL_WRONG_FORMAT
+select column_delete("a", 1);
+select hex(column_delete("", 1));
+--error ER_DYN_COL_DATA
+select hex(column_delete("", -1));
+--error ER_DYN_COL_DATA
+select hex(column_create(-1, 1));
+--error ER_DYN_COL_DATA
+select hex(column_create(65536, 1));
+--error ER_DYN_COL_DATA
+select hex(column_add("", -1, 1));
+--error ER_DYN_COL_DATA
+select hex(column_add("", 65536, 1));
+select hex(column_get("", -1 as int));
+
+--echo #
+--echo # Test with table
+--echo #
+
+# create table with 'str' to hold a set of dynamic columns
+create table t1 (id int primary key, str mediumblob);
+insert into t1 values (1, ''), (2, ''), (3, ''), (4, ''), (5, null);
+
+# Selecting a non existing column
+select id, str, column_get(str, 1 as int) from t1;
+
+# Add some dynamic columns. One and do it with create or add.
+update t1 set str=column_create(1, id, 2, "a") where id < 3;
+update t1 set str=column_add(str, 1, id, 2, "b") where id >= 4;
+
+# Show some data, if it exists
+select id, column_get(str, 1 as int),  column_get(str, 2 as char) from t1 where column_exists(str,1) or column_exists(str,2);
+
+# Add data to row 5 and 6
+update t1 set str=column_create(1, id, 10, "test") where id = 5;
+insert into t1 values (6, column_create(10, "test2"));
+
+# Update some of the columns and add a new column 3
+update t1 set str=column_add(str, 2, 'c', 1, column_get(str, 1 as int) + 1, 3, 100) where id > 2;
+
+# Check data
+select id, length(str), column_get(str, 1 as int),  column_get(str, 2 as char), column_get(str, 3 as int) from t1;
+
+# You can do anything with the columns, like SUM() or GROUP
+select column_get(str, 2 as char), sum(column_get(str, 1 as int)) from t1 group by column_get(str, 2 as char);
+select column_get(str, 2 as char), sum(column_get(str, 1 as int)) from t1 where column_exists(str, 2) <> 0 group by 1;
+select sum(column_get(str, 1 as int)) from t1 group by column_get(str, 2 as char) order by sum(column_get(str, 1 as int)) desc;
+select sum(column_get(str, 1 as int)) from t1 group by column_get(str, 2 as char) having sum(column_get(str, 1 as int)) > 2;
+select sum(column_get(str, 1 as int)) from t1 where column_get(str, 3 as int) > 50 group by column_get(str, 2 as char);
+
+# Deleting of column
+select id, column_list(str) from t1 where id= 5;
+update t1 set str=column_delete(str, 3, 4, 2) where id= 5;
+
+select id, length(str), column_list(str), column_get(str, 1 as int),  column_get(str, 2 as char), column_get(str, 3 as int) from t1;
+
+update t1 set str=column_add(str, 4, 45 as char, 2, 'c') where id= 5;
+select id, length(str), column_list(str), column_get(str, 1 as int),  column_get(str, 2 as char), column_get(str, 3 as int) from t1 where id = 5;
+
+# Check which column exists
+select id, length(str), column_list(str), column_exists(str, 4) from t1;
+select sum(column_get(str, 1 as int)), column_list(str) from t1 group by 2;
+select id, hex(str) from t1;
+
+# Check with a bit larger strings
+
+update t1 set str=column_add(str, 4, repeat("a", 100000)) where id=5;
+select id from t1 where column_get(str,4 as char(100000)) = repeat("a", 100000);
+select id from t1 where column_get(str,4 as char(100)) = repeat("a", 100);
+update t1 set str=column_add(str, 4, repeat("b", 10000)) where id=5;
+select id from t1 where column_get(str,4 as char(100000)) = repeat("b", 10000);
+update t1 set str=column_add(str, 4, repeat("c", 100)) where id=5;
+select id from t1 where column_get(str,4 as char(100000)) = repeat("c", 100);
+update t1 set str=column_add(str, 4, repeat("d", 10000)) where id=5;
+select id from t1 where column_get(str,4 as char(100000)) = repeat("d", 10000);
+update t1 set str=column_add(str, 4, repeat("e", 10), 5, repeat("f", 100000)) where id=5;
+select id from t1 where column_get(str,5 as char(100000)) = repeat("f", 100000);
+select id, column_list(str), length(str) from t1 where id=5;
+update t1 set str=column_delete(str, 5) where id=5;
+select id, column_list(str), length(str) from t1 where id=5;
+
+drop table t1;