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:
None 
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
Triage: Needs Triage: D1 (Critical)

[4 Jul 2013 11:37] Matthias Brendel
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
[4 Jul 2013 11:41] Miguel Solorzano
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] Miguel Solorzano
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] Miguel Solorzano
I supposed to say: 5.6 server isn't affected.