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: | |
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
[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.