[MDEV-6972] Left joined subquery gives wrong result Created: 2014-10-29  Updated: 2015-02-15  Resolved: 2015-02-15

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: 5.3.12, 5.5.40, 10.0.14
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Dennis Minderhoud Assignee: Oleksandr Byelkin
Resolution: Duplicate Votes: 0
Labels: optimizer
Environment:

Windows Server 2012

Issue Links:
Duplicate
duplicates MDEV-6892 WHERE does not apply Closed
duplicates MDEV-6919 wrong result from subquery - too much... Closed

 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



 Comments   
Comment by Elena Stepanova [ 2014-10-29 ]

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-6892 and MDEV-6919, but I'm assigning it to Oleksandr Byelkin to make sure that the fix covers all test cases.

Comment by Oleksandr Byelkin [ 2014-11-17 ]

The problem is in table elimination. With table_elimination=off we have correct result also derived table is absent in the EXPLAIN output.

Comment by Sergei Petrunia [ 2014-11-19 ]

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.

Comment by Sergei Petrunia [ 2014-11-19 ]

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.

Comment by Sergei Petrunia [ 2014-11-19 ]

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

Comment by Sergei Petrunia [ 2014-11-19 ]

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.

Comment by Oleksandr Byelkin [ 2015-02-14 ]

I feel Déjà vu here. I definetely alredy was adding null_ref_table dependency to used_tables(). (The patch probably lost somewhere)

Comment by Oleksandr Byelkin [ 2015-02-14 ]

The usage of null_ref_table have to be limited somehow in LEFT JOIN...

Comment by Oleksandr Byelkin [ 2015-02-15 ]

It is duplicate of MDEV-6892 which is on review.

Generated at Thu Feb 08 07:15:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.