Bug #24345 simple query on a view crashes...
Submitted: 15 Nov 2006 21:30 Modified: 16 Jan 2007 6:09
Reporter: Jrme Despatis (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.26,5.0.30 OS:Linux (Linux Debian Testing)
Assigned to: Igor Babaev CPU Architecture:Any

[15 Nov 2006 21:30] Jrme Despatis
Description:
I have several tables, one is a view, and on a simple query which involves these queries, i get Lost connection to MySQL server during query

or no data at all if i simplify the query

This is such blocking i have to downgrade MySQL

My scripts perfectly worked when i was on MySQL 5.24a, but i've just upgraded to 5.26...

=> i need to downgrade to 5.24a waiting for this bug to be resolved

How to repeat:
Here is a .sql file in order to create all tables to show what seems to be an enormous bug :

------------------------------DUMP.SQL -------------------------------------

I'll put the script in attachement as it's bigger than 8ko

----------------------------- DUMP.SQL -------------------------------------

After loading, this query crashes:
mysql> SELECT structures.*, codes_postaux.code_postal, regions.ref_pays FROM (structures) LEFT JOIN codes_postaux ON structures.facturation_ref_code_postal = codes_postaux.id LEFT JOIN __vue_villes_fr AS villes ON structures.facturation_ref_ville = villes.id LEFT JOIN sous_regions ON villes.ref_sous_region = sous_regions.id LEFT JOIN regions ON sous_regions.ref_region = regions.id WHERE structures.id = '5';
ERROR 2013 (HY000): Lost connection to MySQL server during query

If i simplify the query (i delete 2 left joins) :
mysql> SELECT structures.*, codes_postaux.code_postal FROM (structures) LEFT JOIN codes_postaux ON structures.facturation_ref_code_postal = codes_postaux.id LEFT JOIN __vue_villes_fr AS villes ON structures.facturation_ref_ville = villes.id WHERE structures.id = '5';
Empty set (0.00 sec)

The result is empty, which is absolutely false. Indeed, when i  delete the where conditions, the query workds, here is the result :

mysql> SELECT structures.*, codes_postaux.code_postal FROM (structures) LEFT JOIN codes_postaux ON structures.facturation_ref_code_postal = codes_postaux.id LEFT JOIN __vue_villes_fr AS villes ON structures.facturation_ref_ville = villes.id;
+----+-----------------------+-----------------------------+-------------+
| id | facturation_ref_ville | facturation_ref_code_postal | code_postal |
+----+-----------------------+-----------------------------+-------------+
|  1 |                  NULL |                        NULL | NULL        |
|  2 |                  NULL |                        NULL | NULL        |
|  3 |                  NULL |                        NULL | NULL        |
|  4 |                  NULL |                        NULL | NULL        |
|  5 |                  NULL |                        NULL | NULL        |
+----+-----------------------+-----------------------------+-------------+
5 rows in set (0.00 sec)

So by adding the WHERE conditions, i should obtain this :

mysql> SELECT structures.*, codes_postaux.code_postal FROM (structures) LEFT JOIN codes_postaux ON structures.facturation_ref_code_postal = codes_postaux.id LEFT JOIN __vue_villes_fr AS villes ON structures.facturation_ref_ville = villes.id WHERE structures.id = '5';
+----+-----------------------+-----------------------------+-------------+
| id | facturation_ref_ville | facturation_ref_code_postal | code_postal |
+----+-----------------------+-----------------------------+-------------+
|  5 |                  NULL |                        NULL | NULL        |
+----+-----------------------+-----------------------------+-------------+

Suggested fix:
If i delete the WHERE close, the query works, here is a working query:
SELECT structures.*, codes_postaux.code_postal, regions.ref_pays FROM (structures) LEFT JOIN codes_postaux ON structures.facturation_ref_code_postal = codes_postaux.id LEFT JOIN __vue_villes_fr AS villes ON structures.facturation_ref_ville = villes.id LEFT JOIN sous_regions ON villes.ref_sous_region = sous_regions.id LEFT JOIN regions ON sous_regions.ref_region = regions.id;
[15 Nov 2006 21:31] Jrme Despatis
The script to create all tables in order to show the bug

Attachment: dump.sql (text/x-sql), 8.18 KiB.

[19 Nov 2006 17:00] MySQL Verification Team
verfied using the dump.sql and the query.
[19 Nov 2006 17:01] MySQL Verification Team
stack

Attachment: bug24345.txt (plain/text, text), 1.46 KiB.

[26 Nov 2006 19:55] Jrme Despatis
Another bug ! (maybe due to the previous i dunno)

With the SAME dump.sql, if i do:

mysql> SELECT structures.*, codes_postaux.code_postal FROM (structures) LEFT JOIN codes_postaux ON structures.facturation_ref_code_postal = codes_postaux.id LEFT JOIN villes ON structures.facturation_ref_ville = villes.id WHERE structures.id = '2';
+----+-----------------------+-----------------------------+-------------+
| id | facturation_ref_ville | facturation_ref_code_postal | code_postal |
+----+-----------------------+-----------------------------+-------------+
|  2 |                  NULL |                        NULL | NULL        |
+----+-----------------------+-----------------------------+-------------+

=> You get a result, which is correct
But if i do the same query on the view __vue_villes_fr (similar to tables villes), i get no result ! which is nonsens :

mysql> SELECT structures.*, codes_postaux.code_postal FROM (structures) LEFT JOIN codes_postaux ON structures.facturation_ref_code_postal = codes_postaux.id LEFT JOIN __vue_villes_fr AS villes ON structures.facturation_ref_ville = villes.id WHERE structures.id = '2';
[2 Jan 2007 23:37] Igor Babaev
The problem can be demonstrated with a simplified test case:

mysql> CREATE TABLE t1 (
    ->   id int NOT NULL PRIMARY KEY,
    ->   ct int DEFAULT NULL,
    ->   pc int DEFAULT NULL,
    ->   KEY idx_ct (ct),
    ->   KEY idx_pc (pc)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t1 VALUES   (1,NULL,NULL),(2,NULL,NULL),(3,NULL,NULL),(4,NULL,NULL),(5
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2 (
    ->   id int NOT NULL PRIMARY KEY,
    ->   sr int NOT NULL,
    ->   nm varchar(255) NOT NULL,
    ->   KEY idx_sr (sr)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t2 VALUES   (2441905,4308,'Les Abymes'),(2441906,4308,'Anse-Bertrand')
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t3 (
    ->   id int NOT NULL PRIMARY KEY,
    ->   ct int NOT NULL,
    ->   ln int NOT NULL,
    ->   KEY idx_ct (ct),
    ->   KEY idx_ln (ln)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t4 (
    ->   id int NOT NULL PRIMARY KEY,
    ->   nm varchar(255) NOT NULL
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t4 VALUES (4308,'Guadeloupe'),(4309,'Martinique');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE VIEW v1 AS
    ->   SELECT t2.id AS id, t2.sr AS sr
    ->     FROM t2 LEFT JOIN t3 ON (t3.ct=t2.id AND t3.ln='5');
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT t1.*
    ->   FROM t1 LEFT JOIN v1 ON t1.ct=v1.id
    ->     WHERE t1.id='5';
Empty set (0.02 sec)

mysql> SELECT t1.*,t4.nm
    ->   FROM t1 LEFT JOIN v1 ON t1.ct=v1.id
    ->           LEFT JOIN t4 ON v1.sr=t4.id
    ->     WHERE t1.id='5';
ERROR 2013 (HY000): Lost connection to MySQL server during query

Moreover the same problem can be demonstrated without using any view:

mysql> SELECT t1.*
    ->   FROM t1 LEFT JOIN
    ->       (t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id
    ->     WHERE t1.id='5';
Empty set (0.00 sec)

mysql> SELECT t1.*
    ->   FROM t1 LEFT JOIN
    ->       (t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id
    ->           LEFT JOIN t4 ON t2.sr=t4.id
    ->     WHERE t1.id='5';
ERROR 2013 (HY000): Lost connection to MySQL server during query
[3 Jan 2007 20: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/17592

ChangeSet@1.2351, 2007-01-03 12:16:03-08:00, igor@olga.mysql.com +3 -0
  Fixed bug #24345.
  This bug appeared after the patch for bug 21390 that had added some code
  to handle outer joins with no matches after substitution of a const
  table in an efficient way. That code as it is cannot be applied to the case
  of nested outer join operations. Being applied to the queries with
  nested outer joins the code can cause crashes or wrong result sets.
  The fix blocks row substitution for const inner tables of an outer join
  if the inner operand is not a single table.
[15 Jan 2007 8:11] Sergei Glukhov
Fixed in 5.0.34, 5.1.15-beta
[16 Jan 2007 6:09] Jon Stephens
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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.34 and 5.1.15 changelogs.