| Bug #63959 | Repeated query execution gives different column types (user variable) | ||
|---|---|---|---|
| Submitted: | 7 Jan 2012 18:21 | Modified: | 13 Aug 2012 13:31 |
| Reporter: | Mike Pomraning | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.1.61, 5.5.19 | OS: | Any (Linux, Mac OS X) |
| Assigned to: | CPU Architecture: | Any | |
[7 Jan 2012 18:24]
Valeriy Kravchuk
Thank you for the bug report. Verified just as described with 5.1.61 on Mac OS X also:
macbook-pro:5.1 openxs$ bin/mysql -uroot --column-type-info test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.61-debug Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT uservar FROM (SELECT @u AS uservar FROM (select @u := CAST(0
-> AS UNSIGNED) AS ignored) initializer) d;
Field 1: `uservar`
Catalog: `def`
Database: ``
Table: `d`
Org_table: ``
Type: BLOB
Collation: binary (63)
Length: 16777215
Max_length: 1
Decimals: 0
Flags: BLOB BINARY
+---------+
| uservar |
+---------+
| 0 |
+---------+
1 row in set (0.04 sec)
mysql> SELECT uservar FROM (SELECT @u AS uservar FROM (select @u := CAST(0 AS UNSIGNED) AS ignored) initializer) d;
Field 1: `uservar`
Catalog: `def`
Database: ``
Table: `d`
Org_table: ``
Type: LONGLONG
Collation: binary (63)
Length: 20
Max_length: 1
Decimals: 0
Flags: UNSIGNED NUM
+---------+
| uservar |
+---------+
| 0 |
+---------+
1 row in set (0.00 sec)
[13 Aug 2012 13:31]
Guilhem Bichot
SELECT uservar
FROM (SELECT @u AS uservar
FROM (select @u := CAST(0 AS UNSIGNED) AS ignored) initializer) d;
When query starts, @u does not exist. So, as
http://dev.mysql.com/doc/refman/5.6/en/user-variables.html
says, "If you refer to a variable that has not been initialized, it has a
value of NULL and a type of string. ".
Thus, when parser sees 'SELECT @u AS uservar', it checks the type of @u:
string; thus column 'uservar' will be string.
Query is executed, @u gets defined to a number (0).
When query starts for second time, @u is as the previous execution left it: a
number.
This is exactly what's documented in
http://dev.mysql.com/doc/refman/5.6/en/user-variables.html :
"Another issue with assigning a value to a variable and reading the value
within the same statement is that the default result type of a variable is
based on its type at the start of the statement. The following example
illustrates this:
mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;
For this SELECT statement, MySQL reports to the client that column one is a
string and converts all accesses of @a to strings, even though @a is set to a
number for the second row. After the SELECT statement executes, @a is
regarded as a number for the next statement.
To avoid problems with this behavior, either do not assign a value to and
read the value of the same variable within a single statement, or else set
the variable to 0, 0.0, or '' to define its type before you use it. "
So it works as documented. Not a bug.
Description: == Overview == In a session, the very first execution of certain queries will interpret a column derived from a numeric user variable as `mediumtext`, rather than as a numeric column. **All subsequent executions in a session of the very same query, referencing the same user variable, will interpret the variable as a numeric type.** Affects at least 5.5.19 and 5.5.17. == Example == Run these under `mysql --column-type-info` in the same session: -- `uservar` is type `mediumtext` SELECT uservar FROM (SELECT @u AS uservar FROM (select @u := CAST(0 AS UNSIGNED) AS ignored) initializer) d; -- hereafter it is type `bigint` SELECT uservar FROM (SELECT @u AS uservar FROM (select @u := CAST(0 AS UNSIGNED) AS ignored) initializer) d; Now disconnect and reconnect and observe the same behavior. Change `@u` to some other variable not yet used in the session, and observe the same behavior: first execution in the above query is BLOB-ish, subsequent executions are numeric. == Impact == The exact same SELECT produces differently typed selections, and perhaps even different result sets since different types sort differently. For example, certain complex queries emulating ROW_NUMBER()/RANK() with user variables will appear to sort incorrectly, but only the very first time they are run in a session. (Is it sorted 1, 2, 10 or '1', '10', '2' ?) This is how I discovered this issue. Also, tables created from certain queries (CREATE TABLE t AS SELECT ...) will have an *unpredictable column type* if user doesn't know whether the query has been issued in this session or not. How to repeat: -- -- On my system: -- -- $ mysql -N < path/to/this/sql -- MySQL 5.5.19 -- 1st run: t.uservar is type `mediumtext` -- 2nd run: t.uservar is type `bigint` -- SELECT CONCAT('MySQL ', VERSION()); -- Initialize @u to an integral value in a nested derived table. -- CREATE TABLE t AS SELECT uservar FROM (SELECT @u AS uservar FROM (select @u := CAST(0 AS UNSIGNED) AS ignored) initializer) d; -- Now, what type is that `uservar` column? -- SELECT CONCAT('1st run: t.uservar is type `', DATA_TYPE, '`') FROM information_schema.columns WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME = 't'; DROP TABLE t; -- Let's do it all over again -- CREATE TABLE t AS SELECT uservar FROM (SELECT @u AS uservar FROM (select @u := CAST(0 AS UNSIGNED) AS ignored) initializer) d; SELECT CONCAT('2nd run: t.uservar is type `', DATA_TYPE, '`') FROM information_schema.columns WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME = 't'; DROP TABLE t;