MySQL Bugs: #32848: Data type conversion bug in union subselects in MySQL 5.0.38

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

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.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.45, and inform about the results.

It appears that the same behavior is still present in 5.0.45.

Regards,

Arno

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)

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.

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.

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    |       |
+-------+---------+------+-----+---------+-------+

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.

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.

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

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.56

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.