Bug #2552 UNION returns NULL instead of expected value (innoDB only tables)
Submitted: 29 Jan 2004 4:32 Modified: 14 Feb 2004 10:26
Reporter: Andrey Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.1, 4.1.2-alpha OS:Microsoft Windows (WinXP/Suse 9.0)
Assigned to: Sinisa Milivojevic CPU Architecture:Any

[29 Jan 2004 4:32] Andrey
Description:
Query:
  create table t(  id int,  name char(10),  name2 char(10) ) type=innodb;
  insert into t values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
  select name from t  union all select name2 from t
Returns: 
  NULL
  NULL
  NULL
  fff
  sss
  ttt
Expected:
  first
  second
  thrid
  fff
  sss
  ttt

Query fail if table is InnoDB, MyISAM works as expected. 

Query "select name from t  union all select name from t" works too (name instead of name2!!).
Replacing name2 on any const fails too. 

4.1.2-alpha builded from dev tree 10 hours ago :)

How to repeat:
[29 Jan 2004 8:41] Sinisa Milivojevic
Sorry, but I get proper results.

I used this test case:
create table t1(  id int,  name char(10),  name2 char(10) ) type=innodb;
insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
select name from t1  union all select name2 from t1;

And these are the results with latest 4.1.2:
name
first
second
third
fff
sss
ttt
[29 Jan 2004 20:54] Andrey
Sorry, but I must insist that it is bug. :(

I get illegal result with 4.1.1-alpha (binary distrib) and 4.1.2-alpha (dev-tree src distrib).
May be it was fixed in a time between i built sources and report a bug?

Could you exec this test case on a 4.1.1-alpha?
[29 Jan 2004 21:26] Miguel Solorzano
At least at my side you are right. I tested with the latest BK 4.1
tree on Windows and Suse Linux:

on Windows:

mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 4.1.2-alpha-max-debug |
+-----------------------+
1 row in set (0.00 sec)

mysql> select name from t1  union all select name2 from t1;
+------+
| name |
+------+
| NULL |
| NULL |
| NULL |
| fff  |
| sss  |
| ttt  |
+------+
6 rows in set (0.01 sec)

On Suse:

hegel:/usr/local/mysql # /usr/local/mysql/bin//mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.2-alpha-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> create table t1(  id int,  name char(10),  name2 char(10) )
    -> type=innodb;
Query OK, 0 rows affected, 1 warning (0.21 sec)

mysql> insert into t1
    -> values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select name from t1  union all select name2 from t1;
+------+
| name |
+------+
| NULL |
| NULL |
| NULL |
| fff  |
| sss  |
| ttt  |
+------+
6 rows in set (0.00 sec)
[6 Feb 2004 10:54] Sinisa Milivojevic
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

A fix will come in 4.1.2
[14 Feb 2004 10:26] Sinisa Milivojevic
Changes pushed.