Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.40, 10.0.14
-
None
-
5.5.44, 5.5.45, 5.5.47-1, 5.5.48-0
Description
A query with scalar subquery in a comparison returns no result when using "ORDER BY" and "LIMIT" in the scalar subquery. (https://mariadb.com/kb/en/sql-99-complete-really/31-searching-with-subqueries/scalar-subqueries/#comment_1325)
Sample SQL statement:
SELECT SQL_NO_CACHE a.project_number FROM projects a WHERE ( SELECT z.country FROM projects_history z WHERE z.project_number = a.project_number AND z.history_date <= '2014-09-01' ORDER BY z.id DESC LIMIT 1 ) IN ( SELECT r.country FROM region r WHERE r.region = 'eame' ); |
The query was executed in MariaDB (10.0.3), MySQL(5.6.17) and MySQL(5.0.96). The MySQL(5.0.96) returns the expected result.
See attached files for details (db dump, explain, actual result and expected result)
Attachments
Issue Links
- links to
This is where we get the lookup value from:
(gdb) wher
#0 Item_field::str_result (this=0x7fff94008ae8, str=0x7fff9400db50) at /home/psergey/dev2/5.5/sql/item.cc:2602
#1 0x00000000007f6808 in Item_cache_str::cache_value (this=0x7fff9400da50) at /home/psergey/dev2/5.5/sql/item.cc:9154
#2 0x00000000007fc1d8 in Item_cache::has_value (this=0x7fff9400da50) at /home/psergey/dev2/5.5/sql/item.h:4124
#3 0x00000000007f6bb9 in Item_cache_str::save_in_field (this=0x7fff9400da50, field=0x7fff94029988, no_conversions=true) at /home/psergey/dev2/5.5/sql/item.cc:9225
#4 0x000000000068241c in store_key_item::copy_inner (this=0x7fff94029950) at /home/psergey/dev2/5.5/sql/sql_select.h:1664
#5 0x000000000068200b in store_key::copy (this=0x7fff94029950) at /home/psergey/dev2/5.5/sql/sql_select.h:1554
#6 0x000000000067421c in cp_buffer_from_ref (thd=0x2f5bcb0, table=0x7fff9402e268, ref=0x7fff940284f8) at /home/psergey/dev2/5.5/sql/sql_select.cc:20357
#7 0x0000000000674155 in cmp_buffer_with_ref (thd=0x2f5bcb0, table=0x7fff9402e268, tab_ref=0x7fff940284f8) at /home/psergey/dev2/5.5/sql/sql_select.cc:20339
#8 0x000000000066d22d in join_read_key2 (thd=0x2f5bcb0, tab=0x7fff940282e8, table=0x7fff9402e268, table_ref=0x7fff940284f8) at /home/psergey/dev2/5.5/sql/sql_select.cc:17522
#9 0x000000000066d18e in join_read_key (tab=0x7fff940282e8) at /home/psergey/dev2/5.5/sql/sql_select.cc:17500
#10 0x000000000066bbae in sub_select (join=0x7fff9400c610, join_tab=0x7fff940282e8, end_of_records=false) at /home/psergey/dev2/5.5/sql/sql_select.cc:16874
#11 0x000000000066c2d7 in evaluate_join_record (join=0x7fff9400c610, join_tab=0x7fff94027fc8, error=0) at /home/psergey/dev2/5.5/sql/sql_select.cc:17096
#12 0x000000000066bc10 in sub_select (join=0x7fff9400c610, join_tab=0x7fff94027fc8, end_of_records=false) at /home/psergey/dev2/5.5/sql/sql_select.cc:16877
#13 0x000000000066b477 in do_select (join=0x7fff9400c610, fields=0x2f5f7c0, table=0x0, procedure=0x0) at /home/psergey/dev2/5.5/sql/sql_select.cc:16539
#14 0x0000000000649dff in JOIN::exec (this=0x7fff9400c610) at /home/psergey/dev2/5.5/sql/sql_select.cc:2873
#15 0x000000000064a60f in mysql_select (thd=0x2f5bcb0, rref_pointer_array=0x2f5f918, tables=0x7fff94007ab0, wild_num=0, fields=..., conds=0x7fff9400c368, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff9400c5f0, unit=0x2f5efd0, select_lex=0x2f5f6a8) at /home/psergey/dev2/5.5/sql/sql_select.cc:3094
We copy the data from "z.country" field:
(gdb) p this->field_name
$125 = 0x7fff94016d20 "country"
(gdb) p this
$126 = (Item_field *) 0x7fff94008ae8
(gdb) p this->field->table->alias.Ptr
$127 = 0x7fff94012440 "z"
the problem is, we never executed the subquery. A breakpoint in JOIN::exec() was only hit for select_number==1