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:37]
Matthias Brendel
[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.