MySQL Bugs: #2858: Wrong result from UNION

Bug #2858	Wrong result from UNION
Submitted:	18 Feb 2004 2:53	Modified:	26 Apr 2004 8:23
Reporter:	Bruce Altner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.18-nt	OS:	Windows (Windows 2000 Pro)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
I just installed 4.0.18-nt and I am getting very weird results. I asked 
this before and was instructed to file this report.

I have uploaded a reproducible test case to your ftp site for verification (see below). Here is the situation:

After restarting mysql I ran the vevents.dump script in an empty test database. This created a VEVENTS table, upon which I ran this query:

SELECT LOCATION FROM VEVENTS WHERE EVENT_ID=2 UNION ALL  SELECT LOCATION 
FROM VEVENTS WHERE EVENT_ID=3;

which gives me this:

+----------+
| location |
+----------+
| Mic-5    |
| Mic-6    |
+----------+
2 rows in set (0.01 sec)

running it again gives me this:

+----------+
| location |
+----------+
| Mic-5    |
| Mic-6    |
| Mic-5    |
| Mic-6    |
+----------+
4 rows in set (0.00 sec)

and again gives me this:

+----------+
| location |
+----------+
| Mic-5    |
| Mic-6    |
| Mic-5    |
| Mic-6    |
| Mic-5    |
| Mic-6    |
+----------+
6 rows in set (0.01 sec)

and so on for as long as I care to entertain myself in this way.  If I 
restart mysql I get back to 2 rows returned.

I have reproduced this on two Windows 2000 boxes. What's going on? Why do I 
keep adding on to the result set? When I drop back down to 4.0.13 this 
problem disappears! Is this a bug in 4.0.18?

Thanks,
Bruce

How to repeat:
You should be able to reproduce this from the dump file (vevents.dump), which was created by mysqldump and uploaded to your pub/mysql/Incoming directory.

Suggested fix:
?

screen dump showing extra character in phpMyAdmin

Attachment: vevents_php.jpg (image/jpeg, text), 58.98 KiB.

I just noticed when testing this with phpMyAdmin (php 4.2.3) that an extra character is tagging along with each query. The uploaded jpg file is a screen dump taken after the third execution of the query decribed above. -BA

I testd on WinXP without to repeat the behavior reported:

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.18-nt

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

mysql> SELECT LOCATION FROM VEVENTS WHERE EVENT_ID=2 UNION ALL  SELECT LOCATION
    -> FROM VEVENTS WHERE EVENT_ID=3;
+----------+
| LOCATION |
+----------+
| Mic-5    |
| Mic-6    |
+----------+
2 rows in set (0.15 sec)

mysql> SELECT LOCATION FROM VEVENTS WHERE EVENT_ID=2 UNION ALL  SELECT LOCATION
    -> FROM VEVENTS WHERE EVENT_ID=3;
+----------+
| LOCATION |
+----------+
| Mic-5    |
| Mic-6    |
+----------+
2 rows in set (0.01 sec)

mysql> SELECT LOCATION FROM VEVENTS WHERE EVENT_ID=2 UNION ALL  SELECT LOCATION
    -> FROM VEVENTS WHERE EVENT_ID=3;
+----------+
| LOCATION |
+----------+
| Mic-5    |
| Mic-6    |
+----------+
2 rows in set (0.02 sec)

mysql> SELECT LOCATION FROM VEVENTS WHERE EVENT_ID=2 UNION ALL  SELECT LOCATION
    -> FROM VEVENTS WHERE EVENT_ID=3;
+----------+
| LOCATION |
+----------+
| Mic-5    |
| Mic-6    |
+----------+
2 rows in set (0.01 sec)

mysql>

Could you please upload for us your my.ini file.

Thanks.

Ok, now with your my.ini file I was able to repeat the behavior
reported, the below key in the my.ini file is the cause for:

set-variable=lower_case_table_names=0

and the server at the start does:

C:\mysql\bin>mysqld-nt --standalone --console
040218 20:29:09  Warning: Setting lower_case_table_names=2 because file system for C:\mysql\data
\ is case insensitive
040218 20:29:10  InnoDB: Started
mysqld-nt: ready for connections.

below the results I am getting now:

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.18-nt

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

mysql> SELECT LOCATION FROM VEVENTS WHERE EVENT_ID=2 UNION ALL  SELECT LOCATION
    -> FROM VEVENTS WHERE EVENT_ID=3;
+----------+
| LOCATION |
+----------+
| Mic-5    |
| Mic-6    |
+----------+
2 rows in set (0.01 sec)

mysql> SELECT LOCATION FROM VEVENTS WHERE EVENT_ID=2 UNION ALL  SELECT LOCATION
    -> FROM VEVENTS WHERE EVENT_ID=3;
+----------+
| LOCATION |
+----------+
| Mic-5    |
| Mic-6    |
| Mic-5    |
| Mic-6    |
+----------+
4 rows in set (0.01 sec)

mysql> SELECT LOCATION FROM VEVENTS WHERE EVENT_ID=2 UNION ALL  SELECT LOCATION
    -> FROM VEVENTS WHERE EVENT_ID=3;
+----------+
| LOCATION |
+----------+
| Mic-5    |
| Mic-6    |
| Mic-5    |
| Mic-6    |
| Mic-5    |
| Mic-6    |
+----------+
6 rows in set (0.01 sec)

Thanks you for the bug report.

This is a patch that fixes a problem:
This is a patch that fixes both in 4.0.19:
 
 *** /home/miguel/diff/old/mysqld.cc     2004-02-07 15:02:58.000000000 -0200
 --- /home/miguel/diff/new/mysqld.cc     2004-03-05 23:32:38.000000000 -0300
 ***************
 *** 2109,2114 ****
 --- 2109,2120 ----
        lower_case_table_names= 2;
      }
 
 +   if (lower_case_table_names)
 +   {
 +     casedn_str(opt_mysql_tmpdir);
 +     casedn_str(mysql_tmpdir);
 +   }
 +
    #ifdef HAVE_OPENSSL
      if (opt_use_ssl)
     {

The above one is only a proposed and not yet reviewed, much less approved, one.

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:

Note: The used fix was not the proposed one.
Fix will be in 4.0.19 and 4.1.2

ChangeSet@1.1730.2.1, 2004-03-10 13:46:11+02:00, monty@mysql.com
  Fixed memory leak in DROP DATABASE when using RAID tables (Bug #2882)

(and don't be confused by a changeset comment)