Bug #32848 Data type conversion bug in union subselects in MySQL 5.0.38
Submitted: 29 Nov 2007 15:25 Modified: 28 Jan 2008 17:35
Reporter: Arno Schäfer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.0.38, 5.0.45, 5.1-BK, 6.0.3 OS:Linux
Assigned to: Martin Hansson CPU Architecture:Any

[29 Nov 2007 15:25] Arno Schäfer
Description:
I believe I may have discovered a bug in MySQL 5.0.38:

create table foo1 (a int);
create table foo2 (a int);
insert into foo1 value (1), (2), (3);
insert into foo2 value (1), (2), (3);

select * from (
  select * from (
    select NULL as a from foo1
  ) as d1
  union select * from (
    select a from foo2
  ) as d2
) as d3;

(BTW. this rather complicated construct is used to overcome "ERROR 1248
(42000): Every derived table must have its own alias" that is produced
when trying select count(*) from (select NULL as a from foo1 union select
a from foo2); ).

It appears that in this case, the selected int values are converted to
strings padded with zero characters at the end, i.e.
"1\0\0\0\0\0\0\0\0\0\0". In PHP, this requires manual casting to (int)
before the value can be further used in a string context.

When running

select NULL as a from foo1 union select a from foo2;

instead, the int values are returned correctly.

I hope this is clear enough. Is this considered a bug, and is it already a
known bug?

Best Regards,

Arno

How to repeat:
create table foo1 (a int);
create table foo2 (a int);
insert into foo1 value (1), (2), (3);
insert into foo2 value (1), (2), (3);

select * from (
  select * from (
    select NULL as a from foo1
  ) as d1
  union select * from (
    select a from foo2
  ) as d2
) as d3;

Suggested fix:
Keep int data type in result.
[29 Nov 2007 16:56] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.45, and inform about the results.
[29 Nov 2007 18:22] Arno Schäfer
It appears that the same behavior is still present in 5.0.45.

Regards,

Arno
[29 Nov 2007 19:34] Valeriy Kravchuk
Sorry, but I can not repeat this in 5.0.44, for example, and 5.0.45 should give the same results:

mysql> select version();
+------------------------------+
| version()                    |
+------------------------------+
| 5.0.44-enterprise-gpl-nt-log |
+------------------------------+
1 row in set (0.00 sec)

mysql> drop table foo1;
Query OK, 0 rows affected (0.27 sec)

mysql> drop table foo2;
Query OK, 0 rows affected (0.03 sec)

mysql> create table foo1 (a int);
Query OK, 0 rows affected (0.14 sec)

mysql> create table foo2 (a int);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into foo1 value (1), (2), (3);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into foo2 value (1), (2), (3);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from (
    ->   select * from (
    ->     select NULL as a from foo1
    ->   ) as d1
    ->   union select * from (
    ->     select a from foo2
    ->   ) as d2
    -> ) as d3;
+-------------+
| a           |
+-------------+
| NULL        |
| 1           |
| 2           |
| 3           |
+-------------+
4 rows in set (0.00 sec)
[29 Nov 2007 22:33] Arno Schäfer
It is a bit hard to recognize on the command line. Compare this:

mysql> select * from (
    ->   select * from (
    ->     select NULL as a from foo1
    ->   ) as d1
    ->   union select * from (
    ->     select a from foo2
    ->   ) as d2
    -> ) as d3;
+-------------+
| a           |
+-------------+
| NULL        |
| 1           |
| 2           |
| 3           |
+-------------+
4 rows in set (0.00 sec)

to this:

mysql> select NULL as a from foo1 union select a from foo2;
+------+
| a    |
+------+
| NULL |
|    1 |
|    2 |
|    3 |
+------+
4 rows in set (0.00 sec)

It SHOULD return the exact same result, but as you see, in the second case, the numbers are right aligned, no doubt indicating that these are integer values. In the first case, the values are left aligned AND the column width is wider than the values shown. When examining the result under PHP, it becomes apparent that the values returned are string values that are 11 characters long, and they are the int values converted to string type and padded with the zero character.
[30 Nov 2007 4:14] Valeriy Kravchuk
Sorry for NOT getting your point immediately. Indeed this is a bug:

mysql> create table ttttt select * from (
    -> select * from (
    -> select a from foo2
    -> ) as d2
    -> union
    -> select * from (
    -> select NULL as a from foo1
    -> ) as d1
    -> ) as d3;
Query OK, 4 rows affected (0.22 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc ttttt;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a     | binary(11) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.03 sec)

So, the result's data type is binary(11) and NOT integer.
[5 Dec 2007 15:51] Martin Hansson
I believe this boils down to the "type of null" problem. Indeed:

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2), (3);

CREATE TABLE t2 SELECT * FROM ( SELECT NULL FROM t1 ) d1;

CREATE TABLE t3 SELECT NULL FROM t1;

CREATE TABLE t4 SELECT a FROM t1;

DESC t2;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| NULL  | binary(0) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+

DESC t3;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| NULL  | binary(0) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+

DESC t4;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
[6 Dec 2007 15:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39423

ChangeSet@1.2546, 2007-12-06 16:37:43+01:00, mhansson@linux-st28.site +6 -0
  Bug#32848: Data type conversion bug in union subselects in MySQL 5.0.38
  
  There were two problems when inferring the correct field types resulting from
  UNION queries.
  - If the type is NULL for all corresponding fields in the UNION, the resulting 
    type would be NULL, while the type is BINARY(0) if there is just a single 
    SELECT NULL.
  - If one SELECT in the UNION uses a subselect, a temporary table is created
    to represent the subselect, and the result type defaults to a STRING type,
    hiding the fact that the type was unknown(just a NULL value).
  Fixed by remembering whenever a field was created from a NULL value and pass
  type NULL to the type coercion if that is the case, and altering
  the coercion rule for NULL+NULL to return STRING.
[11 Dec 2007 19:14] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39739

ChangeSet@1.2546, 2007-12-11 20:15:03+01:00, mhansson@linux-st28.site +6 -0
  Bug#32848: Data type conversion bug in union subselects in MySQL 5.0.38
  
  There were two problems when inferring the correct field types resulting from
  UNION queries.
  - If the type is NULL for all corresponding fields in the UNION, the resulting 
    type would be NULL, while the type is BINARY(0) if there is just a single 
    SELECT NULL.
  - If one SELECT in the UNION uses a subselect, a temporary table is created
    to represent the subselect, and the result type defaults to a STRING type,
    hiding the fact that the type was unknown(just a NULL value).
  Fixed by remembering whenever a field was created from a NULL value and pass
  type NULL to the type coercion if that is the case, and creating a string field
  as result of UNION only if the type would otherwise be NULL.
[20 Dec 2007 9:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40254

ChangeSet@1.2547, 2007-12-20 10:58:21+01:00, mhansson@linux-st28.site +1 -0
  Bug#32848: Data type conversion bug in union subselects in MySQL 5.0.38 
  
  Warnings elimination
[11 Jan 2008 12:17] Bugs System
Pushed into 6.0.5-alpha
[11 Jan 2008 12:20] Bugs System
Pushed into 5.1.23-rc
[11 Jan 2008 12:22] Bugs System
Pushed into 5.0.56
[28 Jan 2008 17:35] Paul Dubois
Noted in 5.0.56, 5.1.23, 6.0.5 changelogs:

The correct data type for a NULL column resulting from a UNION could
be determined incorrectly in some cases: 1) Not correctly inferred as
NULL depending on the number of selects; 2) Not inferred correctly as
NULL if one select used a subquery.