| Bug #45266 | Queries in Azalea sporadically return bad results | ||
|---|---|---|---|
| Submitted: | 2 Jun 2009 12:39 | Modified: | 4 Sep 2009 12:57 |
| Reporter: | Philip Stoev | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.4 | OS: | Any |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
| Tags: | regression | ||
[3 Jun 2009 8:16]
Sveta Smirnova
Thank you for the report. Verified as described.
[5 Jun 2009 8:47]
Philip Stoev
Another way to reproduce with the RQG: perl runall.pl --basedir=/build/bzr/azalea/ --grammar=conf/join.yy --threads=5 --validator=SelectStability The SelectStablity Validator runs the same query a few times and signals failure if the different executions returned different results. When you run this command, apart from the other unrelated error messages, you will see things like: # 11:43:00 Query: SELECT table2 . `time_key` AS field1 FROM (BB AS table1 LEFT JOIN (B AS table2 CROSS JOIN (A AS table3 STRAIGHT_JOIN (BB AS table4 LEFT OUTER JOIN (A AS table5 RIGHT OUTER JOIN (AA AS table6 INNER JOIN C AS table7 ON ( table7 . `pk` = table6 . `time_nokey` )) ON ( table7 . `int_key` = table6 . `time_nokey` )) ON ( table7 . `date_key` = table6 . `pk` )) ON ( table7 . `datetime_nokey` = table6 . `int_key` )) ON ( table7 . `time_key` = table6 . `int_nokey` )) ON ( table7 . `date_nokey` = table6 . `varchar_key` )) WHERE NOT ( ( ( table6 . `time_nokey` < '2007-09-13 18:54:24' AND table4 . `int_nokey` < 'gjgj') AND table3 . `date_key` > table1 . `pk`) AND table1 . `pk` <= 'lclc') GROUP BY field1 ; returns different result when executed after a delay of 0 seconds. --- /tmp//randgen7556-server0.dump 2009-06-05 11:43:00.000000000 +0300 +++ /tmp//randgen7556-server1.dump 2009-06-05 11:43:00.000000000 +0300 @@ -0,0 +1 @@ +NULL
[5 Jun 2009 9:56]
Philip Stoev
I could not reproduce this issue under valgrind -- it appears that the server is running too slowly for the race condition to manifest.
[22 Jun 2009 15:31]
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/76854 2796 Evgeny Potemkin 2009-06-22 Bug#45266: Uninitialized variable lead to an empty result. The TABLE::reginfo.impossible_range is used by the optimizer to indicate that the condition applied to the table is impossible. It wasn't initialized at table opening and this led to an empty result on some queries. The open_table function now initializes the TABLE::reginfo.impossible_range variable. @ sql/sql_base.cc Bug#45266: Uninitialized variable lead to an empty result. The open_table function now initializes the TABLE::reginfo.impossible_range variable. @ sql/sql_select.cc Bug#45266: Uninitialized variable lead to an empty result. The TABLE::reginfo.impossible_range variable now initialized when the table is being opened.
[24 Jun 2009 16:32]
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/77056 2796 Evgeny Potemkin 2009-06-24 Bug#45266: Uninitialized variable lead to an empty result. The TABLE::reginfo.impossible_range is used by the optimizer to indicate that the condition applied to the table is impossible. It wasn't initialized at table opening and this led to an empty result on some queries. The open_table function now initializes the TABLE::reginfo.impossible_range variable. @ mysql-test/r/select.result A test case for the bug#45266: Uninitialized variable lead to an empty result. @ mysql-test/t/select.test A test case for the bug#45266: Uninitialized variable lead to an empty result. @ sql/sql_base.cc Bug#45266: Uninitialized variable lead to an empty result. The open_table function now initializes the TABLE::reginfo.impossible_range variable. @ sql/sql_select.cc Bug#45266: Uninitialized variable lead to an empty result. The open_table function now initializes the TABLE::reginfo.impossible_range variable.
[25 Jun 2009 8:39]
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/77120 2796 Evgeny Potemkin 2009-06-25 Bug#45266: Uninitialized variable lead to an empty result. The TABLE::reginfo.impossible_range is used by the optimizer to indicate that the condition applied to the table is impossible. It wasn't initialized at table opening and this might lead to an empty result on complex queries: a query might set the impossible_range flag on a table and when the query finishes, all tables are returned back to the table cache. The next query that uses the table with the impossible_range flag set and an index over the table will see the flag and thus return an empty result. The open_table function now initializes the TABLE::reginfo.impossible_range variable. @ mysql-test/r/select.result A test case for the bug#45266: Uninitialized variable lead to an empty result. @ mysql-test/t/select.test A test case for the bug#45266: Uninitialized variable lead to an empty result. @ sql/sql_base.cc Bug#45266: Uninitialized variable lead to an empty result. The open_table function now initializes the TABLE::reginfo.impossible_range variable. @ sql/sql_select.cc Bug#45266: Uninitialized variable lead to an empty result. The open_table function now initializes the TABLE::reginfo.impossible_range variable. @ sql/structs.h Bug#45266: Uninitialized variable lead to an empty result. A comment is added.
[25 Jun 2009 12:54]
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/77184 2796 Evgeny Potemkin 2009-06-25 Bug#45266: Uninitialized variable lead to an empty result. The TABLE::reginfo.impossible_range is used by the optimizer to indicate that the condition applied to the table is impossible. It wasn't initialized at table opening and this might lead to an empty result on complex queries: a query might set the impossible_range flag on a table and when the query finishes, all tables are returned back to the table cache. The next query that uses the table with the impossible_range flag set and an index over the table will see the flag and thus return an empty result. The open_table function now initializes the TABLE::reginfo.impossible_range variable. @ mysql-test/r/select.result A test case for the bug#45266: Uninitialized variable lead to an empty result. @ mysql-test/r/select_jcl6.result A test case result adjusted after fix for the bug#45266. @ mysql-test/t/select.test A test case for the bug#45266: Uninitialized variable lead to an empty result. @ sql/sql_base.cc Bug#45266: Uninitialized variable lead to an empty result. The open_table function now initializes the TABLE::reginfo.impossible_range variable. @ sql/sql_select.cc Bug#45266: Uninitialized variable lead to an empty result. The open_table function now initializes the TABLE::reginfo.impossible_range variable. @ sql/structs.h Bug#45266: Uninitialized variable lead to an empty result. A comment is added.
[26 Jun 2009 10:52]
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/77307 2812 Evgeny Potemkin 2009-06-26 [merge] Merged bug#45266.
[26 Jun 2009 15:58]
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/77367 2977 Evgeny Potemkin 2009-06-26 Bug#45266: Uninitialized variable lead to an empty result. The TABLE::reginfo.impossible_range is used by the optimizer to indicate that the condition applied to the table is impossible. It wasn't initialized at table opening and this might lead to an empty result on complex queries: a query might set the impossible_range flag on a table and when the query finishes, all tables are returned back to the table cache. The next query that uses the table with the impossible_range flag set and an index over the table will see the flag and thus return an empty result. The open_table function now initializes the TABLE::reginfo.impossible_range variable. @ mysql-test/r/select.result A test case for the bug#45266: Uninitialized variable lead to an empty result. @ mysql-test/t/select.test A test case for the bug#45266: Uninitialized variable lead to an empty result. @ sql/sql_base.cc Bug#45266: Uninitialized variable lead to an empty result. The open_table function now initializes the TABLE::reginfo.impossible_range variable. @ sql/sql_select.cc Bug#45266: Uninitialized variable lead to an empty result. The open_table function now initializes the TABLE::reginfo.impossible_range variable. @ sql/structs.h Bug#45266: Uninitialized variable lead to an empty result. A comment is added.
[26 Jun 2009 16:00]
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/77368 2981 Evgeny Potemkin 2009-06-26 [merge] Merged bug#45266.
[29 Jun 2009 9:02]
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/77430 3409 Evgeny Potemkin 2009-06-29 [merge] Merged fix for the bug#45266.
[3 Jul 2009 6:13]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090702084644-k95gd2asolvz2zpu) (version source revid:holyfoot@mysql.com-20090626134731-v8ihjcbytkyxhm7k) (merge vers: 5.4.4-alpha) (pib:11)
[8 Jul 2009 13:30]
Bugs System
Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:epotemkin@mysql.com-20090626195941-1kwpfn7udfavap2o) (merge vers: 5.1.37) (pib:11)
[9 Jul 2009 7:35]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090702084644-k95gd2asolvz2zpu) (version source revid:holyfoot@mysql.com-20090626134731-v8ihjcbytkyxhm7k) (merge vers: 5.4.4-alpha) (pib:11)
[9 Jul 2009 7:36]
Bugs System
Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:epotemkin@mysql.com-20090626195941-1kwpfn7udfavap2o) (merge vers: 5.1.37) (pib:11)
[10 Jul 2009 11:21]
Bugs System
Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:v.narayanan@sun.com-20090629093429-51ipymxqrh0n1skw) (merge vers: 5.4.4-alpha) (pib:11)
[26 Aug 2009 13:46]
Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46]
Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48]
Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:33]
Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[4 Sep 2009 1:14]
Paul DuBois
Noted in 5.4.4 changelog. The optimizer mishandled "impossible range" conditions and returned empty results due to an uninitialized variable.
[4 Sep 2009 1:15]
Paul DuBois
The initial report for this bug says that it does not occur in 5.1, but there is a "pushed into 5.1.37" comment later on. Does the problem occur in 5.1 or was the 5.1 push a null merge?
[4 Sep 2009 7:07]
Evgeny Potemkin
5.1 is affected also. There is two different queries on which the bug manifests itself. The one reported here doesn't trigger it in 5.1, the second one does.
[4 Sep 2009 12:57]
Paul DuBois
Noted in 5.1.37 changelog.
[7 Oct 2009 18:37]
Paul DuBois
The 5.4 fix has been pushed to 5.4.2.
Description: When executing certain queries, Azalea will sporadically return zero matching rows. This failure is not present in 5.1 however appears widespread in Azalea in terms of the queries that are involved and how often it happens. Such unreliability in the results is a major obstacle to future Azalea testing. How to repeat: 1. Use the RQG to start an Azalea server and execute a single-thread workload against it. $ perl runall.pl --basedir1=/build/bzr/mysql-5.1/ --basedir2=/build/bzr/azalea/ --grammar=conf/join.yy --threads=1 --queries=1000000 --validator=ResultsetComparatorSimplify For the purpose of this bug, ignore all test output. 2. In a separate console, use the following perl script to run the same query over and over again. use strict; use DBI; $| = 1; my $dsn = 'dbi:mysql:host=127.0.0.1:port=19308:user=root:database=test'; my $query = 'SELECT table1 .`time_key` field2 FROM B table1 LEFT JOIN BB JOIN A table5 ON table5 .`date_key` ON table5 .`pk` GROUP BY field2;'; my $dbh = DBI->connect($dsn); foreach my $trial (1..1500) { my $sth = $dbh->prepare($query); $sth->execute(); if ($sth->rows() == 0) { print localtime()." [$$] trial: $trial; rows 0\n"; } } 3. Observe that approximately 50% of the query executions result in zero rows returned. If the RQG workload is stopped, then the query starts consistently returning 2 rows Suggested fix: it looks like memory corruption to me -- queries step on each other's toes.