Bug #11866 Union Caused Crash

• Submitted: 11 Jul 2005 18:26
• Modified: 25 Jul 2005 10:55
• Reporter: Robert Ludwick
• Status: Can't repeat
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.1.10a
• OS: Linux (Linux/Apache)
• CPU Architecture: Any

[11 Jul 2005 18:26] Robert Ludwick

Description:
The following statement stops the MySQL daemon altogether on our server. When run separately, the two select statements execute correctly.

(select sum(`table1`.`field1`), date(`table2`.`field2`) as `this`, 0 from `table1`, `table2` where date(`table2`.`field2`)>='2001-01-01' and date(`table2`.`field2`)<='2001-02-01' and `table1`.`field3`='xxx' and `table1`.`field4`=`table2`.`field5` group by `this`)

union

(select 0, `field1` as `this`, `field2` from `table1` where `field3`>='2001-02-01' and `field3`<='2001-02-01' and `field4`='x' and `field5`='xxx')

How to repeat:
Run a similar query.

Suggested fix:
I don't know. Has to do with the UNION.

[11 Jul 2005 22:53] MySQL Verification Team

Could you please provide a dump (with data definition and some data)
for to run the offended query. There are similar bug reports and doing
our own test case maybe it won't repeat your test case.

Thanks in advance.

[11 Jul 2005 23:05] Robert Ludwick

I can't. The tables that this query deals all have sensitive data from an ecommerce application.

In the first select statement, the fields are as follows:
field1 = int
field2 = datetime
field3 = text
field4 and field5 = varchar

In the second:
field1 = date
field2 = int
field3 = date
field4 and field5 = varchar

[12 Jul 2005 3:34] Jorge del Conde

I was unable to reproduce this bug in 4.1.13 from bk:

mysql> desc table1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| field1 | int(11)     | YES  |     | NULL    |       |
| field2 | datetime    | YES  |     | NULL    |       |
| field3 | text        | YES  |     | NULL    |       |
| field4 | varchar(50) | YES  |     | NULL    |       |
| field5 | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> desc table2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| field1 | date        | YES  |     | NULL    |       |
| field2 | int(11)     | YES  |     | NULL    |       |
| field3 | date        | YES  |     | NULL    |       |
| field4 | varchar(50) | YES  |     | NULL    |       |
| field5 | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> (select sum(`table1`.`field1`), date(`table2`.`field2`) as `this`, 0 from
    -> `table1`, `table2` where date(`table2`.`field2`)>='2001-01-01' and
    -> date(`table2`.`field2`)<='2001-02-01' and `table1`.`field3`='xxx' and
    -> `table1`.`field4`=`table2`.`field5` group by `this`)
    -> 
    -> union
    -> 
    -> (select 0, `field1` as `this`, `field2` from `table1` where
    -> `field3`>='2001-02-01' and `field3`<='2001-02-01' and `field4`='x' and
    -> `field5`='xxx')
    -> ;
Empty set (0.00 sec)

[25 Jul 2005 10:55] Sergei Golubchik

If you cannot share your tables, you may try to create an artificial test case - using a tables of a similar structure, and generated content. Feel free to reopen this bugreport if you'll succeed.

Unfortunately, we cannot fix a behavior that we cannot repeat :(