Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
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.3-7+squeeze14 (default debian package; tried both: mysql & mysqli)
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-MariaDB-mariadb1~squeeze-log for debian-linux-gnu on x86_64 (mariadb.org binary distribution)):
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.