sub-subselect sometimes looses reference to enclosing table

[Daniel Heimann]

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.

[Elena Stepanova (elenst)]

Hi Daniel,

Does CLIENT-A ever return wrong results for QUERY-BAD if CLIENT-B was NOT involved in the flow before?

Thanks.

[Daniel Heimann (denu)]

hi Elena,

thanks for your immediate reply!

CLIENT-A is unaffected, when restarting the daemon and only accessing it with the mysql cli.
As soon as php-Clients (by the way: php5.2.13 compiled from source (with Client API library version => 5.0.51a, Client API header version => 5.0.32) also leads to the aforementioned behaviour) join in, the flow starts as described above.

How else, can I help?

[Elena Stepanova (elenst)]

Hi Daniel,

I've installed phpmyadmin, but couldn't so far reproduce the problem, the results differ starting from the first step: instead of
"If CLIENT-B executes QUERY-BAD the result-set is empty" i'm getting a valid result set.

So, please

1) enable general log (set global general_log=1);

2) execute your scenario;

3) then execute it again, only before each SELECT also run EXPLAIN EXTENDED SELECT ...
(still run the SELECTs too, to make sure that results didn't change because of the EXPLAIN);

4) attach the resulting general log (it shouldn't be big, but you can compress it if it makes sense) and, importantly, your cnf file.

Thanks!

[Daniel Heimann (denu)]

hi Elena,

I went through the following steps (after turning general_log=1):

1. phpmyadmin -> bad-query (empty result-set)
2. cli -> bad-query (empty result-set)

3. cli -> good-query (non-empty result-set)
4. cli -> bad-query (non-empty result-set)

5. phpmyadmin -> bad (non-empty result-set)
6. phpmyadmin -> bad (empty result-set)

Then I reexecuted 1 - 6 with "EXPLAIN EXTENDED" in front of it.
Sorry, phpmyadmin generates some more queries in the log, to render its output.

general log is:

/usr/sbin/mysqld, Version: 5.5.25-MariaDB-mariadb1~squeeze (mariadb.org binary distribution). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
120907 11:30:13 13572 Connect root@localhost as anonymous on
  13572 Query SET CHARACTER SET 'utf8'
  13572 Query SET collation_connection = 'utf8_general_ci'
  13572 Init DB mysql
  13572 Query SHOW TABLES LIKE 'user'
  13572 Init DB mysql
  13572 Query SHOW TABLE STATUS FROM `mysql` LIKE 'user%'
  13572 Query SHOW FULL COLUMNS FROM `mysql`.`user`
  13572 Quit
120907 11:30:31 13573 Connect root@localhost as anonymous on
  13573 Query SET CHARACTER SET 'utf8'
  13573 Query SET collation_connection = 'utf8_general_ci'
  13573 Init DB mysql
  13573 Init DB mysql
  13573 Query SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
 LIMIT 0, 30
  13573 Query SELECT SQL_CALC_FOUND_ROWS a . * FROM mysql.user a WHERE ( SELECT EXISTS (
SELECT 1
FROM mysql.user b
WHERE b.user = a.user
LIMIT 1 ) ) LIMIT 1
  13573 Query SELECT FOUND_ROWS()
  13573 Init DB mysql
  13573 Query SHOW TABLES LIKE 'user'
  13573 Init DB mysql
  13573 Query SHOW TABLE STATUS FROM `mysql` LIKE 'user%'
  13573 Query SELECT COUNT(*) FROM `mysql`.`user`
  13573 Query SHOW FULL COLUMNS FROM `mysql`.`user`
  13573 Quit
120907 11:33:59 13571 Query SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
120907 11:34:11 13571 Query SELECT a.* FROM mysql.user a WHERE ( EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
120907 11:34:16 13571 Query SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
120907 11:34:59 13574 Connect root@localhost as anonymous on
  13574 Query SET CHARACTER SET 'utf8'
  13574 Query SET collation_connection = 'utf8_general_ci'
  13574 Init DB mysql
  13574 Init DB mysql
  13574 Query SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
 LIMIT 0, 30
  13574 Query SELECT SQL_CALC_FOUND_ROWS a . * FROM mysql.user a WHERE ( SELECT EXISTS (
SELECT 1
FROM mysql.user b
WHERE b.user = a.user
LIMIT 1 ) ) LIMIT 1
  13574 Query SELECT FOUND_ROWS()
  13574 Init DB mysql
  13574 Query SHOW TABLES LIKE 'user'
  13574 Init DB mysql
  13574 Query SHOW TABLE STATUS FROM `mysql` LIKE 'user%'
  13574 Query SELECT COUNT(*) FROM `mysql`.`user`
  13574 Query SHOW INDEX FROM `mysql`.`user`
  13574 Query SHOW FULL COLUMNS
        FROM `mysql`.`user`
  13574 Quit
120907 11:35:05 13575 Connect root@localhost as ano...

[Elena Stepanova (elenst)]

Hi Daniel,

Thank you, sorry if I wasn't clear in my previous comment -- when you execute EXPLAIN EXTENDED, we need the actual result set. It will look somewhat like this:

+------+--------------------+-------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------------+-------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | b | index | NULL | PRIMARY | 228 | NULL | 6 | 100.00 | Using where; Using index |
+------+--------------------+-------+-------+---------------+---------+---------+------+------+----------+--------------------------+
2 rows in set, 3 warnings (0.00 sec)

(but probably a little bit different, at least for the wrong results, and this difference is what we are chasing now).

Even better if you additionally execute SHOW WARNINGS right after SHOW EXPLAIN, it will provide more information (will be verbose, considering the number of columns in the table, but it's okay).

No need to re-send the log and the config file, please just provide the output of your commands if possible.

Thank you.

[Elena Stepanova (elenst)]

Sorry, of course I meant not "after SHOW EXPLAIN" but "after EXPLAIN EXTENDED".
(SHOW EXPLAIN is something we're working on now, so it's stuck in my head).

[Daniel Heimann (denu)]

hi Elena,

thank you for clarification. I'll leave out the result-sets (when not empty, because of privacy issues). After showing "explain extended" and "show warnings" results from executing GOOD and working-BAD query in CLI, I executed "QUERY-BAD" in phpmyadmin, which then yields QUERY-BAD to not work (return no results) in CLIENT-A (CLI), too.

I collected CLI output in the following order:

1. CLI (CLIENT-A) with QUERY-GOOD
2. CLI (CLIENT-A) with QUERY-BAD (but still returning same result-set, as QUERY-GOOD)
    phpmyadmin (CLIENT-B)
3. CLI (CLIENT-A) with QUERY-BAD (returning empty result-set, after CLIENT-B executed QUERY-BAD)

Here we go:

1. CLI (CLIENT-A) with QUERY-GOOD

MariaDB [(none)]> EXPLAIN EXTENDED SELECT a.* FROM mysql.user a WHERE ( EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) );
+------+--------------------+-------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------------+-------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 351 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | b | index | NULL | PRIMARY | 228 | NULL | 351 | 100.00 | Using where; Using index |
+------+--------------------+-------+-------+---------------+---------+---------+------+------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)

MariaDB [(none)]> SHOW WARNINGS;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------...

[Elena Stepanova (elenst)]

Hi Daniel,

Thanks for all the data, unfortunately it still doesn't show the root of the problem (not because you collected it wrongly, but because the problem is apparently a tricky one).

Is it only reproducible on the `user` table with particular contents? You mentioned at the beginning that it *could* be used for convenience, and I agree that generally it's more convenient to use an already existing common table, but since it's not reproducible in our environment so far, and since your table contains private data, if possible, could you maybe give a full example, with a synthetic non-confidential table data and structure?

Thanks.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

Could you please run such variant of that 3 SELECT "bad" query in 2 variants that give different result:
SELECT a.* FROM mysql.user a WHERE ( SELECT 0+EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) );

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

ah and yet another one

SELECT a.* FROM mysql.user a WHERE 0+SELECT (EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) );

[Daniel Heimann (denu)]

hi Elena,

yes of course; that was just for convenience. Initially we found the problem within another context (an e-commerce shop-system) and rewrote it to demonstrate the effect, with tables, everyone has available.

The described effect can be seen, by just executing (as db-root):

CREATE USER example IDENTIFIED BY "exexex";
CREATE DATABASE example;
GRANT ALL ON example.* TO example;

and then (as example):

CREATE TABLE `user` ( `User` char(2), PRIMARY KEY (`User`) );
INSERT INTO `user` values ('u1');
SELECT a.* FROM example.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM example.user b WHERE b.user = a.user LIMIT 1 ) );

Nothing more is needed (thinking about it: sorry, for not coming up with that, from the very start). Especially the interaction between php and mysql-cli still kicks in as before.

While I put things together, I wanted to write some minimal php code to further reduce unknown variables (like phpmyadmin) in reproduceability. But neither a trivial function-based API approach...

 $link = mysql_connect("$host", 'example', 'exexex');
 $result = mysql_query('SELECT a.* FROM example.user a WHERE (SELECT EXISTS (SELECT 1 FROM example.user b WHERE b.user = a.user LIMIT 1))');
 while ($row = mysql_fetch_assoc($result)) {
   echo $row['User'] . "\n";
 }

...nor mysqli-API alone...

 $mysqli = new mysqli("$host", "example", "exexex", "example");
 $result = $mysqli->query('SELECT a.* FROM example.user a WHERE (SELECT EXISTS (SELECT 1 FROM example.user b WHERE b.user = a.user LIMIT 1))');
 while($row = $result->fetch_assoc()){
   echo $row['User'] . "\n";
 }

...were able to fubar the query.

So I turned on general log once more and inspected all those queries, phpmyadmin put around "QUERY-BAD":

 SET CHARACTER SET 'utf8'
 SET collation_connection = 'utf8_general_ci'
 SELECT a.* FROM example.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM example.user b WHERE b.user = a.user LIMIT 1 ) ) LIMIT 0, 30
 SELECT SQL_CALC_FOUND_ROWS a . * FROM example.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM example.user b WHERE b.user = a.user LIMIT 1 ) ) LIMIT 1
 SELECT FOUND_ROWS()
 SHOW TABLES LIKE 'user'
 SHOW TABLE STATUS FROM `example` LIKE 'user%'
 SELECT COUNT(*) FROM `example`.`user`
 SHOW FULL COLUMNS FROM `example`.`user`

Trying all of these on CLI then finally showed, that after executing:

 SHOW FULL COLUMNS FROM `example`.`user`

the result-set gets and stays empty for further QUERY-BAD until someone executes QUERY-GOOD.

I hope it helps, that we can rule out php.

Can you reproduce?

[Daniel Heimann (denu)]

hi,

Oleksandr "Sanja" Byelkin's query from comment #9 gets broken too, when someone does...

SHOW FULL COLUMNS FROM `example`.`user`;

and "magically repaired" by executing...

SELECT a.* FROM example.user a WHERE ( EXISTS ( SELECT 1 FROM example.user b WHERE b.user = a.user LIMIT 1 ) );

While the query from comment #10 can not be executed at all:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT (EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )' at line 1

[Elena Stepanova (elenst)]

Daniel,

Yes, I was able to reproduce it with 'SHOW FULL COLUMNS', as you described (I'm wondering why it didn't happen for me with phpmyadmin, possibly my hasty settings for phpmyadmin were such that this SHOW was not executed).

Thanks a lot for your analysis and thorough approach!

[Daniel Heimann (denu)]

hi Elena,

thank you for your very fast feedback and overall guidance in reporting this!
Reproducible bugs are much less worse, than (seemingly) unreproducible ones.

Happy tracing down to the roots/fixing.
Please tell me, if I can test or send in anything else to help.

Don't know if that matters, but I tested 5.5.27 (mysqld Ver 5.5.27-MariaDB-mariadb1~squeeze-log for debian-linux-gnu on x86_64 (mariadb.org binary distribution)) some minutes ago and the bug is still in there.

[Elena Stepanova (elenst)]

Refiled in JIRA as https://mariadb.atlassian.net/browse/MDEV-521
The entry in JIRA contains a testcase which works both in MySQL client and MTR (basically the same as above, only generalized).

Hi Daniel,

If you're interested in the intermediate progress on the bug, you might want to subscribe to JIRA in general (https://mariadb.atlassian.net/secure/Dashboard.jspa) and to this bug in particular.
This LP entry will be updated when the bug is fixed, but otherwise probably won't receive many updates as we are gradually switching to JIRA for bug tracking.

Thanks again for your help.

[Daniel Heimann (denu)]

Thank you very much for all the hard work! Looking forward to 5.5.28.