sub-subselect sometimes looses reference to enclosing table
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Oleksandr "Sanja" Byelkin |
Bug Description
Trying to match a field with the out-most table from a EXISTS-sub-subquery sometimes silently fails.
There is a combination which seemingly "repairs" this, but then again reproduceably breaks it - see below.
We already tried to turn off all optimizer features and disabled query cache; both to no avail.
To easily reproduce this, please use the contained 'mysql' database with the following example queries.
the clients:
CLIENT-A: mysql-cli
Version: 15.1 Distrib 5.5.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1)
Connected via: unix-socket
CLIENT-B: php Application (phpmyadmin o.e. to execute Queries will do)
Version: php5.3.
Connected via: unix-socket
the queries:
"QUERY-GOOD":
SELECT a.* FROM mysql.user a WHERE ( EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) );
"QUERY-BAD":
SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) );
Notice the "SELECT EXISTS" vs. "EXISTS" only.
the bug:
QUERY-GOOD works on CLIENT-A and CLIENT-B.
QUERY-BAD works every time on MySQL 5.5.25a, MySQL 5.1.63 and MySQL 5.0.51a. Not so, in MariaDB 5.5.25 (5.5.25-
If CLIENT-B executes QUERY-BAD the result-set is empty. (should not and is not in MySQL-5.*)
If CLIENT-A afterwards executes QUERY-BAD the result-set is empty, too. (should not and is not in MySQL-5.*)
If CLIENT-A now executes QUERY-GOOD there are results returned. (always)
If CLIENT-A then executes QUERY-BAD again there are still results returned! (strange: initially the result was empty)
it stays like this for CLIENT-A until...
if CLIENT-B executes QUERY-BAD once: it gets the same results like CLIENT-A. ("healed")
if CLIENT-B executes QUERY-BAD one more time: the result-set is once more empty ("broken again")
if CLIENT-A now executes QUERY-BAD the result-set it is empty again and stays so, until QUERY-GOOD is re-executed.
I don't think MariaDB should return empty result-sets when MySQL does not.
I further assume queries originating from different clients, shouldn't interfere like this.
Sorry, if this was overly complex.
Changed in maria: | |
status: | New → Fix Released |
Hi Daniel,
Does CLIENT-A ever return wrong results for QUERY-BAD if CLIENT-B was NOT involved in the flow before?
Thanks.