diff options
Diffstat (limited to 'plugin/type_test')
14 files changed, 2956 insertions, 0 deletions
diff --git a/plugin/type_test/CMakeLists.txt b/plugin/type_test/CMakeLists.txt new file mode 100644 index 00000000..132458cb --- /dev/null +++ b/plugin/type_test/CMakeLists.txt @@ -0,0 +1,17 @@ +# Copyright (c) 2019, MariaDB corporation +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; version 2 of the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA + +MYSQL_ADD_PLUGIN(type_test plugin.cc RECOMPILE_FOR_EMBEDDED + MODULE_ONLY COMPONENT Test) diff --git a/plugin/type_test/mysql-test/type_test/suite.opt b/plugin/type_test/mysql-test/type_test/suite.opt new file mode 100644 index 00000000..edf6d838 --- /dev/null +++ b/plugin/type_test/mysql-test/type_test/suite.opt @@ -0,0 +1 @@ +--plugin-load-add=$TYPE_TEST_SO diff --git a/plugin/type_test/mysql-test/type_test/suite.pm b/plugin/type_test/mysql-test/type_test/suite.pm new file mode 100644 index 00000000..fe38ca57 --- /dev/null +++ b/plugin/type_test/mysql-test/type_test/suite.pm @@ -0,0 +1,10 @@ +package My::Suite::Type_test; + +@ISA = qw(My::Suite); + +return "No TYPE_TEST plugin" unless $ENV{TYPE_TEST_SO}; + +sub is_default { 1 } + +bless { }; + diff --git a/plugin/type_test/mysql-test/type_test/type_test_double-debug.result b/plugin/type_test/mysql-test/type_test/type_test_double-debug.result new file mode 100644 index 00000000..975decca --- /dev/null +++ b/plugin/type_test/mysql-test/type_test/type_test_double-debug.result @@ -0,0 +1,35 @@ +# +# MDEV-20016 Add MariaDB_DATA_TYPE_PLUGIN +# +# Testing that a user-defined handler is resolved by name +# when opening a FRM file. +SET @old_debug_dbug=@@debug_dbug; +SET @@debug_dbug="+d,frm_data_type_info"; +CREATE TABLE t1 (a TEST_DOUBLE); +Warnings: +Note 1105 build_frm_image: Field data type info length: 13 +Note 1105 DBUG: [0] name='a' type_info='test_double' +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +Warnings: +Note 1105 DBUG: [0] name='a' type_info='test_double' +DROP TABLE t1; +SET @@debug_dbug=@old_debug_dbug; +# Testing what happens on failure to resolve a type handler by name +SET @old_debug_dbug=@@debug_dbug; +SET @@debug_dbug="+d,frm_data_type_info"; +CREATE TABLE t1 (a TEST_DOUBLE); +Warnings: +Note 1105 build_frm_image: Field data type info length: 13 +Note 1105 DBUG: [0] name='a' type_info='test_double' +FLUSH TABLES; +SET @@debug_dbug="+d,emulate_handler_by_name_or_error_failure"; +SHOW CREATE TABLE t1; +ERROR HY000: Unknown data type: 'test_double' +SELECT * FROM t1; +ERROR HY000: Unknown data type: 'test_double' +DROP TABLE t1; +SET @@debug_dbug=@old_debug_dbug; diff --git a/plugin/type_test/mysql-test/type_test/type_test_double-debug.test b/plugin/type_test/mysql-test/type_test/type_test_double-debug.test new file mode 100644 index 00000000..ee5cf430 --- /dev/null +++ b/plugin/type_test/mysql-test/type_test/type_test_double-debug.test @@ -0,0 +1,32 @@ +--source include/have_debug.inc + +--echo # +--echo # MDEV-20016 Add MariaDB_DATA_TYPE_PLUGIN +--echo # + +--echo # Testing that a user-defined handler is resolved by name +--echo # when opening a FRM file. + +SET @old_debug_dbug=@@debug_dbug; +SET @@debug_dbug="+d,frm_data_type_info"; +CREATE TABLE t1 (a TEST_DOUBLE); +--enable_prepare_warnings +SHOW CREATE TABLE t1; +--disable_prepare_warnings +DROP TABLE t1; +SET @@debug_dbug=@old_debug_dbug; + + +--echo # Testing what happens on failure to resolve a type handler by name + +SET @old_debug_dbug=@@debug_dbug; +SET @@debug_dbug="+d,frm_data_type_info"; +CREATE TABLE t1 (a TEST_DOUBLE); +FLUSH TABLES; +SET @@debug_dbug="+d,emulate_handler_by_name_or_error_failure"; +--error ER_UNKNOWN_DATA_TYPE +SHOW CREATE TABLE t1; +--error ER_UNKNOWN_DATA_TYPE +SELECT * FROM t1; +DROP TABLE t1; +SET @@debug_dbug=@old_debug_dbug; diff --git a/plugin/type_test/mysql-test/type_test/type_test_double.result b/plugin/type_test/mysql-test/type_test/type_test_double.result new file mode 100644 index 00000000..19ebb6cd --- /dev/null +++ b/plugin/type_test/mysql-test/type_test/type_test_double.result @@ -0,0 +1,704 @@ +# +# MDEV-20016 Add MariaDB_DATA_TYPE_PLUGIN +# +SELECT +PLUGIN_NAME, +PLUGIN_VERSION, +PLUGIN_STATUS, +PLUGIN_TYPE, +PLUGIN_AUTHOR, +PLUGIN_DESCRIPTION, +PLUGIN_LICENSE, +PLUGIN_MATURITY, +PLUGIN_AUTH_VERSION +FROM INFORMATION_SCHEMA.PLUGINS +WHERE PLUGIN_TYPE='DATA TYPE' + AND PLUGIN_NAME='test_double'; +PLUGIN_NAME test_double +PLUGIN_VERSION 1.0 +PLUGIN_STATUS ACTIVE +PLUGIN_TYPE DATA TYPE +PLUGIN_AUTHOR MariaDB Corporation +PLUGIN_DESCRIPTION Data type TEST_DOUBLE +PLUGIN_LICENSE GPL +PLUGIN_MATURITY Experimental +PLUGIN_AUTH_VERSION 1.0 +CREATE TABLE t1 (a TEST_DOUBLE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TEST_DOUBLE(4)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` test_double(4,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TEST_DOUBLE(10)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` test_double(10,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TEST_DOUBLE(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` test_double(20,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TEST_DOUBLE(4,2)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` test_double(4,2) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TEST_DOUBLE(10,5)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` test_double(10,5) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TEST_DOUBLE(20,10)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` test_double(20,10) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +SELECT CAST('100' AS TEST_DOUBLE) AS cast; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def cast 5 22 3 Y 32896 31 63 +cast +100 +BEGIN NOT ATOMIC +DECLARE a TEST_DOUBLE DEFAULT 256; +SELECT HEX(a), a; +END; +$$ +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def HEX(a) 253 44 3 Y 0 0 8 +def a a 5 22 3 Y 32768 31 63 +HEX(a) a +100 256 +CREATE FUNCTION f1(p TEST_DOUBLE) RETURNS TEST_DOUBLE RETURN 1; +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `f1`(p TEST_DOUBLE) RETURNS test_double +RETURN 1 latin1 latin1_swedish_ci latin1_swedish_ci +SELECT f1(10); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def f1(10) f1(10) 5 22 1 Y 32768 31 63 +f1(10) +1 +DROP FUNCTION f1; +CREATE TABLE t1 (a TEST_DOUBLE); +CREATE TABLE t2 AS SELECT a FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a TEST_DOUBLE); +CREATE TABLE t2 AS SELECT COALESCE(a,a) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,a)` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a TEST_DOUBLE); +CREATE TABLE t2 AS SELECT LEAST(a,a) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,a)` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 AS SELECT MIN(a), MAX(a) FROM t1; +SELECT * FROM t2; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t2 t2 MIN(a) MIN(a) 5 22 1 Y 32768 31 63 +def test t2 t2 MAX(a) MAX(a) 5 22 1 Y 32768 31 63 +MIN(a) MAX(a) +1 2 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `MIN(a)` test_double DEFAULT NULL, + `MAX(a)` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (id INT, a TEST_DOUBLE); +INSERT INTO t1 VALUES (1,1),(1,2),(2,1),(2,2); +CREATE TABLE t2 AS SELECT id, MIN(a), MAX(a) FROM t1 GROUP BY id; +SELECT * FROM t2; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t2 t2 id id 3 11 1 Y 32768 0 63 +def test t2 t2 MIN(a) MIN(a) 5 22 1 Y 32768 31 63 +def test t2 t2 MAX(a) MAX(a) 5 22 1 Y 32768 31 63 +id MIN(a) MAX(a) +1 1 2 +2 1 2 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) DEFAULT NULL, + `MIN(a)` test_double DEFAULT NULL, + `MAX(a)` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 AS SELECT DISTINCT a FROM t1; +SELECT * FROM t2; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t2 t2 a a 5 22 1 Y 32768 31 63 +a +1 +2 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 AS SELECT (SELECT a FROM t1) AS c1; +SELECT * FROM t2; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t2 t2 c1 c1 5 22 1 Y 32768 31 63 +c1 +1 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a TEST_DOUBLE); +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +# Testing CREATE..LIKE +CREATE TABLE t1 (a TEST_DOUBLE); +CREATE TABLE t2 LIKE t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +# Testing CREATE..SELECT +CREATE TABLE t1 (a TEST_DOUBLE); +CREATE TABLE t2 AS SELECT * FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +# Testing ALTER +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (10),(20); +SELECT * FROM t1; +a +10 +20 +ALTER TABLE t1 MODIFY a INT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +a +10 +20 +ALTER TABLE t1 MODIFY a TEST_DOUBLE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +a +10 +20 +DROP TABLE t1; +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (10),(20); +ALTER TABLE t1 ADD b TEST_DOUBLE DEFAULT 0; +SELECT * FROM t1; +a b +10 0 +20 0 +DROP TABLE t1; +# Testing metadata views +CREATE TABLE t1 (a TEST_DOUBLE); +SELECT +TABLE_CATALOG, +TABLE_SCHEMA, +TABLE_NAME, +COLUMN_NAME, +ORDINAL_POSITION, +COLUMN_DEFAULT, +IS_NULLABLE, +DATA_TYPE, +CHARACTER_MAXIMUM_LENGTH, +CHARACTER_OCTET_LENGTH, +NUMERIC_PRECISION, +NUMERIC_SCALE, +DATETIME_PRECISION, +CHARACTER_SET_NAME, +COLLATION_NAME, +COLUMN_TYPE, +EXTRA +FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; +TABLE_CATALOG def +TABLE_SCHEMA test +TABLE_NAME t1 +COLUMN_NAME a +ORDINAL_POSITION 1 +COLUMN_DEFAULT NULL +IS_NULLABLE YES +DATA_TYPE test_double +CHARACTER_MAXIMUM_LENGTH NULL +CHARACTER_OCTET_LENGTH NULL +NUMERIC_PRECISION 22 +NUMERIC_SCALE 31 +DATETIME_PRECISION NULL +CHARACTER_SET_NAME NULL +COLLATION_NAME NULL +COLUMN_TYPE test_double +EXTRA +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +a test_double YES NULL +DROP TABLE t1; +# Testing indexing +CREATE TABLE t1 (a TEST_DOUBLE, KEY(a)); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7); +EXPLAIN SELECT * FROM t1 WHERE a=3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 9 const 1 Using index +DROP TABLE t1; +# Testing aggregation for result +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7); +CREATE TABLE t2 AS SELECT +COALESCE(a,1) AS c1, +COALESCE(a,1.0) AS c2, +COALESCE(a,1e0) AS c3 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` test_double DEFAULT NULL, + `c2` test_double DEFAULT NULL, + `c3` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2 ORDER BY c1; +c1 c2 c3 +0 0 0 +1 1 1 +2 2 2 +3 3 3 +4 4 4 +5 5 5 +6 6 6 +7 7 7 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_DOUBLE, +c_tinyint TINYINT, +c_smallint SMALLINT, +c_mediumint MEDIUMINT, +c_int INT, +c_bigint BIGINT +); +CREATE TABLE t2 AS SELECT +COALESCE(c,c_tinyint), +COALESCE(c,c_smallint), +COALESCE(c,c_mediumint), +COALESCE(c,c_bigint) +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(c,c_tinyint)` test_double DEFAULT NULL, + `COALESCE(c,c_smallint)` test_double DEFAULT NULL, + `COALESCE(c,c_mediumint)` test_double DEFAULT NULL, + `COALESCE(c,c_bigint)` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_DOUBLE, +c_time TIME, +c_date DATE, +c_datetime DATETIME, +c_timestamp TIMESTAMP +); +SELECT COALESCE(c, c_time) FROM t1; +ERROR HY000: Illegal parameter data types test_double and time for operation 'coalesce' +SELECT COALESCE(c, c_date) FROM t1; +ERROR HY000: Illegal parameter data types test_double and date for operation 'coalesce' +SELECT COALESCE(c, c_datetime) FROM t1; +ERROR HY000: Illegal parameter data types test_double and datetime for operation 'coalesce' +SELECT COALESCE(c, c_timestamp) FROM t1; +ERROR HY000: Illegal parameter data types test_double and timestamp for operation 'coalesce' +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_DOUBLE, +c_char CHAR(32), +c_varchar VARCHAR(32), +c_tinytext TINYTEXT, +c_text TEXT, +c_mediumtext MEDIUMTEXT, +c_longtext LONGTEXT +); +SELECT COALESCE(c, c_char) FROM t1; +ERROR HY000: Illegal parameter data types test_double and char for operation 'coalesce' +SELECT COALESCE(c, c_varchar) FROM t1; +ERROR HY000: Illegal parameter data types test_double and varchar for operation 'coalesce' +SELECT COALESCE(c, c_tinytext) FROM t1; +ERROR HY000: Illegal parameter data types test_double and tinyblob for operation 'coalesce' +SELECT COALESCE(c, c_text) FROM t1; +ERROR HY000: Illegal parameter data types test_double and blob for operation 'coalesce' +SELECT COALESCE(c, c_mediumtext) FROM t1; +ERROR HY000: Illegal parameter data types test_double and mediumblob for operation 'coalesce' +SELECT COALESCE(c, c_longtext) FROM t1; +ERROR HY000: Illegal parameter data types test_double and longblob for operation 'coalesce' +DROP TABLE t1; +# Testing aggregation for min/max +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7); +CREATE TABLE t2 AS SELECT +LEAST(a,1) AS c1, +LEAST(a,1.0) AS c2, +LEAST(a,1e0) AS c3 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` test_double DEFAULT NULL, + `c2` test_double DEFAULT NULL, + `c3` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2 ORDER BY c1; +c1 c2 c3 +0 0 0 +1 1 1 +1 1 1 +1 1 1 +1 1 1 +1 1 1 +1 1 1 +1 1 1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_DOUBLE, +c_tinyint TINYINT, +c_smallint SMALLINT, +c_mediumint MEDIUMINT, +c_int INT, +c_bigint BIGINT +); +CREATE TABLE t2 AS SELECT +LEAST(c,c_tinyint), +LEAST(c,c_smallint), +LEAST(c,c_mediumint), +LEAST(c,c_bigint) +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(c,c_tinyint)` test_double DEFAULT NULL, + `LEAST(c,c_smallint)` test_double DEFAULT NULL, + `LEAST(c,c_mediumint)` test_double DEFAULT NULL, + `LEAST(c,c_bigint)` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_DOUBLE, +c_time TIME, +c_date DATE, +c_datetime DATETIME, +c_timestamp TIMESTAMP +); +SELECT LEAST(c, c_time) FROM t1; +ERROR HY000: Illegal parameter data types test_double and time for operation 'least' +SELECT LEAST(c, c_date) FROM t1; +ERROR HY000: Illegal parameter data types test_double and date for operation 'least' +SELECT LEAST(c, c_datetime) FROM t1; +ERROR HY000: Illegal parameter data types test_double and datetime for operation 'least' +SELECT LEAST(c, c_timestamp) FROM t1; +ERROR HY000: Illegal parameter data types test_double and timestamp for operation 'least' +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_DOUBLE, +c_char CHAR(32), +c_varchar VARCHAR(32), +c_tinytext TINYTEXT, +c_text TEXT, +c_mediumtext MEDIUMTEXT, +c_longtext LONGTEXT +); +SELECT LEAST(c, c_char) FROM t1; +ERROR HY000: Illegal parameter data types test_double and char for operation 'least' +SELECT LEAST(c, c_varchar) FROM t1; +ERROR HY000: Illegal parameter data types test_double and varchar for operation 'least' +SELECT LEAST(c, c_tinytext) FROM t1; +ERROR HY000: Illegal parameter data types test_double and tinyblob for operation 'least' +SELECT LEAST(c, c_text) FROM t1; +ERROR HY000: Illegal parameter data types test_double and blob for operation 'least' +SELECT LEAST(c, c_mediumtext) FROM t1; +ERROR HY000: Illegal parameter data types test_double and mediumblob for operation 'least' +SELECT LEAST(c, c_longtext) FROM t1; +ERROR HY000: Illegal parameter data types test_double and longblob for operation 'least' +DROP TABLE t1; +# Testing aggregation for numeric operation - plus +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7); +CREATE TABLE t2 AS SELECT +a+1 AS c1, +a+1.0 AS c2, +a+1e0 AS c3 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` test_double DEFAULT NULL, + `c2` test_double DEFAULT NULL, + `c3` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2 ORDER BY c1; +c1 c2 c3 +1 1 1 +2 2 2 +3 3 3 +4 4 4 +5 5 5 +6 6 6 +7 7 7 +8 8 8 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_DOUBLE, +c_tinyint TINYINT, +c_smallint SMALLINT, +c_mediumint MEDIUMINT, +c_int INT, +c_bigint BIGINT +); +CREATE TABLE t2 AS SELECT +c + c_tinyint, +c + c_smallint, +c + c_mediumint, +c + c_bigint +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c + c_tinyint` test_double DEFAULT NULL, + `c + c_smallint` test_double DEFAULT NULL, + `c + c_mediumint` test_double DEFAULT NULL, + `c + c_bigint` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_DOUBLE, +c_time TIME, +c_date DATE, +c_datetime DATETIME, +c_timestamp TIMESTAMP +); +SELECT c + c_time FROM t1; +ERROR HY000: Illegal parameter data types test_double and time for operation '+' +SELECT c + c_date FROM t1; +ERROR HY000: Illegal parameter data types test_double and date for operation '+' +SELECT c + c_datetime FROM t1; +ERROR HY000: Illegal parameter data types test_double and datetime for operation '+' +SELECT c + c_timestamp FROM t1; +ERROR HY000: Illegal parameter data types test_double and timestamp for operation '+' +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_DOUBLE, +c_char CHAR(32), +c_varchar VARCHAR(32), +c_tinytext TINYTEXT, +c_text TEXT, +c_mediumtext MEDIUMTEXT, +c_longtext LONGTEXT +); +SELECT c + c_char FROM t1; +ERROR HY000: Illegal parameter data types test_double and char for operation '+' +SELECT c + c_varchar FROM t1; +ERROR HY000: Illegal parameter data types test_double and varchar for operation '+' +SELECT c + c_tinytext FROM t1; +ERROR HY000: Illegal parameter data types test_double and tinyblob for operation '+' +SELECT c + c_text FROM t1; +ERROR HY000: Illegal parameter data types test_double and blob for operation '+' +SELECT c + c_mediumtext FROM t1; +ERROR HY000: Illegal parameter data types test_double and mediumblob for operation '+' +SELECT c + c_longtext FROM t1; +ERROR HY000: Illegal parameter data types test_double and longblob for operation '+' +DROP TABLE t1; +# Testing aggregation for numeric operation - minus +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); +CREATE TABLE t2 AS SELECT +a-1 AS c1, +a-1.0 AS c2, +a-1e0 AS c3 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` test_double DEFAULT NULL, + `c2` test_double DEFAULT NULL, + `c3` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2 ORDER BY c1; +c1 c2 c3 +0 0 0 +1 1 1 +2 2 2 +3 3 3 +4 4 4 +5 5 5 +6 6 6 +7 7 7 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_DOUBLE, +c_tinyint TINYINT, +c_smallint SMALLINT, +c_mediumint MEDIUMINT, +c_int INT, +c_bigint BIGINT +); +CREATE TABLE t2 AS SELECT +c - c_tinyint, +c - c_smallint, +c - c_mediumint, +c - c_bigint +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c - c_tinyint` test_double DEFAULT NULL, + `c - c_smallint` test_double DEFAULT NULL, + `c - c_mediumint` test_double DEFAULT NULL, + `c - c_bigint` test_double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_DOUBLE, +c_time TIME, +c_date DATE, +c_datetime DATETIME, +c_timestamp TIMESTAMP +); +SELECT c - c_time FROM t1; +ERROR HY000: Illegal parameter data types test_double and time for operation '-' +SELECT c - c_date FROM t1; +ERROR HY000: Illegal parameter data types test_double and date for operation '-' +SELECT c - c_datetime FROM t1; +ERROR HY000: Illegal parameter data types test_double and datetime for operation '-' +SELECT c - c_timestamp FROM t1; +ERROR HY000: Illegal parameter data types test_double and timestamp for operation '-' +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_DOUBLE, +c_char CHAR(32), +c_varchar VARCHAR(32), +c_tinytext TINYTEXT, +c_text TEXT, +c_mediumtext MEDIUMTEXT, +c_longtext LONGTEXT +); +SELECT c - c_char FROM t1; +ERROR HY000: Illegal parameter data types test_double and char for operation '-' +SELECT c - c_varchar FROM t1; +ERROR HY000: Illegal parameter data types test_double and varchar for operation '-' +SELECT c - c_tinytext FROM t1; +ERROR HY000: Illegal parameter data types test_double and tinyblob for operation '-' +SELECT c - c_text FROM t1; +ERROR HY000: Illegal parameter data types test_double and blob for operation '-' +SELECT c - c_mediumtext FROM t1; +ERROR HY000: Illegal parameter data types test_double and mediumblob for operation '-' +SELECT c - c_longtext FROM t1; +ERROR HY000: Illegal parameter data types test_double and longblob for operation '-' +DROP TABLE t1; +# Testing CAST to other data types +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (20000102); +CREATE TABLE t2 AS SELECT +a, +CAST(a AS CHAR), +CAST(a AS DECIMAL), +CAST(a AS DOUBLE), +CAST(a AS SIGNED), +CAST(a AS UNSIGNED), +CAST(a AS TIME), +CAST(a AS DATETIME), +CAST(a AS DATE) +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` test_double DEFAULT NULL, + `CAST(a AS CHAR)` varchar(22) DEFAULT NULL, + `CAST(a AS DECIMAL)` decimal(10,0) DEFAULT NULL, + `CAST(a AS DOUBLE)` double DEFAULT NULL, + `CAST(a AS SIGNED)` bigint(20) DEFAULT NULL, + `CAST(a AS UNSIGNED)` bigint(20) unsigned DEFAULT NULL, + `CAST(a AS TIME)` time DEFAULT NULL, + `CAST(a AS DATETIME)` datetime DEFAULT NULL, + `CAST(a AS DATE)` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2; +a 20000102 +CAST(a AS CHAR) 20000102 +CAST(a AS DECIMAL) 20000102 +CAST(a AS DOUBLE) 20000102 +CAST(a AS SIGNED) 20000102 +CAST(a AS UNSIGNED) 20000102 +CAST(a AS TIME) 00:00:00 +CAST(a AS DATETIME) 2000-01-02 00:00:00 +CAST(a AS DATE) 2000-01-02 +DROP TABLE t2; +DROP TABLE t1; diff --git a/plugin/type_test/mysql-test/type_test/type_test_double.test b/plugin/type_test/mysql-test/type_test/type_test_double.test new file mode 100644 index 00000000..993eab95 --- /dev/null +++ b/plugin/type_test/mysql-test/type_test/type_test_double.test @@ -0,0 +1,530 @@ +--echo # +--echo # MDEV-20016 Add MariaDB_DATA_TYPE_PLUGIN +--echo # + +--vertical_results +SELECT + PLUGIN_NAME, + PLUGIN_VERSION, + PLUGIN_STATUS, + PLUGIN_TYPE, + PLUGIN_AUTHOR, + PLUGIN_DESCRIPTION, + PLUGIN_LICENSE, + PLUGIN_MATURITY, + PLUGIN_AUTH_VERSION +FROM INFORMATION_SCHEMA.PLUGINS + WHERE PLUGIN_TYPE='DATA TYPE' + AND PLUGIN_NAME='test_double'; +--horizontal_results + +CREATE TABLE t1 (a TEST_DOUBLE); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_DOUBLE(4)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_DOUBLE(10)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_DOUBLE(20)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +CREATE TABLE t1 (a TEST_DOUBLE(4,2)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_DOUBLE(10,5)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_DOUBLE(20,10)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--disable_ps_protocol +--enable_metadata +SELECT CAST('100' AS TEST_DOUBLE) AS cast; +--disable_metadata +--enable_ps_protocol + +--disable_ps_protocol +--enable_metadata +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE a TEST_DOUBLE DEFAULT 256; + SELECT HEX(a), a; +END; +$$ +DELIMITER ;$$ +--disable_metadata +--enable_ps_protocol + +CREATE FUNCTION f1(p TEST_DOUBLE) RETURNS TEST_DOUBLE RETURN 1; +SHOW CREATE FUNCTION f1; +--disable_ps_protocol +--enable_metadata +SELECT f1(10); +--disable_metadata +--enable_ps_protocol +DROP FUNCTION f1; + +CREATE TABLE t1 (a TEST_DOUBLE); +CREATE TABLE t2 AS SELECT a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_DOUBLE); +CREATE TABLE t2 AS SELECT COALESCE(a,a) FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_DOUBLE); +CREATE TABLE t2 AS SELECT LEAST(a,a) FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 AS SELECT MIN(a), MAX(a) FROM t1; +--disable_ps_protocol +--enable_metadata +SELECT * FROM t2; +--disable_metadata +--enable_ps_protocol +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (id INT, a TEST_DOUBLE); +INSERT INTO t1 VALUES (1,1),(1,2),(2,1),(2,2); +CREATE TABLE t2 AS SELECT id, MIN(a), MAX(a) FROM t1 GROUP BY id; +--disable_ps_protocol +--enable_metadata +SELECT * FROM t2; +--disable_metadata +--enable_ps_protocol +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 AS SELECT DISTINCT a FROM t1; +--disable_ps_protocol +--enable_metadata +SELECT * FROM t2; +--disable_metadata +--enable_ps_protocol +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 AS SELECT (SELECT a FROM t1) AS c1; +--disable_ps_protocol +--enable_metadata +SELECT * FROM t2; +--disable_metadata +--enable_ps_protocol +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_DOUBLE); +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +--echo # Testing CREATE..LIKE + +CREATE TABLE t1 (a TEST_DOUBLE); +CREATE TABLE t2 LIKE t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +--echo # Testing CREATE..SELECT + +CREATE TABLE t1 (a TEST_DOUBLE); +CREATE TABLE t2 AS SELECT * FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + + +--echo # Testing ALTER + +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (10),(20); +SELECT * FROM t1; +ALTER TABLE t1 MODIFY a INT; +SHOW CREATE TABLE t1; +SELECT * FROM t1; +ALTER TABLE t1 MODIFY a TEST_DOUBLE; +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (10),(20); +ALTER TABLE t1 ADD b TEST_DOUBLE DEFAULT 0; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # Testing metadata views + +CREATE TABLE t1 (a TEST_DOUBLE); +--vertical_results +SELECT + TABLE_CATALOG, + TABLE_SCHEMA, + TABLE_NAME, + COLUMN_NAME, + ORDINAL_POSITION, + COLUMN_DEFAULT, + IS_NULLABLE, + DATA_TYPE, + CHARACTER_MAXIMUM_LENGTH, + CHARACTER_OCTET_LENGTH, + NUMERIC_PRECISION, + NUMERIC_SCALE, + DATETIME_PRECISION, + CHARACTER_SET_NAME, + COLLATION_NAME, + COLUMN_TYPE, + EXTRA +FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; +--horizontal_results +SHOW COLUMNS FROM t1; +DROP TABLE t1; + + +--echo # Testing indexing + +CREATE TABLE t1 (a TEST_DOUBLE, KEY(a)); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7); +EXPLAIN SELECT * FROM t1 WHERE a=3; +DROP TABLE t1; + +--echo # Testing aggregation for result + +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7); +CREATE TABLE t2 AS SELECT + COALESCE(a,1) AS c1, + COALESCE(a,1.0) AS c2, + COALESCE(a,1e0) AS c3 +FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2 ORDER BY c1; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_DOUBLE, + c_tinyint TINYINT, + c_smallint SMALLINT, + c_mediumint MEDIUMINT, + c_int INT, + c_bigint BIGINT +); +CREATE TABLE t2 AS SELECT + COALESCE(c,c_tinyint), + COALESCE(c,c_smallint), + COALESCE(c,c_mediumint), + COALESCE(c,c_bigint) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_DOUBLE, + c_time TIME, + c_date DATE, + c_datetime DATETIME, + c_timestamp TIMESTAMP +); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_time) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_date) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_datetime) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_timestamp) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_DOUBLE, + c_char CHAR(32), + c_varchar VARCHAR(32), + c_tinytext TINYTEXT, + c_text TEXT, + c_mediumtext MEDIUMTEXT, + c_longtext LONGTEXT +); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_char) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_varchar) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_tinytext) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_text) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_mediumtext) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_longtext) FROM t1; +DROP TABLE t1; + +--echo # Testing aggregation for min/max + +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7); +CREATE TABLE t2 AS SELECT + LEAST(a,1) AS c1, + LEAST(a,1.0) AS c2, + LEAST(a,1e0) AS c3 +FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2 ORDER BY c1; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_DOUBLE, + c_tinyint TINYINT, + c_smallint SMALLINT, + c_mediumint MEDIUMINT, + c_int INT, + c_bigint BIGINT +); +CREATE TABLE t2 AS SELECT + LEAST(c,c_tinyint), + LEAST(c,c_smallint), + LEAST(c,c_mediumint), + LEAST(c,c_bigint) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_DOUBLE, + c_time TIME, + c_date DATE, + c_datetime DATETIME, + c_timestamp TIMESTAMP +); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_time) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_date) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_datetime) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_timestamp) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_DOUBLE, + c_char CHAR(32), + c_varchar VARCHAR(32), + c_tinytext TINYTEXT, + c_text TEXT, + c_mediumtext MEDIUMTEXT, + c_longtext LONGTEXT +); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_char) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_varchar) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_tinytext) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_text) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_mediumtext) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_longtext) FROM t1; +DROP TABLE t1; + + +--echo # Testing aggregation for numeric operation - plus + +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7); +CREATE TABLE t2 AS SELECT + a+1 AS c1, + a+1.0 AS c2, + a+1e0 AS c3 +FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2 ORDER BY c1; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_DOUBLE, + c_tinyint TINYINT, + c_smallint SMALLINT, + c_mediumint MEDIUMINT, + c_int INT, + c_bigint BIGINT +); +CREATE TABLE t2 AS SELECT + c + c_tinyint, + c + c_smallint, + c + c_mediumint, + c + c_bigint +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_DOUBLE, + c_time TIME, + c_date DATE, + c_datetime DATETIME, + c_timestamp TIMESTAMP +); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_time FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_date FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_datetime FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_timestamp FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_DOUBLE, + c_char CHAR(32), + c_varchar VARCHAR(32), + c_tinytext TINYTEXT, + c_text TEXT, + c_mediumtext MEDIUMTEXT, + c_longtext LONGTEXT +); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_char FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_varchar FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_tinytext FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_text FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_mediumtext FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_longtext FROM t1; +DROP TABLE t1; + +--echo # Testing aggregation for numeric operation - minus + +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); +CREATE TABLE t2 AS SELECT + a-1 AS c1, + a-1.0 AS c2, + a-1e0 AS c3 +FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2 ORDER BY c1; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_DOUBLE, + c_tinyint TINYINT, + c_smallint SMALLINT, + c_mediumint MEDIUMINT, + c_int INT, + c_bigint BIGINT +); +CREATE TABLE t2 AS SELECT + c - c_tinyint, + c - c_smallint, + c - c_mediumint, + c - c_bigint +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_DOUBLE, + c_time TIME, + c_date DATE, + c_datetime DATETIME, + c_timestamp TIMESTAMP +); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_time FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_date FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_datetime FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_timestamp FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_DOUBLE, + c_char CHAR(32), + c_varchar VARCHAR(32), + c_tinytext TINYTEXT, + c_text TEXT, + c_mediumtext MEDIUMTEXT, + c_longtext LONGTEXT +); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_char FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_varchar FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_tinytext FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_text FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_mediumtext FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_longtext FROM t1; +DROP TABLE t1; + +--echo # Testing CAST to other data types + +CREATE TABLE t1 (a TEST_DOUBLE); +INSERT INTO t1 VALUES (20000102); +CREATE TABLE t2 AS SELECT + a, + CAST(a AS CHAR), + CAST(a AS DECIMAL), + CAST(a AS DOUBLE), + CAST(a AS SIGNED), + CAST(a AS UNSIGNED), + CAST(a AS TIME), + CAST(a AS DATETIME), + CAST(a AS DATE) +FROM t1; +SHOW CREATE TABLE t2; +--vertical_results +SELECT * FROM t2; +--horizontal_results +DROP TABLE t2; +DROP TABLE t1; diff --git a/plugin/type_test/mysql-test/type_test/type_test_int8-debug.result b/plugin/type_test/mysql-test/type_test/type_test_int8-debug.result new file mode 100644 index 00000000..952a63c8 --- /dev/null +++ b/plugin/type_test/mysql-test/type_test/type_test_int8-debug.result @@ -0,0 +1,35 @@ +# +# MDEV-20016 Add MariaDB_DATA_TYPE_PLUGIN +# +# Testing that a user-defined handler is resolved by name +# when opening a FRM file. +SET @old_debug_dbug=@@debug_dbug; +SET @@debug_dbug="+d,frm_data_type_info"; +CREATE TABLE t1 (a TEST_INT8); +Warnings: +Note 1105 build_frm_image: Field data type info length: 11 +Note 1105 DBUG: [0] name='a' type_info='test_int8' +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` test_int8(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +Warnings: +Note 1105 DBUG: [0] name='a' type_info='test_int8' +DROP TABLE t1; +SET @@debug_dbug=@old_debug_dbug; +# Testing what happens on failure to resolve a type handler by name +SET @old_debug_dbug=@@debug_dbug; +SET @@debug_dbug="+d,frm_data_type_info"; +CREATE TABLE t1 (a TEST_INT8); +Warnings: +Note 1105 build_frm_image: Field data type info length: 11 +Note 1105 DBUG: [0] name='a' type_info='test_int8' +FLUSH TABLES; +SET @@debug_dbug="+d,emulate_handler_by_name_or_error_failure"; +SHOW CREATE TABLE t1; +ERROR HY000: Unknown data type: 'test_int8' +SELECT * FROM t1; +ERROR HY000: Unknown data type: 'test_int8' +DROP TABLE t1; +SET @@debug_dbug=@old_debug_dbug; diff --git a/plugin/type_test/mysql-test/type_test/type_test_int8-debug.test b/plugin/type_test/mysql-test/type_test/type_test_int8-debug.test new file mode 100644 index 00000000..7e60bf3e --- /dev/null +++ b/plugin/type_test/mysql-test/type_test/type_test_int8-debug.test @@ -0,0 +1,32 @@ +--source include/have_debug.inc + +--echo # +--echo # MDEV-20016 Add MariaDB_DATA_TYPE_PLUGIN +--echo # + +--echo # Testing that a user-defined handler is resolved by name +--echo # when opening a FRM file. + +SET @old_debug_dbug=@@debug_dbug; +SET @@debug_dbug="+d,frm_data_type_info"; +CREATE TABLE t1 (a TEST_INT8); +--enable_prepare_warnings +SHOW CREATE TABLE t1; +--disable_prepare_warnings +DROP TABLE t1; +SET @@debug_dbug=@old_debug_dbug; + + +--echo # Testing what happens on failure to resolve a type handler by name + +SET @old_debug_dbug=@@debug_dbug; +SET @@debug_dbug="+d,frm_data_type_info"; +CREATE TABLE t1 (a TEST_INT8); +FLUSH TABLES; +SET @@debug_dbug="+d,emulate_handler_by_name_or_error_failure"; +--error ER_UNKNOWN_DATA_TYPE +SHOW CREATE TABLE t1; +--error ER_UNKNOWN_DATA_TYPE +SELECT * FROM t1; +DROP TABLE t1; +SET @@debug_dbug=@old_debug_dbug; diff --git a/plugin/type_test/mysql-test/type_test/type_test_int8.result b/plugin/type_test/mysql-test/type_test/type_test_int8.result new file mode 100644 index 00000000..f18c990c --- /dev/null +++ b/plugin/type_test/mysql-test/type_test/type_test_int8.result @@ -0,0 +1,683 @@ +# +# MDEV-20016 Add MariaDB_DATA_TYPE_PLUGIN +# +SELECT +PLUGIN_NAME, +PLUGIN_VERSION, +PLUGIN_STATUS, +PLUGIN_TYPE, +PLUGIN_AUTHOR, +PLUGIN_DESCRIPTION, +PLUGIN_LICENSE, +PLUGIN_MATURITY, +PLUGIN_AUTH_VERSION +FROM INFORMATION_SCHEMA.PLUGINS +WHERE PLUGIN_TYPE='DATA TYPE' + AND PLUGIN_NAME='test_int8'; +PLUGIN_NAME test_int8 +PLUGIN_VERSION 1.0 +PLUGIN_STATUS ACTIVE +PLUGIN_TYPE DATA TYPE +PLUGIN_AUTHOR MariaDB Corporation +PLUGIN_DESCRIPTION Data type TEST_INT8 +PLUGIN_LICENSE GPL +PLUGIN_MATURITY Experimental +PLUGIN_AUTH_VERSION 1.0 +CREATE TABLE t1 (a TEST_INT8); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` test_int8(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TEST_INT8(4)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` test_int8(4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TEST_INT8(10)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` test_int8(10) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TEST_INT8(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` test_int8(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +SELECT CAST('100' AS TEST_INT8) AS cast; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def cast 3 3 3 N 32897 0 63 +cast +100 +BEGIN NOT ATOMIC +DECLARE a TEST_INT8 DEFAULT 256; +SELECT HEX(a), a; +END; +$$ +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def HEX(a) 253 40 3 Y 0 0 8 +def a a 8 20 3 Y 32768 0 63 +HEX(a) a +100 256 +CREATE FUNCTION f1(p TEST_INT8) RETURNS TEST_INT8 RETURN 1; +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `f1`(p TEST_INT8) RETURNS test_int8(20) +RETURN 1 latin1 latin1_swedish_ci latin1_swedish_ci +SELECT f1(10); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def f1(10) f1(10) 8 20 1 Y 32768 0 63 +f1(10) +1 +DROP FUNCTION f1; +CREATE TABLE t1 (a TEST_INT8); +CREATE TABLE t2 AS SELECT a FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` test_int8(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a TEST_INT8); +CREATE TABLE t2 AS SELECT COALESCE(a,a) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,a)` test_int8(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a TEST_INT8); +CREATE TABLE t2 AS SELECT LEAST(a,a) FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,a)` test_int8(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 AS SELECT MIN(a), MAX(a) FROM t1; +SELECT * FROM t2; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t2 t2 MIN(a) MIN(a) 8 20 1 Y 32768 0 63 +def test t2 t2 MAX(a) MAX(a) 8 20 1 Y 32768 0 63 +MIN(a) MAX(a) +1 2 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `MIN(a)` test_int8(20) DEFAULT NULL, + `MAX(a)` test_int8(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (id INT, a TEST_INT8); +INSERT INTO t1 VALUES (1,1),(1,2),(2,1),(2,2); +CREATE TABLE t2 AS SELECT id, MIN(a), MAX(a) FROM t1 GROUP BY id; +SELECT * FROM t2; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t2 t2 id id 3 11 1 Y 32768 0 63 +def test t2 t2 MIN(a) MIN(a) 8 20 1 Y 32768 0 63 +def test t2 t2 MAX(a) MAX(a) 8 20 1 Y 32768 0 63 +id MIN(a) MAX(a) +1 1 2 +2 1 2 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) DEFAULT NULL, + `MIN(a)` test_int8(20) DEFAULT NULL, + `MAX(a)` test_int8(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 AS SELECT DISTINCT a FROM t1; +SELECT * FROM t2; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t2 t2 a a 8 20 1 Y 32768 0 63 +a +1 +2 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` test_int8(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 AS SELECT (SELECT a FROM t1) AS c1; +SELECT * FROM t2; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t2 t2 c1 c1 8 20 1 Y 32768 0 63 +c1 +1 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` test_int8(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (a TEST_INT8); +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` test_int8(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +# Testing CREATE..LIKE +CREATE TABLE t1 (a TEST_INT8); +CREATE TABLE t2 LIKE t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` test_int8(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +# Testing CREATE..SELECT +CREATE TABLE t1 (a TEST_INT8); +CREATE TABLE t2 AS SELECT * FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` test_int8(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +# Testing ALTER +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (10),(20); +SELECT * FROM t1; +a +10 +20 +ALTER TABLE t1 MODIFY a INT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +a +10 +20 +ALTER TABLE t1 MODIFY a TEST_INT8; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` test_int8(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +a +10 +20 +DROP TABLE t1; +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (10),(20); +ALTER TABLE t1 ADD b TEST_INT8 DEFAULT 0; +SELECT * FROM t1; +a b +10 0 +20 0 +DROP TABLE t1; +# Testing metadata views +CREATE TABLE t1 (a TEST_INT8); +SELECT +TABLE_CATALOG, +TABLE_SCHEMA, +TABLE_NAME, +COLUMN_NAME, +ORDINAL_POSITION, +COLUMN_DEFAULT, +IS_NULLABLE, +DATA_TYPE, +CHARACTER_MAXIMUM_LENGTH, +CHARACTER_OCTET_LENGTH, +NUMERIC_PRECISION, +NUMERIC_SCALE, +DATETIME_PRECISION, +CHARACTER_SET_NAME, +COLLATION_NAME, +COLUMN_TYPE, +EXTRA +FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; +TABLE_CATALOG def +TABLE_SCHEMA test +TABLE_NAME t1 +COLUMN_NAME a +ORDINAL_POSITION 1 +COLUMN_DEFAULT NULL +IS_NULLABLE YES +DATA_TYPE test_int8 +CHARACTER_MAXIMUM_LENGTH NULL +CHARACTER_OCTET_LENGTH NULL +NUMERIC_PRECISION 19 +NUMERIC_SCALE 0 +DATETIME_PRECISION NULL +CHARACTER_SET_NAME NULL +COLLATION_NAME NULL +COLUMN_TYPE test_int8(20) +EXTRA +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +a test_int8(20) YES NULL +DROP TABLE t1; +# Testing indexing +CREATE TABLE t1 (a TEST_INT8, KEY(a)); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7); +EXPLAIN SELECT * FROM t1 WHERE a=3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 9 const 1 Using index +DROP TABLE t1; +# Testing aggregation for result +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7); +CREATE TABLE t2 AS SELECT +COALESCE(a,1) AS c1, +COALESCE(a,1.0) AS c2, +COALESCE(a,1e0) AS c3 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` test_int8(20) DEFAULT NULL, + `c2` decimal(20,1) DEFAULT NULL, + `c3` double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2 ORDER BY c1; +c1 c2 c3 +0 0.0 0 +1 1.0 1 +2 2.0 2 +3 3.0 3 +4 4.0 4 +5 5.0 5 +6 6.0 6 +7 7.0 7 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_INT8, +c_tinyint TINYINT, +c_smallint SMALLINT, +c_mediumint MEDIUMINT, +c_int INT, +c_bigint BIGINT +); +CREATE TABLE t2 AS SELECT +COALESCE(c,c_tinyint), +COALESCE(c,c_smallint), +COALESCE(c,c_mediumint), +COALESCE(c,c_bigint) +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(c,c_tinyint)` test_int8(20) DEFAULT NULL, + `COALESCE(c,c_smallint)` test_int8(20) DEFAULT NULL, + `COALESCE(c,c_mediumint)` test_int8(20) DEFAULT NULL, + `COALESCE(c,c_bigint)` test_int8(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_INT8, +c_time TIME, +c_date DATE, +c_datetime DATETIME, +c_timestamp TIMESTAMP +); +SELECT COALESCE(c, c_time) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and time for operation 'coalesce' +SELECT COALESCE(c, c_date) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and date for operation 'coalesce' +SELECT COALESCE(c, c_datetime) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and datetime for operation 'coalesce' +SELECT COALESCE(c, c_timestamp) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and timestamp for operation 'coalesce' +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_INT8, +c_char CHAR(32), +c_varchar VARCHAR(32), +c_tinytext TINYTEXT, +c_text TEXT, +c_mediumtext MEDIUMTEXT, +c_longtext LONGTEXT +); +SELECT COALESCE(c, c_char) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and char for operation 'coalesce' +SELECT COALESCE(c, c_varchar) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and varchar for operation 'coalesce' +SELECT COALESCE(c, c_tinytext) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and tinyblob for operation 'coalesce' +SELECT COALESCE(c, c_text) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and blob for operation 'coalesce' +SELECT COALESCE(c, c_mediumtext) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and mediumblob for operation 'coalesce' +SELECT COALESCE(c, c_longtext) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and longblob for operation 'coalesce' +DROP TABLE t1; +# Testing aggregation for min/max +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7); +CREATE TABLE t2 AS SELECT +LEAST(a,1) AS c1, +LEAST(a,1.0) AS c2, +LEAST(a,1e0) AS c3 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` test_int8(20) DEFAULT NULL, + `c2` decimal(20,1) DEFAULT NULL, + `c3` double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2 ORDER BY c1; +c1 c2 c3 +0 0.0 0 +1 1.0 1 +1 1.0 1 +1 1.0 1 +1 1.0 1 +1 1.0 1 +1 1.0 1 +1 1.0 1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_INT8, +c_tinyint TINYINT, +c_smallint SMALLINT, +c_mediumint MEDIUMINT, +c_int INT, +c_bigint BIGINT +); +CREATE TABLE t2 AS SELECT +LEAST(c,c_tinyint), +LEAST(c,c_smallint), +LEAST(c,c_mediumint), +LEAST(c,c_bigint) +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(c,c_tinyint)` test_int8(20) DEFAULT NULL, + `LEAST(c,c_smallint)` test_int8(20) DEFAULT NULL, + `LEAST(c,c_mediumint)` test_int8(20) DEFAULT NULL, + `LEAST(c,c_bigint)` test_int8(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_INT8, +c_time TIME, +c_date DATE, +c_datetime DATETIME, +c_timestamp TIMESTAMP +); +SELECT LEAST(c, c_time) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and time for operation 'least' +SELECT LEAST(c, c_date) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and date for operation 'least' +SELECT LEAST(c, c_datetime) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and datetime for operation 'least' +SELECT LEAST(c, c_timestamp) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and timestamp for operation 'least' +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_INT8, +c_char CHAR(32), +c_varchar VARCHAR(32), +c_tinytext TINYTEXT, +c_text TEXT, +c_mediumtext MEDIUMTEXT, +c_longtext LONGTEXT +); +SELECT LEAST(c, c_char) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and char for operation 'least' +SELECT LEAST(c, c_varchar) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and varchar for operation 'least' +SELECT LEAST(c, c_tinytext) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and tinyblob for operation 'least' +SELECT LEAST(c, c_text) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and blob for operation 'least' +SELECT LEAST(c, c_mediumtext) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and mediumblob for operation 'least' +SELECT LEAST(c, c_longtext) FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and longblob for operation 'least' +DROP TABLE t1; +# Testing aggregation for numeric operation - plus +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7); +CREATE TABLE t2 AS SELECT +a+1 AS c1, +a+1.0 AS c2, +a+1e0 AS c3 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` bigint(21) DEFAULT NULL, + `c2` decimal(21,1) DEFAULT NULL, + `c3` double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2 ORDER BY c1; +c1 c2 c3 +1 1.0 1 +2 2.0 2 +3 3.0 3 +4 4.0 4 +5 5.0 5 +6 6.0 6 +7 7.0 7 +8 8.0 8 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_INT8, +c_tinyint TINYINT, +c_smallint SMALLINT, +c_mediumint MEDIUMINT, +c_int INT, +c_bigint BIGINT +); +CREATE TABLE t2 AS SELECT +c + c_tinyint, +c + c_smallint, +c + c_mediumint, +c + c_bigint +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c + c_tinyint` bigint(21) DEFAULT NULL, + `c + c_smallint` bigint(21) DEFAULT NULL, + `c + c_mediumint` bigint(21) DEFAULT NULL, + `c + c_bigint` bigint(21) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_INT8, +c_time TIME, +c_date DATE, +c_datetime DATETIME, +c_timestamp TIMESTAMP +); +SELECT c + c_time FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and time for operation '+' +SELECT c + c_date FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and date for operation '+' +SELECT c + c_datetime FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and datetime for operation '+' +SELECT c + c_timestamp FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and timestamp for operation '+' +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_INT8, +c_char CHAR(32), +c_varchar VARCHAR(32), +c_tinytext TINYTEXT, +c_text TEXT, +c_mediumtext MEDIUMTEXT, +c_longtext LONGTEXT +); +SELECT c + c_char FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and char for operation '+' +SELECT c + c_varchar FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and varchar for operation '+' +SELECT c + c_tinytext FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and tinyblob for operation '+' +SELECT c + c_text FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and blob for operation '+' +SELECT c + c_mediumtext FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and mediumblob for operation '+' +SELECT c + c_longtext FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and longblob for operation '+' +DROP TABLE t1; +# Testing aggregation for numeric operation - minus +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); +CREATE TABLE t2 AS SELECT +a-1 AS c1, +a-1.0 AS c2, +a-1e0 AS c3 +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` bigint(21) DEFAULT NULL, + `c2` decimal(21,1) DEFAULT NULL, + `c3` double DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2 ORDER BY c1; +c1 c2 c3 +0 0.0 0 +1 1.0 1 +2 2.0 2 +3 3.0 3 +4 4.0 4 +5 5.0 5 +6 6.0 6 +7 7.0 7 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_INT8, +c_tinyint TINYINT, +c_smallint SMALLINT, +c_mediumint MEDIUMINT, +c_int INT, +c_bigint BIGINT +); +CREATE TABLE t2 AS SELECT +c - c_tinyint, +c - c_smallint, +c - c_mediumint, +c - c_bigint +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c - c_tinyint` bigint(21) DEFAULT NULL, + `c - c_smallint` bigint(21) DEFAULT NULL, + `c - c_mediumint` bigint(21) DEFAULT NULL, + `c - c_bigint` bigint(21) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_INT8, +c_time TIME, +c_date DATE, +c_datetime DATETIME, +c_timestamp TIMESTAMP +); +SELECT c - c_time FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and time for operation '-' +SELECT c - c_date FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and date for operation '-' +SELECT c - c_datetime FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and datetime for operation '-' +SELECT c - c_timestamp FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and timestamp for operation '-' +DROP TABLE t1; +CREATE TABLE t1 ( +c TEST_INT8, +c_char CHAR(32), +c_varchar VARCHAR(32), +c_tinytext TINYTEXT, +c_text TEXT, +c_mediumtext MEDIUMTEXT, +c_longtext LONGTEXT +); +SELECT c - c_char FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and char for operation '-' +SELECT c - c_varchar FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and varchar for operation '-' +SELECT c - c_tinytext FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and tinyblob for operation '-' +SELECT c - c_text FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and blob for operation '-' +SELECT c - c_mediumtext FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and mediumblob for operation '-' +SELECT c - c_longtext FROM t1; +ERROR HY000: Illegal parameter data types test_int8 and longblob for operation '-' +DROP TABLE t1; +# Testing CAST to other data types +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (20000102); +CREATE TABLE t2 AS SELECT +a, +CAST(a AS CHAR), +CAST(a AS DECIMAL), +CAST(a AS DOUBLE), +CAST(a AS SIGNED), +CAST(a AS UNSIGNED), +CAST(a AS TIME), +CAST(a AS DATETIME), +CAST(a AS DATE) +FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` test_int8(20) DEFAULT NULL, + `CAST(a AS CHAR)` varchar(20) DEFAULT NULL, + `CAST(a AS DECIMAL)` decimal(10,0) DEFAULT NULL, + `CAST(a AS DOUBLE)` double DEFAULT NULL, + `CAST(a AS SIGNED)` bigint(20) DEFAULT NULL, + `CAST(a AS UNSIGNED)` bigint(20) unsigned DEFAULT NULL, + `CAST(a AS TIME)` time DEFAULT NULL, + `CAST(a AS DATETIME)` datetime DEFAULT NULL, + `CAST(a AS DATE)` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2; +a 20000102 +CAST(a AS CHAR) 20000102 +CAST(a AS DECIMAL) 20000102 +CAST(a AS DOUBLE) 20000102 +CAST(a AS SIGNED) 20000102 +CAST(a AS UNSIGNED) 20000102 +CAST(a AS TIME) 00:00:00 +CAST(a AS DATETIME) 2000-01-02 00:00:00 +CAST(a AS DATE) 2000-01-02 +DROP TABLE t2; +DROP TABLE t1; diff --git a/plugin/type_test/mysql-test/type_test/type_test_int8.test b/plugin/type_test/mysql-test/type_test/type_test_int8.test new file mode 100644 index 00000000..6b5496c3 --- /dev/null +++ b/plugin/type_test/mysql-test/type_test/type_test_int8.test @@ -0,0 +1,518 @@ +--echo # +--echo # MDEV-20016 Add MariaDB_DATA_TYPE_PLUGIN +--echo # + +--vertical_results +SELECT + PLUGIN_NAME, + PLUGIN_VERSION, + PLUGIN_STATUS, + PLUGIN_TYPE, + PLUGIN_AUTHOR, + PLUGIN_DESCRIPTION, + PLUGIN_LICENSE, + PLUGIN_MATURITY, + PLUGIN_AUTH_VERSION +FROM INFORMATION_SCHEMA.PLUGINS + WHERE PLUGIN_TYPE='DATA TYPE' + AND PLUGIN_NAME='test_int8'; +--horizontal_results + +CREATE TABLE t1 (a TEST_INT8); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_INT8(4)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_INT8(10)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_INT8(20)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--disable_ps_protocol +--enable_metadata +SELECT CAST('100' AS TEST_INT8) AS cast; +--disable_metadata +--enable_ps_protocol + +--disable_ps_protocol +--enable_metadata +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE a TEST_INT8 DEFAULT 256; + SELECT HEX(a), a; +END; +$$ +DELIMITER ;$$ +--disable_metadata +--enable_ps_protocol + +CREATE FUNCTION f1(p TEST_INT8) RETURNS TEST_INT8 RETURN 1; +SHOW CREATE FUNCTION f1; +--disable_ps_protocol +--enable_metadata +SELECT f1(10); +--disable_metadata +--enable_ps_protocol +DROP FUNCTION f1; + +CREATE TABLE t1 (a TEST_INT8); +CREATE TABLE t2 AS SELECT a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_INT8); +CREATE TABLE t2 AS SELECT COALESCE(a,a) FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_INT8); +CREATE TABLE t2 AS SELECT LEAST(a,a) FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 AS SELECT MIN(a), MAX(a) FROM t1; +--disable_ps_protocol +--enable_metadata +SELECT * FROM t2; +--disable_metadata +--enable_ps_protocol +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (id INT, a TEST_INT8); +INSERT INTO t1 VALUES (1,1),(1,2),(2,1),(2,2); +CREATE TABLE t2 AS SELECT id, MIN(a), MAX(a) FROM t1 GROUP BY id; +--disable_ps_protocol +--enable_metadata +SELECT * FROM t2; +--disable_metadata +--enable_ps_protocol +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 AS SELECT DISTINCT a FROM t1; +--disable_ps_protocol +--enable_metadata +SELECT * FROM t2; +--disable_metadata +--enable_ps_protocol +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 AS SELECT (SELECT a FROM t1) AS c1; +--disable_ps_protocol +--enable_metadata +SELECT * FROM t2; +--disable_metadata +--enable_ps_protocol +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_INT8); +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +--echo # Testing CREATE..LIKE + +CREATE TABLE t1 (a TEST_INT8); +CREATE TABLE t2 LIKE t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +--echo # Testing CREATE..SELECT + +CREATE TABLE t1 (a TEST_INT8); +CREATE TABLE t2 AS SELECT * FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + + +--echo # Testing ALTER + +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (10),(20); +SELECT * FROM t1; +ALTER TABLE t1 MODIFY a INT; +SHOW CREATE TABLE t1; +SELECT * FROM t1; +ALTER TABLE t1 MODIFY a TEST_INT8; +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (10),(20); +ALTER TABLE t1 ADD b TEST_INT8 DEFAULT 0; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # Testing metadata views + +CREATE TABLE t1 (a TEST_INT8); +--vertical_results +SELECT + TABLE_CATALOG, + TABLE_SCHEMA, + TABLE_NAME, + COLUMN_NAME, + ORDINAL_POSITION, + COLUMN_DEFAULT, + IS_NULLABLE, + DATA_TYPE, + CHARACTER_MAXIMUM_LENGTH, + CHARACTER_OCTET_LENGTH, + NUMERIC_PRECISION, + NUMERIC_SCALE, + DATETIME_PRECISION, + CHARACTER_SET_NAME, + COLLATION_NAME, + COLUMN_TYPE, + EXTRA +FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; +--horizontal_results +SHOW COLUMNS FROM t1; +DROP TABLE t1; + + +--echo # Testing indexing + +CREATE TABLE t1 (a TEST_INT8, KEY(a)); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7); +EXPLAIN SELECT * FROM t1 WHERE a=3; +DROP TABLE t1; + +--echo # Testing aggregation for result + +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7); +CREATE TABLE t2 AS SELECT + COALESCE(a,1) AS c1, + COALESCE(a,1.0) AS c2, + COALESCE(a,1e0) AS c3 +FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2 ORDER BY c1; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_INT8, + c_tinyint TINYINT, + c_smallint SMALLINT, + c_mediumint MEDIUMINT, + c_int INT, + c_bigint BIGINT +); +CREATE TABLE t2 AS SELECT + COALESCE(c,c_tinyint), + COALESCE(c,c_smallint), + COALESCE(c,c_mediumint), + COALESCE(c,c_bigint) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_INT8, + c_time TIME, + c_date DATE, + c_datetime DATETIME, + c_timestamp TIMESTAMP +); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_time) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_date) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_datetime) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_timestamp) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_INT8, + c_char CHAR(32), + c_varchar VARCHAR(32), + c_tinytext TINYTEXT, + c_text TEXT, + c_mediumtext MEDIUMTEXT, + c_longtext LONGTEXT +); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_char) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_varchar) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_tinytext) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_text) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_mediumtext) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT COALESCE(c, c_longtext) FROM t1; +DROP TABLE t1; + +--echo # Testing aggregation for min/max + +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7); +CREATE TABLE t2 AS SELECT + LEAST(a,1) AS c1, + LEAST(a,1.0) AS c2, + LEAST(a,1e0) AS c3 +FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2 ORDER BY c1; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_INT8, + c_tinyint TINYINT, + c_smallint SMALLINT, + c_mediumint MEDIUMINT, + c_int INT, + c_bigint BIGINT +); +CREATE TABLE t2 AS SELECT + LEAST(c,c_tinyint), + LEAST(c,c_smallint), + LEAST(c,c_mediumint), + LEAST(c,c_bigint) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_INT8, + c_time TIME, + c_date DATE, + c_datetime DATETIME, + c_timestamp TIMESTAMP +); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_time) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_date) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_datetime) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_timestamp) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_INT8, + c_char CHAR(32), + c_varchar VARCHAR(32), + c_tinytext TINYTEXT, + c_text TEXT, + c_mediumtext MEDIUMTEXT, + c_longtext LONGTEXT +); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_char) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_varchar) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_tinytext) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_text) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_mediumtext) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT LEAST(c, c_longtext) FROM t1; +DROP TABLE t1; + + +--echo # Testing aggregation for numeric operation - plus + +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7); +CREATE TABLE t2 AS SELECT + a+1 AS c1, + a+1.0 AS c2, + a+1e0 AS c3 +FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2 ORDER BY c1; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_INT8, + c_tinyint TINYINT, + c_smallint SMALLINT, + c_mediumint MEDIUMINT, + c_int INT, + c_bigint BIGINT +); +CREATE TABLE t2 AS SELECT + c + c_tinyint, + c + c_smallint, + c + c_mediumint, + c + c_bigint +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_INT8, + c_time TIME, + c_date DATE, + c_datetime DATETIME, + c_timestamp TIMESTAMP +); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_time FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_date FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_datetime FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_timestamp FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_INT8, + c_char CHAR(32), + c_varchar VARCHAR(32), + c_tinytext TINYTEXT, + c_text TEXT, + c_mediumtext MEDIUMTEXT, + c_longtext LONGTEXT +); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_char FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_varchar FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_tinytext FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_text FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_mediumtext FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c + c_longtext FROM t1; +DROP TABLE t1; + +--echo # Testing aggregation for numeric operation - minus + +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); +CREATE TABLE t2 AS SELECT + a-1 AS c1, + a-1.0 AS c2, + a-1e0 AS c3 +FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2 ORDER BY c1; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_INT8, + c_tinyint TINYINT, + c_smallint SMALLINT, + c_mediumint MEDIUMINT, + c_int INT, + c_bigint BIGINT +); +CREATE TABLE t2 AS SELECT + c - c_tinyint, + c - c_smallint, + c - c_mediumint, + c - c_bigint +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_INT8, + c_time TIME, + c_date DATE, + c_datetime DATETIME, + c_timestamp TIMESTAMP +); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_time FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_date FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_datetime FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_timestamp FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + c TEST_INT8, + c_char CHAR(32), + c_varchar VARCHAR(32), + c_tinytext TINYTEXT, + c_text TEXT, + c_mediumtext MEDIUMTEXT, + c_longtext LONGTEXT +); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_char FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_varchar FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_tinytext FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_text FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_mediumtext FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT c - c_longtext FROM t1; +DROP TABLE t1; + + +--echo # Testing CAST to other data types + +CREATE TABLE t1 (a TEST_INT8); +INSERT INTO t1 VALUES (20000102); +CREATE TABLE t2 AS SELECT + a, + CAST(a AS CHAR), + CAST(a AS DECIMAL), + CAST(a AS DOUBLE), + CAST(a AS SIGNED), + CAST(a AS UNSIGNED), + CAST(a AS TIME), + CAST(a AS DATETIME), + CAST(a AS DATE) +FROM t1; +SHOW CREATE TABLE t2; +--vertical_results +SELECT * FROM t2; +--horizontal_results +DROP TABLE t2; +DROP TABLE t1; diff --git a/plugin/type_test/mysql-test/type_test/type_test_mysql.result b/plugin/type_test/mysql-test/type_test/type_test_mysql.result new file mode 100644 index 00000000..402e8265 --- /dev/null +++ b/plugin/type_test/mysql-test/type_test/type_test_mysql.result @@ -0,0 +1,27 @@ +CREATE TABLE t1 (a TEST_INT8, b TEST_DOUBLE); +Field 1: `a` +Catalog: `def` +Database: `test` +Table: `t1` +Org_table: `t1` +Type: LONGLONG +Collation: binary (63) +Length: 20 +Max_length: 0 +Decimals: 0 +Flags: NUM + +Field 2: `b` +Catalog: `def` +Database: `test` +Table: `t1` +Org_table: `t1` +Type: DOUBLE +Collation: binary (63) +Length: 22 +Max_length: 0 +Decimals: 31 +Flags: NUM + + +DROP TABLE t1; diff --git a/plugin/type_test/mysql-test/type_test/type_test_mysql.test b/plugin/type_test/mysql-test/type_test/type_test_mysql.test new file mode 100644 index 00000000..93a0e08e --- /dev/null +++ b/plugin/type_test/mysql-test/type_test/type_test_mysql.test @@ -0,0 +1,10 @@ +-- source include/have_working_dns.inc +-- source include/not_embedded.inc + +CREATE TABLE t1 (a TEST_INT8, b TEST_DOUBLE); +# +# doesn't report the correct type name yet +# to be fixed +# +--exec $MYSQL -t test --column-type-info -e "SELECT * FROM t1" 2>&1 +DROP TABLE t1; diff --git a/plugin/type_test/plugin.cc b/plugin/type_test/plugin.cc new file mode 100644 index 00000000..4c26c35f --- /dev/null +++ b/plugin/type_test/plugin.cc @@ -0,0 +1,322 @@ +/* + Copyright (c) 2019, MariaDB Corporation + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */ + +#include <my_global.h> +#include <sql_class.h> // THD +#include <mysql/plugin_data_type.h> +#include "sql_type.h" + + +class Type_collection_test: public Type_collection +{ +protected: + const Type_handler *aggregate_common(const Type_handler *h1, + const Type_handler *h2) const; +public: + const Type_handler *handler_by_name(const LEX_CSTRING &name) const override + { + return NULL; + } + const Type_handler *aggregate_for_result(const Type_handler *h1, + const Type_handler *h2) + const override; + const Type_handler *aggregate_for_comparison(const Type_handler *h1, + const Type_handler *h2) + const override; + const Type_handler *aggregate_for_min_max(const Type_handler *h1, + const Type_handler *h2) + const override; + const Type_handler *aggregate_for_num_op(const Type_handler *h1, + const Type_handler *h2) + const override; +}; + + +static Type_collection_test type_collection_test; + + +class Field_test_int8 :public Field_longlong +{ +public: + Field_test_int8(const LEX_CSTRING &name, const Record_addr &addr, + enum utype unireg_check_arg, + uint32 len_arg, bool zero_arg, bool unsigned_arg) + :Field_longlong(addr.ptr(), len_arg, addr.null_ptr(), addr.null_bit(), + Field::NONE, &name, zero_arg, unsigned_arg) + {} + const Type_handler *type_handler() const override; +}; + + +class Type_handler_test_int8: public Type_handler_longlong +{ +public: + const Type_collection *type_collection() const override + { + return &type_collection_test; + } + const Type_handler *type_handler_signed() const override + { + return this; + } + Field *make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const override + { + return new (root) + Field_test_int8(*name, rec, attr->unireg_check, + (uint32) attr->length, + f_is_zerofill(attr->pack_flag) != 0, + f_is_dec(attr->pack_flag) == 0); + } +}; + +static Type_handler_test_int8 type_handler_test_int8; + + +const Type_handler *Field_test_int8::type_handler() const +{ + return &type_handler_test_int8; +} + + +static struct st_mariadb_data_type plugin_descriptor_type_test_int8= +{ + MariaDB_DATA_TYPE_INTERFACE_VERSION, + &type_handler_test_int8 +}; + + +/*************************************************************************/ + +class Field_test_double :public Field_double +{ +public: + Field_test_double(const LEX_CSTRING &name, const Record_addr &addr, + enum utype unireg_check_arg, + uint32 len_arg, uint8 dec_arg, + bool zero_arg, bool unsigned_arg) + :Field_double(addr.ptr(), len_arg, addr.null_ptr(), addr.null_bit(), + Field::NONE, &name, dec_arg, zero_arg, unsigned_arg) + {} + const Type_handler *type_handler() const override; +}; + + +class Type_handler_test_double: public Type_handler_double +{ +public: + const Type_collection *type_collection() const override + { + return &type_collection_test; + } + const Type_handler *type_handler_signed() const override + { + return this; + } + bool Column_definition_data_type_info_image(Binary_string *to, + const Column_definition &def) + const override + { + return to->append(Type_handler_test_double::name().lex_cstring()); + } + Field *make_table_field_from_def(TABLE_SHARE *share, MEM_ROOT *root, + const LEX_CSTRING *name, + const Record_addr &rec, const Bit_addr &bit, + const Column_definition_attributes *attr, + uint32 flags) const override + { + return new (root) + Field_test_double(*name, rec, attr->unireg_check, + (uint32) attr->length, (uint8) attr->decimals, + f_is_zerofill(attr->pack_flag) != 0, + f_is_dec(attr->pack_flag) == 0); + } +}; + +static Type_handler_test_double type_handler_test_double; + + +const Type_handler *Field_test_double::type_handler() const +{ + return &type_handler_test_double; +} + + +static struct st_mariadb_data_type plugin_descriptor_type_test_double= +{ + MariaDB_DATA_TYPE_INTERFACE_VERSION, + &type_handler_test_double +}; + + +/*************************************************************************/ +const Type_handler * +Type_collection_test::aggregate_common(const Type_handler *h1, + const Type_handler *h2) const +{ + if (h1 == h2) + return h1; + + static const Type_aggregator::Pair agg[]= + { + { + &type_handler_slong, + &type_handler_test_double, + &type_handler_test_double + }, + { + &type_handler_newdecimal, + &type_handler_test_double, + &type_handler_test_double + }, + { + &type_handler_double, + &type_handler_test_double, + &type_handler_test_double + }, + { + &type_handler_slong, + &type_handler_test_int8, + &type_handler_test_int8 + }, + { + &type_handler_newdecimal, + &type_handler_test_int8, + &type_handler_newdecimal + }, + { + &type_handler_double, + &type_handler_test_int8, + &type_handler_double + }, + { + &type_handler_stiny, + &type_handler_test_double, + &type_handler_test_double + }, + { + &type_handler_sshort, + &type_handler_test_double, + &type_handler_test_double + }, + { + &type_handler_sint24, + &type_handler_test_double, + &type_handler_test_double + }, + { + &type_handler_slonglong, + &type_handler_test_double, + &type_handler_test_double + }, + { + &type_handler_stiny, + &type_handler_test_int8, + &type_handler_test_int8 + }, + { + &type_handler_sshort, + &type_handler_test_int8, + &type_handler_test_int8 + }, + { + &type_handler_sint24, + &type_handler_test_int8, + &type_handler_test_int8 + }, + { + &type_handler_slonglong, + &type_handler_test_int8, + &type_handler_test_int8 + }, + {NULL,NULL,NULL} + }; + + return Type_aggregator::find_handler_in_array(agg, h1, h2, true); +} + + +const Type_handler * +Type_collection_test::aggregate_for_result(const Type_handler *h1, + const Type_handler *h2) const +{ + return aggregate_common(h1, h2); +} + + +const Type_handler * +Type_collection_test::aggregate_for_min_max(const Type_handler *h1, + const Type_handler *h2) const +{ + return aggregate_common(h1, h2); +} + + +const Type_handler * +Type_collection_test::aggregate_for_num_op(const Type_handler *h1, + const Type_handler *h2) const +{ + return aggregate_common(h1, h2); +} + + +const Type_handler * +Type_collection_test::aggregate_for_comparison(const Type_handler *h1, + const Type_handler *h2) const +{ + DBUG_ASSERT(h1 == h1->type_handler_for_comparison()); + DBUG_ASSERT(h2 == h2->type_handler_for_comparison()); + return aggregate_common(h1, h2); +} + + +/*************************************************************************/ + +maria_declare_plugin(type_test) +{ + MariaDB_DATA_TYPE_PLUGIN, // the plugin type (see include/mysql/plugin.h) + &plugin_descriptor_type_test_int8, // pointer to type-specific plugin descriptor + "test_int8", // plugin name + "MariaDB Corporation", // plugin author + "Data type TEST_INT8", // the plugin description + PLUGIN_LICENSE_GPL, // the plugin license (see include/mysql/plugin.h) + 0, // Pointer to plugin initialization function + 0, // Pointer to plugin deinitialization function + 0x0100, // Numeric version 0xAABB means AA.BB version + NULL, // Status variables + NULL, // System variables + "1.0", // String version representation + MariaDB_PLUGIN_MATURITY_EXPERIMENTAL // Maturity(see include/mysql/plugin.h)*/ +}, +{ + MariaDB_DATA_TYPE_PLUGIN, // the plugin type (see include/mysql/plugin.h) + &plugin_descriptor_type_test_double, // pointer to type-specific plugin descriptor + "test_double", // plugin name + "MariaDB Corporation", // plugin author + "Data type TEST_DOUBLE", // the plugin description + PLUGIN_LICENSE_GPL, // the plugin license (see include/mysql/plugin.h) + 0, // Pointer to plugin initialization function + 0, // Pointer to plugin deinitialization function + 0x0100, // Numeric version 0xAABB means AA.BB version + NULL, // Status variables + NULL, // System variables + "1.0", // String version representation + MariaDB_PLUGIN_MATURITY_EXPERIMENTAL // Maturity(see include/mysql/plugin.h)*/ +} +maria_declare_plugin_end; |