From 18ad176809999275ac7e86a586d41c8c9141f4a4 Mon Sep 17 00:00:00 2001 From: halfspawn Date: Thu, 4 May 2017 15:57:19 +0200 Subject: MDEV-12685 Oracle-compatible function CHR() --- mysql-test/t/func_str.test | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index bb3251b3e31..29a9510db00 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1853,6 +1853,16 @@ SELECT f1,HEX(f2) FROM t1 WHERE f1='YQ==' AND (f2= from_base64( SELECT f1,HEX(f2) FROM t1 WHERE f1='YQ==' AND (f2= from_base64("Yq==") OR f2= from_base64("YQ==")); DROP TABLE t1; +--echo # +--echo # MDEV-12685 Oracle-compatible function CHR() +--echo # +select chr(65); +create database mysqltest1 CHARACTER SET = 'utf8' COLLATE = 'utf8_bin'; +use mysqltest1; +select charset(chr(65)), length(chr(65)),char_length(chr(65)); +select charset(chr(14844588)), length(chr(14844588)),char_length(chr(14844588)); +drop database mysqltest1; +use test; --echo # --echo # End of 10.1 tests -- cgit v1.2.1 From 7c44b8afb781c533898dfb7321dd5cbb88c32ce0 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Mon, 15 May 2017 14:58:05 +0400 Subject: MDEV-12798 Item_param does not preserve exact field type in EXECUTE IMMEDIATE 'CREATE TABLE AS SELECT ?' USING POINT(1,1) --- mysql-test/t/gis.test | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 34797337b1d..ab9c792d523 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -2293,6 +2293,19 @@ CALL p1('multipolygon'); CALL p1('geometrycollection'); DROP PROCEDURE p1; +--echo # +--echo # MDEV-12798 Item_param does not preserve exact field type in EXECUTE IMMEDIATE 'CREATE TABLE AS SELECT ?' USING POINT(1,1) +--echo # +EXECUTE IMMEDIATE 'CREATE TABLE t1 AS SELECT ?' USING POINT(1,1); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +PREPARE stmt FROM 'CREATE OR REPLACE TABLE t1 AS SELECT ?'; +EXECUTE stmt USING POINT(1,1); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + --echo # --echo # End of 10.3 tests --echo # -- cgit v1.2.1 From 705fc43eaafccd7a41e541f3149a917850f4e2fb Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Wed, 10 May 2017 15:29:48 +0400 Subject: MDEV-12775 Reuse data type aggregation code for hybrid functions and UNION Introducing a new class Type_holder (used internally in sql_union.cc), to reuse exactly the same data type attribute aggregation Type_handler API for hybrid functions and UNION. This fixes a number of bugs in UNION: - MDEV-9495 Wrong field type for a UNION of a signed and an unsigned INT expression - MDEV-9497 UNION and COALESCE produce different field types for DECIMAL+INT - MDEV-12594 UNION between fixed length double columns does not always preserve scale - MDEV-12595 UNION converts INT to BIGINT - MDEV-12599 UNION is not symmetric when mixing INT and CHAR Details: - sql_union.cc: Reusing attribute aggregation for UNION. Adding new methods: * st_select_lex_unit::join_union_type_handlers() * st_select_lex_unit::join_union_type_attributes() * st_select_lex_unit::join_union_item_types() Removing the old join_types()-based code. - Changing Type_handler::Item_hybrid_func_fix_attributes() to accept "name", Type_handler_hybrid_field_type, Type_all_attributes as three separate parameters instead of a single Item_hybrid_func parameter, to make it possible to pass both Item_hybrid_func and Type_holder. - Moving the former special GEOMETRY and ENUM/SET attribute aggregation code from Item_type_holder::join_types() to * Type_handler_typelib::Item_hybrid_func_fix_attributes(). * Type_handler_geometry::Item_hybrid_func_fix_attrubutes(). This makes GEOMETRY/ENUM/SET symmetric with all other data types (from the UNION point of view). Removing Item_type_holder::join_types() and Item_type_holder::get_full_info(). - Adding new methods into Type_all_attributes: * Type_all_attributes::set_geometry_type() and Item_hybrid_func::set_geometry_type(). * Adding Type_all_attributes::get_typelib(). * Adding Type_all_attributes::set_typelib(). - Adding Type_handler_typelib as a common parent for Type_handler_enum and Type_handler_set, to avoid code duplication: they have already had two common methods, and we're adding one more shared method. - Adding Type_all_attributes::set_maybe_null(), as some type handlers may want to set maybe_null (e.g. Type_handler_geometry) during data type attribute aggregation. - Changing Type_geometry_attributes() to accept Type_handler and Type_all_attributes as two separate parameters, instead of a single Item parameter, to make it possible to pass Type_holder. - Adding Item_args::add_argument(). - Moving Item_args::alloc_arguments() from "protected" to "public". - Moving Item_type_holder::Item_type_holder() from item.cc to item.h, as now it's very simple. Btw, this constructor should probably be eventually removed. It's now used only in sql_show.cc, which could be modified to use Item_return_decimal (for symmetry with Item_return_xxx created for all other data types). Or, another option: remove all Item_return_xxx and use Item_type_holder for all data types instead. - storage/tokudb/mysql-test/tokudb/r/type_float.result Recording new results (MDEV-12594). - mysql-test/r/cte_recursive.result Recording new results (MDEV-9497) - mysql-test/r/subselect*.result Recording new results (MDEV-12595) - mysql-test/r/metadata.result Recording new results (MDEV-9495) - mysql-test/r/temp_table.result Recording new results (MDEV-12594) - mysql-test/r/type_float.result Recording new results (MDEV-12594) --- mysql-test/t/union.test | 127 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 127 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 04ab71588be..ce8b2bc9c2a 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -1544,6 +1544,133 @@ CREATE OR REPLACE TABLE t1 AS SELECT * FROM (SELECT 1 UNION SELECT 1) AS t0; SHOW CREATE TABLE t1; DROP TABLE t1; +--echo # +--echo # MDEV-9495 Wrong field type for a UNION of a signed and an unsigned INT expression +--echo # +CREATE TABLE t1 (a INT, b INT UNSIGNED); +INSERT INTO t1 VALUES (0x7FFFFFFF,0xFFFFFFFF); +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2 ORDER BY a; +DROP TABLE t2; +CREATE TABLE t2 AS SELECT COALESCE(a,b), COALESCE(b,a) FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; +DROP TABLE t1; + +--echo # +--echo # MDEV-9497 UNION and COALESCE produce different field types for DECIMAL+INT +--echo # +CREATE TABLE t1 AS SELECT COALESCE(10.1,CAST(10 AS UNSIGNED)) AS a; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 AS SELECT 10.1 AS a UNION SELECT CAST(10 AS UNSIGNED); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-12594 UNION between fixed length double columns does not always preserve scale +--echo # +CREATE TABLE t1 (a FLOAT(20,4), b FLOAT(20,3), c FLOAT(20,4)); +INSERT INTO t1 VALUES (1111,2222,3333); + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE OR REPLACE TABLE t2 SELECT a FROM t1 UNION SELECT c FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE OR REPLACE TABLE t2 SELECT b FROM t1 UNION SELECT b FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE OR REPLACE TABLE t2 SELECT c FROM t1 UNION SELECT c FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE OR REPLACE TABLE t2 SELECT c FROM t1 UNION SELECT a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE OR REPLACE TABLE t2 AS SELECT b FROM t1 UNION SELECT a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +DROP TABLE t1; + +--echo # Corner case +CREATE TABLE t1 (a FLOAT(255,4), b FLOAT(255,3)); +INSERT INTO t1 VALUES (1111,2222); +CREATE OR REPLACE TABLE t2 AS SELECT b FROM t1 UNION SELECT a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + + +--echo # +--echo # MDEV-12595 UNION converts INT to BIGINT +--echo # +CREATE TABLE t1 AS SELECT + 1, + -1, + COALESCE(1,1), + COALESCE(-1,-1), + COALESCE(1,-1), + COALESCE(-1,1); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 AS SELECT 1 AS c1,1 AS c2,-1 AS c3,-1 AS c4 UNION SELECT 1,-1,1,-1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # +--echo # MDEV-12599 UNION is not symmetric when mixing INT and CHAR +--echo # + +CREATE OR REPLACE TABLE t1 AS SELECT 1 AS c1, 'a' AS c2 UNION SELECT 'a', 1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 AS SELECT 11112222 AS c1, 'a' AS c2 UNION SELECT 'a', 11112222; +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +CREATE OR REPLACE TABLE t1 AS SELECT 111122223333 AS c1, 'a' AS c2 UNION SELECT 'a', 111122223333; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 AS SELECT 1111222233334444 AS c1, 'a' AS c2 UNION SELECT 'a', 1111222233334444; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INT(3), b VARCHAR(1)); +CREATE TABLE t2 AS SELECT a,b FROM t1 UNION SELECT b,a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a BIGINT(3), b VARCHAR(1)); +CREATE TABLE t2 AS SELECT a,b FROM t1 UNION SELECT b,a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1 (a BIGINT(12), b VARCHAR(1)); +CREATE TABLE t2 AS SELECT a,b FROM t1 UNION SELECT b,a FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + + --echo # --echo # End of 10.3 tests --echo # -- cgit v1.2.1