MySQL Bugs: #24345: simple query on a view crashes...

Bug #24345	simple query on a view crashes...
Submitted:	15 Nov 2006 22:30	Modified:	16 Jan 2007 7:09
Reporter:	Jérôme Despatis (Candidate Quality Contributor)
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	5.0.26,5.0.30	OS:	Linux (Linux Debian Testing)
Assigned to:	Bugs System	Target Version:

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;

The script to create all tables in order to show the bug

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

verfied using the dump.sql and the query.

stack

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

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';

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

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.

Fixed in 5.0.34, 5.1.15-beta

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.