Details
Critical
Description
The result of the query stated below gives always an amount of 1.
CREATE TABLE `table1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `table2` ( `user_id` int(11) NOT NULL, `table1_id` int(11) NOT NULL, PRIMARY KEY (`user_id`,`table1_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO table1 (`id`,`name`) VALUES (1,'test1'), (2,'test2'), (3,'test3'), (4,'test4'), (5,'test5'), (6,'test6'), (7,'test7'), (8,'test8'), (9,'test9'), (10,'test10'); INSERT INTO table2 (`table1_id`,`user_id`) VALUES (1,1), (2,2), (3,1), (4,2), (5,1), (6,2), (7,1), (8,2), (9,1), (10,2); SELECT t1.id, COALESCE(t2.amount, 0) AS amount FROM table1 t1 LEFT JOIN (SELECT 1 AS amount, table1_id, user_id FROM table2) t2 ON t2.table1_id = t1.id AND t2.user_id = 1;
When this query was run on MySQL 5.6, the result was as expected;
Result MariaDB:
1 1 2 1 3 1 4 1 5 1 6 1 7 1 8 1 9 1 10 1
Result MySQL:
1 1 2 0 3 1 4 0 5 1 6 0 7 1 8 0 9 1 10 0
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The problem is in table elimination. With table_elimination=off we have correct result also derived table is absent in the EXPLAIN output.
EXPLAIN shows that table elimination removed t2:
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | index | NULL | PRIMARY | 4 | NULL | 10 | 100.00 | Using index | +------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
This happened, because ON expression is
t2.table1_id = t1.id AND t2.user_id = 1;
AND pk is:
PRIMARY KEY (`user_id`,`table1_id`)
However, the query's select list is :
SELECT t1.id, COALESCE(t2.amount, 0) AS amount
it has t2.amount . This means, t2 could not be eliminated. Need to investigate why table elimination still eliminated the table.
Debugging in eliminate_tables:
(gdb) p dbug_print_item(item) $5 = 0x195f480 "coalesce(1,0)" (gdb) p item->used_tables() $6 = 0
(gdb) p item $7 = (Item_func_coalesce *) 0x7fff88006a88 (gdb) p item->args[0] $8 = (Item_direct_view_ref *) 0x7fff8805feb8 (gdb) p item->args[0]->ref[0] $10 = (Item_int *) 0x7fff88008260
For some reason, "t2.amount" was replaced with constant "1". This is why Table Elimination thinks it can eliminate table t2.
The reason to replace t2.amount with "1" was that "amount" is not a real field. It is a constant:
LEFT JOIN (SELECT 1 AS amount, table1_id, user_id FROM table2) t2
I wonder, if the select list has "coalesce(1,0)", how could it be that it has different values when table_elimination=off ?
The answer is:
(gdb) wher #0 Item_direct_view_ref::check_null_ref (this=0x7fff8805f750) at /home/psergey/dev2/10.0/sql/item.h:3746 #1 0x00000000008781ba in Item_direct_view_ref::val_int (this=0x7fff8805f750) at /home/psergey/dev2/10.0/sql/item.h:3805 #2 0x0000000000886749 in Item_func_coalesce::int_op (this=0x7fff88006a60) at /home/psergey/dev2/10.0/sql/item_cmpfunc.cc:3292 #3 0x00000000008acb89 in Item_func_hybrid_result_type::val_int (this=0x7fff88006a60) at /home/psergey/dev2/10.0/sql/item_func.cc:1000 #4 0x000000000086ba91 in Item::send (this=0x7fff88006a60, protocol=0x422c538, buffer=0x7fffc86d9f30) at /home/psergey/dev2/10.0/sql/item.cc:6489 #5 0x00000000005b5817 in Protocol::send_result_set_row (this=0x422c538, row_items=0x42304c0) at /home/psergey/dev2/10.0/sql/protocol.cc:904 #6 0x00000000006233cb in select_send::send_data (this=0x7fff88079f48, items=...) at /home/psergey/dev2/10.0/sql/sql_class.cc:2542 #7 0x00000000006beb7a in end_send (join=0x7fff8805eb18, join_tab=0x7fff88064df0, end_of_records=false) at /home/psergey/dev2/10.0/sql/sql_select.cc:18825 #8 0x00000000006bcd3e in evaluate_null_complemented_join_record (join=0x7fff8805eb18, join_tab=0x7fff88064ac8) at /home/psergey/dev2/10.0/sql/sql_select.cc:18052 #9 0x00000000006bc451 in sub_select (join=0x7fff8805eb18, join_tab=0x7fff88064ac8, end_of_records=false) at /home/psergey/dev2/10.0/sql/sql_select.cc:17755 #10 0x00000000006bc960 in evaluate_join_record (join=0x7fff8805eb18, join_tab=0x7fff880647a0, error=0) at /home/psergey/dev2/10.0/sql/sql_select.cc:17933 #11 0x00000000006bc3eb in sub_select (join=0x7fff8805eb18, join_tab=0x7fff880647a0, end_of_records=false) at /home/psergey/dev2/10.0/sql/sql_select.cc:17750 #12 0x00000000006bbabb in do_select (join=0x7fff8805eb18, fields=0x42304c0, table=0x0, procedure=0x0) at /home/psergey/dev2/10.0/sql/sql_select.cc:17373 #13 0x0000000000699234 in JOIN::exec_inner (this=0x7fff8805eb18) at /home/psergey/dev2/10.0/sql/sql_select.cc:3080 #14 0x000000000069675a in JOIN::exec (this=0x7fff8805eb18) at /home/psergey/dev2/10.0/sql/sql_select.cc:2370
Apparently, Item_direct_view_ref that points to a constant will still check if certain table is NULL, and return different values depending on that:
bool check_null_ref()
{
if (null_ref_table == NULL)
{
if (!(null_ref_table= view->get_real_join_table()))
null_ref_table= NO_NULL_TABLE;
}
if (null_ref_table != NO_NULL_TABLE && null_ref_table->null_row)
{
null_value= 1;
return TRUE;
}
Based on this, I think that Item_func_direct_view_ref() should not return 0 from item->used_tables(). It should return null_ref_table, if it has one.
I feel Déjà vu here. I definetely alredy was adding null_ref_table dependency to used_tables(). (The patch probably lost somewhere)
The usage of null_ref_table have to be limited somehow in LEFT JOIN...
It is duplicate of MDEV-6892 which is on review.
Thanks for the report.
As a workaround, please try to set optimizer_switch='derived_merge=off'.
It is likely to be a duplicate of
MDEV-6892andMDEV-6919, but I'm assigning it to Oleksandr Byelkin to make sure that the fix covers all test cases.