From 755ae21b826d6e644560b0ad397eb46cd7191369 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 18 May 2007 12:44:03 +0200 Subject: Bug#26277 User variable returns one type in SELECT @v and other for CREATE as SELECT @v - Adding variable m_cached_result_type to keep the variable type consistent during the execution of a statement. - Before each result set is returned to the client the description of each column is sent as meta data. Previously the result type for a column could change if the hash variable entry changed between statements. This caused the result set of the query to alternate column types in certain cases which is not supported by MySQL client-server protocol. Example: Previously this sequence: SET @a:=1; SELECT @a:="text", @a; would return "text", "text"; After the change the SELECT returns "text", 0 The reson for this is that previously the result set from 'SELECT @a;' would always be of the type STRING, whereas now the type of the variable is taken from the last SET statement. However, 'SELECT @a:="text"' will return type of STRING since the right side of the assignment is used. mysql-test/r/ps_2myisam.result: Changed test result because SQL type of a user variable now more accurately represents its Item type: since Item type of a variable can be either STRING, INT, DECIMAL or DOUBLE, SQL type of the result set metadata now can be either MYSQL_TYPE_VARCHAR, MYSQL_TYPE_LONGLONG, MYSQL_TYPE_NEWDECIMAL or MYSQL_TYPE_DOUBLE. Previously it was always MYSQL_TYPE_VARCHAR. In particular, integer variables now have changed from MYSQL_TYPE_VARCHAR to MYSQL_TYPE_LONGLONG. mysql-test/r/ps_3innodb.result: Changed test result because SQL type of a user variable now more accurately represents its Item type: since Item type of a variable can be either STRING, INT, DECIMAL or DOUBLE, SQL type of the result set metadata now can be either MYSQL_TYPE_VARCHAR, MYSQL_TYPE_LONGLONG, MYSQL_TYPE_NEWDECIMAL or MYSQL_TYPE_DOUBLE. Previously it was always MYSQL_TYPE_VARCHAR. In particular, integer variables now have changed from MYSQL_TYPE_VARCHAR to MYSQL_TYPE_LONGLONG. mysql-test/r/ps_4heap.result: Changed test result because SQL type of a user variable now more accurately represents its Item type: since Item type of a variable can be either STRING, INT, DECIMAL or DOUBLE, SQL type of the result set metadata now can be either MYSQL_TYPE_VARCHAR, MYSQL_TYPE_LONGLONG, MYSQL_TYPE_NEWDECIMAL or MYSQL_TYPE_DOUBLE. Previously it was always MYSQL_TYPE_VARCHAR. In particular, integer variables now have changed from MYSQL_TYPE_VARCHAR to MYSQL_TYPE_LONGLONG. mysql-test/r/ps_5merge.result: Changed test result because SQL type of a user variable now more accurately represents its Item type: since Item type of a variable can be either STRING, INT, DECIMAL or DOUBLE, SQL type of the result set metadata now can be either MYSQL_TYPE_VARCHAR, MYSQL_TYPE_LONGLONG, MYSQL_TYPE_NEWDECIMAL or MYSQL_TYPE_DOUBLE. Previously it was always MYSQL_TYPE_VARCHAR. In particular, integer variables now have changed from MYSQL_TYPE_VARCHAR to MYSQL_TYPE_LONGLONG. mysql-test/r/ps_7ndb.result: Changed test result because SQL type of a user variable now more accurately represents its Item type: since Item type of a variable can be either STRING, INT, DECIMAL or DOUBLE, SQL type of the result set metadata now can be either MYSQL_TYPE_VARCHAR, MYSQL_TYPE_LONGLONG, MYSQL_TYPE_NEWDECIMAL or MYSQL_TYPE_DOUBLE. Previously it was always MYSQL_TYPE_VARCHAR. In particular, integer variables now have changed from MYSQL_TYPE_VARCHAR to MYSQL_TYPE_LONGLONG. mysql-test/r/sp-vars.result: Added test case. Previously variables could change their variable type during the execution of a statement. Which variable type to use in the statement is specified in any previous statement. mysql-test/r/type_date.result: This test case result is changed because it is no longer allowed for user variables to change their variable type during the execution of a statement. The determination of which variable type to use in the statement is specified in any previous statement. mysql-test/r/user_var.result: This test case result is changed because it is no longer allowed for user variables to change their variable type during the execution of a statement. The determination of which variable type to use in the statement is specified in any previous statement. mysql-test/t/sp-vars.test: Added test case. Previously variables could change their variable type during the execution of a statement. Which variable type to use in the statement is specified in any previous statement. mysql-test/t/type_date.test: This test case result is changed because it is no longer allowed for user variables to change their variable type during the execution of a statement. The determination of which variable type to use in the statement is specified in any previous statement. sql/item_func.cc: Adding variable m_cached_result_type to keep the variable type consistent during the execution of a statement. Previously the result type could change if the hash variable entry changed between statements. This caused the result set of the query to alternate column types in certain cases. sql/item_func.h: Adding variable m_cached_result_type to keep the variable type consistent during the execution of a statement. Previously the result type could change if the hash variable entry changed between statements. This caused the result set of the query to alternate column types in certain cases. --- mysql-test/t/sp-vars.test | 36 ++++++++++++++++++++++++++++++++++++ mysql-test/t/type_date.test | 9 ++++++--- 2 files changed, 42 insertions(+), 3 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/sp-vars.test b/mysql-test/t/sp-vars.test index 0014dc1f6af..9f59c7fb680 100644 --- a/mysql-test/t/sp-vars.test +++ b/mysql-test/t/sp-vars.test @@ -1368,3 +1368,39 @@ CALL p1(); DROP PROCEDURE p1; # End of 5.0 tests. + +# +# Bug #26277 User variable returns one type in SELECT @v and other for CREATE as SELECT @v +# +--disable_warnings +drop function if exists f1; +drop table if exists t1; +--enable_warnings + +delimiter |; +create function f1() returns int +begin + if @a=1 then set @b='abc'; + else set @b=1; + end if; + set @a=1; + return 0; +end| + +create table t1 (a int)| +insert into t1 (a) values (1), (2)| + +set @b=1| +set @a=0| +select f1(), @b from t1| + +set @b:='test'| +set @a=0| +select f1(), @b from t1| + +delimiter ;| + +drop function f1; +drop table t1; +# End of 5.1 tests. + diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index 02cd07e3c16..dcee4fd2ffc 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -128,9 +128,12 @@ drop table t1; # Bug #23093: Implicit conversion of 9912101 to date does not match # cast(9912101 as date) # -select @d:=1111, year(@d), month(@d), day(@d), cast(@d as date); -select @d:=011111, year(@d), month(@d), day(@d), cast(@d as date); -select @d:=1311, year(@d), month(@d), day(@d), cast(@d as date); +select @d:=1111; +select year(@d), month(@d), day(@d), cast(@d as date); +select @d:=011111; +select year(@d), month(@d), day(@d), cast(@d as date); +select @d:=1311; +select year(@d), month(@d), day(@d), cast(@d as date); create table t1 (d date , dt datetime , ts timestamp); insert into t1 values (9912101,9912101,9912101); insert into t1 values (11111,11111,11111); -- cgit v1.2.1