|
Re: sub-subselect sometimes looses reference to enclosing table
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;
------
------
| Note |
1276 |
Field or reference 'mysql.a.User' of SELECT #2 was resolved in SELECT #1 |
| Note |
1003 |
select `mysql`.`a`.`Host` AS `Host`,`mysql`.`a`.`User` AS `User`,`mysql`.`a`.`Password` AS `Password`,`mysql`.`a`.`Select_priv` AS `Select_priv`,`mysql`.`a`.`Insert_priv` AS `Insert_priv`,`mysql`.`a`.`Update_priv` AS `Update_priv`,`mysql`.`a`.`Delete_priv` AS `Delete_priv`,`mysql`.`a`.`Create_priv` AS `Create_priv`,`mysql`.`a`.`Drop_priv` AS `Drop_priv`,`mysql`.`a`.`Reload_priv` AS `Reload_priv`,`mysql`.`a`.`Shutdown_priv` AS `Shutdown_priv`,`mysql`.`a`.`Process_priv` AS `Process_priv`,`mysql`.`a`.`File_priv` AS `File_priv`,`mysql`.`a`.`Grant_priv` AS `Grant_priv`,`mysql`.`a`.`References_priv` AS `References_priv`,`mysql`.`a`.`Index_priv` AS `Index_priv`,`mysql`.`a`.`Alter_priv` AS `Alter_priv`,`mysql`.`a`.`Show_db_priv` AS `Show_db_priv`,`mysql`.`a`.`Super_priv` AS `Super_priv`,`mysql`.`a`.`Create_tmp_table_priv` AS `Create_tmp_table_priv`,`mysql`.`a`.`Lock_tables_priv` AS `Lock_tables_priv`,`mysql`.`a`.`Execute_priv` AS `Execute_priv`,`mysql`.`a`.`Repl_slave_priv` AS `Repl_slave_priv`,`mysql`.`a`.`Repl_client_priv` AS `Repl_client_priv`,`mysql`.`a`.`Create_view_priv` AS `Create_view_priv`,`mysql`.`a`.`Show_view_priv` AS `Show_view_priv`,`mysql`.`a`.`Create_routine_priv` AS `Create_routine_priv`,`mysql`.`a`.`Alter_routine_priv` AS `Alter_routine_priv`,`mysql`.`a`.`Create_user_priv` AS `Create_user_priv`,`mysql`.`a`.`Event_priv` AS `Event_priv`,`mysql`.`a`.`Trigger_priv` AS `Trigger_priv`,`mysql`.`a`.`Create_tablespace_priv` AS `Create_tablespace_priv`,`mysql`.`a`.`ssl_type` AS `ssl_type`,`mysql`.`a`.`ssl_cipher` AS `ssl_cipher`,`mysql`.`a`.`x509_issuer` AS `x509_issuer`,`mysql`.`a`.`x509_subject` AS `x509_subject`,`mysql`.`a`.`max_questions` AS `max_questions`,`mysql`.`a`.`max_updates` AS `max_updates`,`mysql`.`a`.`max_connections` AS `max_connections`,`mysql`.`a`.`max_user_connections` AS `max_user_connections`,`mysql`.`a`.`plugin` AS `plugin`,`mysql`.`a`.`authentication_string` AS `authentication_string` from `mysql`.`user` `a` where <expr_cache><`mysql`.`a`.`User`>(exists(select 1 from `mysql`.`user` `b` where (`mysql`.`b`.`User` = `mysql`.`a`.`User`))) |
------
2 rows in set (0.00 sec)
2. CLI (CLIENT-A) with QUERY-BAD (but still returning same result-set, as QUERY-GOOD)
MariaDB [(none)]> EXPLAIN EXTENDED SELECT a.* FROM mysql.user a WHERE ( SELECT 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 |
| 3 |
DEPENDENT SUBQUERY |
b |
index |
NULL |
PRIMARY |
228 |
NULL |
351 |
100.00 |
Using where; Using index |
-----
2 rows in set, 3 warnings (0.00 sec)
MariaDB [(none)]> SHOW WARNINGS;
------
------
| Note |
1276 |
Field or reference 'mysql.a.User' of SELECT #3 was resolved in SELECT #1 |
| Note |
1249 |
Select 2 was reduced during optimization |
| Note |
1003 |
select `mysql`.`a`.`Host` AS `Host`,`mysql`.`a`.`User` AS `User`,`mysql`.`a`.`Password` AS `Password`,`mysql`.`a`.`Select_priv` AS `Select_priv`,`mysql`.`a`.`Insert_priv` AS `Insert_priv`,`mysql`.`a`.`Update_priv` AS `Update_priv`,`mysql`.`a`.`Delete_priv` AS `Delete_priv`,`mysql`.`a`.`Create_priv` AS `Create_priv`,`mysql`.`a`.`Drop_priv` AS `Drop_priv`,`mysql`.`a`.`Reload_priv` AS `Reload_priv`,`mysql`.`a`.`Shutdown_priv` AS `Shutdown_priv`,`mysql`.`a`.`Process_priv` AS `Process_priv`,`mysql`.`a`.`File_priv` AS `File_priv`,`mysql`.`a`.`Grant_priv` AS `Grant_priv`,`mysql`.`a`.`References_priv` AS `References_priv`,`mysql`.`a`.`Index_priv` AS `Index_priv`,`mysql`.`a`.`Alter_priv` AS `Alter_priv`,`mysql`.`a`.`Show_db_priv` AS `Show_db_priv`,`mysql`.`a`.`Super_priv` AS `Super_priv`,`mysql`.`a`.`Create_tmp_table_priv` AS `Create_tmp_table_priv`,`mysql`.`a`.`Lock_tables_priv` AS `Lock_tables_priv`,`mysql`.`a`.`Execute_priv` AS `Execute_priv`,`mysql`.`a`.`Repl_slave_priv` AS `Repl_slave_priv`,`mysql`.`a`.`Repl_client_priv` AS `Repl_client_priv`,`mysql`.`a`.`Create_view_priv` AS `Create_view_priv`,`mysql`.`a`.`Show_view_priv` AS `Show_view_priv`,`mysql`.`a`.`Create_routine_priv` AS `Create_routine_priv`,`mysql`.`a`.`Alter_routine_priv` AS `Alter_routine_priv`,`mysql`.`a`.`Create_user_priv` AS `Create_user_priv`,`mysql`.`a`.`Event_priv` AS `Event_priv`,`mysql`.`a`.`Trigger_priv` AS `Trigger_priv`,`mysql`.`a`.`Create_tablespace_priv` AS `Create_tablespace_priv`,`mysql`.`a`.`ssl_type` AS `ssl_type`,`mysql`.`a`.`ssl_cipher` AS `ssl_cipher`,`mysql`.`a`.`x509_issuer` AS `x509_issuer`,`mysql`.`a`.`x509_subject` AS `x509_subject`,`mysql`.`a`.`max_questions` AS `max_questions`,`mysql`.`a`.`max_updates` AS `max_updates`,`mysql`.`a`.`max_connections` AS `max_connections`,`mysql`.`a`.`max_user_connections` AS `max_user_connections`,`mysql`.`a`.`plugin` AS `plugin`,`mysql`.`a`.`authentication_string` AS `authentication_string` from `mysql`.`user` `a` where <expr_cache><`mysql`.`a`.`User`>(exists(select 1 from `mysql`.`user` `b` where (`mysql`.`b`.`User` = `mysql`.`a`.`User`))) |
------
3 rows in set (0.00 sec)
3. CLI (CLIENT-A) with QUERY-BAD (returning empty result-set, after CLIENT-B executed QUERY-BAD two times)
MariaDB [(none)]> SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) );
Empty set (0.00 sec)
MariaDB [(none)]> EXPLAIN EXTENDED SELECT a.* FROM mysql.user a WHERE ( SELECT 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 |
| 3 |
DEPENDENT SUBQUERY |
b |
index |
NULL |
PRIMARY |
228 |
NULL |
351 |
100.00 |
Using where; Using index |
-----
2 rows in set, 3 warnings (0.00 sec)
MariaDB [(none)]> SHOW WARNINGS;
------
------
| Note |
1276 |
Field or reference 'mysql.a.User' of SELECT #3 was resolved in SELECT #1 |
| Note |
1249 |
Select 2 was reduced during optimization |
| Note |
1003 |
select `mysql`.`a`.`Host` AS `Host`,`mysql`.`a`.`User` AS `User`,`mysql`.`a`.`Password` AS `Password`,`mysql`.`a`.`Select_priv` AS `Select_priv`,`mysql`.`a`.`Insert_priv` AS `Insert_priv`,`mysql`.`a`.`Update_priv` AS `Update_priv`,`mysql`.`a`.`Delete_priv` AS `Delete_priv`,`mysql`.`a`.`Create_priv` AS `Create_priv`,`mysql`.`a`.`Drop_priv` AS `Drop_priv`,`mysql`.`a`.`Reload_priv` AS `Reload_priv`,`mysql`.`a`.`Shutdown_priv` AS `Shutdown_priv`,`mysql`.`a`.`Process_priv` AS `Process_priv`,`mysql`.`a`.`File_priv` AS `File_priv`,`mysql`.`a`.`Grant_priv` AS `Grant_priv`,`mysql`.`a`.`References_priv` AS `References_priv`,`mysql`.`a`.`Index_priv` AS `Index_priv`,`mysql`.`a`.`Alter_priv` AS `Alter_priv`,`mysql`.`a`.`Show_db_priv` AS `Show_db_priv`,`mysql`.`a`.`Super_priv` AS `Super_priv`,`mysql`.`a`.`Create_tmp_table_priv` AS `Create_tmp_table_priv`,`mysql`.`a`.`Lock_tables_priv` AS `Lock_tables_priv`,`mysql`.`a`.`Execute_priv` AS `Execute_priv`,`mysql`.`a`.`Repl_slave_priv` AS `Repl_slave_priv`,`mysql`.`a`.`Repl_client_priv` AS `Repl_client_priv`,`mysql`.`a`.`Create_view_priv` AS `Create_view_priv`,`mysql`.`a`.`Show_view_priv` AS `Show_view_priv`,`mysql`.`a`.`Create_routine_priv` AS `Create_routine_priv`,`mysql`.`a`.`Alter_routine_priv` AS `Alter_routine_priv`,`mysql`.`a`.`Create_user_priv` AS `Create_user_priv`,`mysql`.`a`.`Event_priv` AS `Event_priv`,`mysql`.`a`.`Trigger_priv` AS `Trigger_priv`,`mysql`.`a`.`Create_tablespace_priv` AS `Create_tablespace_priv`,`mysql`.`a`.`ssl_type` AS `ssl_type`,`mysql`.`a`.`ssl_cipher` AS `ssl_cipher`,`mysql`.`a`.`x509_issuer` AS `x509_issuer`,`mysql`.`a`.`x509_subject` AS `x509_subject`,`mysql`.`a`.`max_questions` AS `max_questions`,`mysql`.`a`.`max_updates` AS `max_updates`,`mysql`.`a`.`max_connections` AS `max_connections`,`mysql`.`a`.`max_user_connections` AS `max_user_connections`,`mysql`.`a`.`plugin` AS `plugin`,`mysql`.`a`.`authentication_string` AS `authentication_string` from `mysql`.`user` `a` where <expr_cache><`mysql`.`a`.`User`>(exists(select 1 from `mysql`.`user` `b` where (`mysql`.`b`.`User` = `mysql`.`a`.`User`))) |
------
3 rows in set (0.00 sec)
I hope, I got you right this time. What else can I provide?
|
|
Re: sub-subselect sometimes looses reference to enclosing table
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?
|
LPexportBug1046882.xml