| Bug #69670 | Dependent subquery with ORDER BY returns empty string | ||
|---|---|---|---|
| Submitted: | 4 Jul 2013 11:37 | Modified: | 5 Jul 2013 0:58 |
| Reporter: | Matthias Brendel | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S1 (Critical) |
| Version: | 5.5.31/5.5.32 | OS: | Linux (Debian ) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | dependent subquery, order by | ||
[4 Jul 2013 11:41]
MySQL Verification Team
Thank you for the bug report. Please provide a complete test case (table/data, actual results, expected results). Thanks.
[4 Jul 2013 11:55]
Matthias Brendel
Correction:
The query
SELECT d.identifier,
(
SELECT t.dialog
FROM translations t
WHERE t.dialog_id = d.id
AND t.language_id IN (2)
ORDER BY FIELD(t.language_id, 2)
LIMIT 1
) AS dialog
FROM dialogs d
WHERE d.identifier IN ('CUSTOMER_NUMBER')
does not return the expected value.
[5 Jul 2013 0:58]
MySQL Verification Team
Thank you for the feedback. Server 5.5 isn't affected:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.32 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT d.identifier,
-> (
-> SELECT t.dialog
-> FROM translations t
-> WHERE t.dialog_id = d.id
-> AND t.language_id IN (2)
-> ORDER BY FIELD(t.language_id, 2)
-> LIMIT 1
-> ) AS dialog
-> FROM dialogs d
-> WHERE d.identifier IN ('CUSTOMER_NUMBER');
+-----------------+--------+
| identifier | dialog |
+-----------------+--------+
| CUSTOMER_NUMBER | |
+-----------------+--------+
1 row in set (0.00 sec)
#######################################################################
[miguel@tikal mysql-5.6.12-linux-glibc2.5-x86_64]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.12 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT d.identifier,
-> (
-> SELECT t.dialog
-> FROM translations t
-> WHERE t.dialog_id = d.id
-> AND t.language_id IN (2)
-> ORDER BY FIELD(t.language_id, 2)
-> LIMIT 1
-> ) AS dialog
-> FROM dialogs d
-> WHERE d.identifier IN ('CUSTOMER_NUMBER');
+-----------------+-----------------+
| identifier | dialog |
+-----------------+-----------------+
| CUSTOMER_NUMBER | Customer numberWelcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.32 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT d.identifier,
-> (
-> SELECT t.dialog
-> FROM translations t
-> WHERE t.dialog_id = d.id
-> AND t.language_id IN (2)
-> ORDER BY FIELD(t.language_id, 2)
-> LIMIT 1
-> ) AS dialog
-> FROM dialogs d
-> WHERE d.identifier IN ('CUSTOMER_NUMBER');
+-----------------+--------+
| identifier | dialog |
+-----------------+--------+
| CUSTOMER_NUMBER | |
+-----------------+--------+
1 row in set (0.00 sec)
#######################################################################
[miguel@tikal mysql-5.6.12-linux-glibc2.5-x86_64]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.12 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT d.identifier,
-> (
-> SELECT t.dialog
-> FROM translations t
-> WHERE t.dialog_id = d.id
-> AND t.language_id IN (2)
-> ORDER BY FIELD(t.language_id, 2)
-> LIMIT 1
-> ) AS dialog
-> FROM dialogs d
-> WHERE d.identifier IN ('CUSTOMER_NUMBER');
+-----------------+-----------------+
| identifier | dialog |
+-----------------+----------------Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.32 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT d.identifier,
-> (
-> SELECT t.dialog
-> FROM translations t
-> WHERE t.dialog_id = d.id
-> AND t.language_id IN (2)
-> ORDER BY FIELD(t.language_id, 2)
-> LIMIT 1
-> ) AS dialog
-> FROM dialogs d
-> WHERE d.identifier IN ('CUSTOMER_NUMBER');
+-----------------+--------+
| identifier | dialog |
+-----------------+--------+
| CUSTOMER_NUMBER | |
+-----------------+--------+
1 row in set (0.00 sec)
#######################################################################
[miguel@tikal mysql-5.6.12-linux-glibc2.5-x86_64]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.12 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT d.identifier,
-> (
-> SELECT t.dialog
-> FROM translations t
-> WHERE t.dialog_id = d.id
-> AND t.language_id IN (2)
-> ORDER BY FIELD(t.language_id, 2)
-> LIMIT 1
-> ) AS dialog
-> FROM dialogs d
-> WHERE d.identifier IN ('CUSTOMER_NUMBER');
+-----------------+-----------------+
| identifier | dialog |
+-----------------+-----------------+
| CUSTOMER_NUMBER | Customer number |
+-----------------+-----------------+
1 row in set (0.01 sec)
mysql>
-+
| CUSTOMER_NUMBER | Customer number |
+-----------------+-----------------+
1 row in set (0.01 sec)
mysql>
|
+-----------------+-----------------+
1 row in set (0.01 sec)
mysql>
[5 Jul 2013 1:01]
MySQL Verification Team
I supposed to say: 5.6 server isn't affected.
Description: For some queries a dependent subquery does not return the expected value especially if there is an ORDER BY statement in the dependent subquery. Instead of returning the specified value it returns an empty string. This worked in MySql 5.1 but since we have updated to 5.5 we have this bug. How to repeat: Please import the uploaded data and run the following query. For column `dialog` an empty string is returned. SELECT d.identifier, ( SELECT t.dialog FROM translations t WHERE t.dialog_id = d.id AND t.language_id IN (2, 1) ORDER BY FIELD(t.language_id, 2, 1) LIMIT 1 ) AS dialog FROM dialogs d WHERE d.identifier IN ('CUSTOMER_NUMBER') # <= query single dialog However if we want to retrieve multiple dialogs the column `dialog` returns the correct value. SELECT d.identifier, ( SELECT t.dialog FROM translations t WHERE t.dialog_id = d.id AND t.language_id IN (2, 1) ORDER BY FIELD(t.language_id, 2, 1) LIMIT 1 ) AS dialog FROM dialogs d WHERE d.identifier IN ('CUSTOMER_NUMBER', 'CUSTOMER_WEIGHT') # <= query 2+ dialogs