[MDEV-4355] Wrong result (extra row) with AND and OR conditions Created: 2013-04-03  Updated: 2013-09-15  Resolved: 2013-08-21

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: 10.0.5, 5.5.33, 5.3.13

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: optimizer

Issue Links:
Relates
relates to MDEV-4418 Valgrind warnings or server crash in ... Closed
relates to MDEV-4420 Assertion `item->type() == Item::FIEL... Closed

 Description   

It is most likely a regression introduced (or made visible) by the following revision:

revno: 3628
 
revision-id: igor@askmonty.org-20130225031611-jk8lyhhjazov66qc
 
committer: Igor Babaev <igor@askmonty.org>
 
message:
 
  Fixed bug mdev-4177

I cannot verify it with 100% certainty because the provided test case causes a crash on revisions 3628..3634; but it produces a correct result on maria/5.3 up to revno 3627 and a wrong result starting from revno 3635 and up to (and including) the current 3646.
Also reproducible on current maria/5.5.

Test case:

CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (1,101),(2,102),(3,103),(4,104),(5,NULL);
 
 
 
SELECT * FROM t1
 
WHERE ( NULL OR a = 5 ) AND ( b != 1 OR a = 1 );

Actual result:

a	b
 
5	NULL

Expected result - empty set:

a	b

Inconsistency of the actual result can be confirmed by executing the second part of AND separately:

SELECT * FROM t1
 
WHERE b != 1 OR a = 1;
 
a	b
 
1	101
 
2	102
 
3	103
 
4	104

It is a correct result, and it doesn't include the row with a=5, so the result set for the bigger query cannot include it either (but currently it does).

Reproducible with the default optimizer_switch as well as with all OFF values.

EXPLAIN:

EXPLAIN EXTENDED
 
SELECT * FROM t1
 
WHERE ( NULL OR a = 5 ) AND ( b != 1 OR a = 1 );
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
 
Warnings:
 
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and ((`test`.`t1`.`b` <> 1) or 1))



 Comments   
Comment by Patryk Pomykalski [ 2013-07-06 ]

I came up with a fix, not sure if it solves the real problem...

=== modified file 'sql/sql_select.cc'
— sql/sql_select.cc 2013-06-05 20:53:35 +0000
+++ sql/sql_select.cc 2013-07-06 17:33:40 +0000
@@ -12134,8 +12134,11 @@
TABLE_LIST *native_sjm= embedding_sjm(item_equal->context_field);
if (item_const && upper->get_const())

{ - /* Upper item also has "field_item=const". Don't produce equality here */ - item= 0; + if (!item_const->eq(upper->get_const(), false)) + return new Item_int((longlong)0, 1); + else + /* Upper item also has "field_item=const". Don't produce equality here */ + item= 0; }

else
{

Comment by Sergei Golubchik [ 2013-07-10 ]

paste from the IRC:

<serg> can you explain what the problem is?
<igor> the problem arises in the following situation:
you have f1=f2/const OR FALSE (or always FALSE disjunct)
you also have other AND equalities involving f1.
the tower of equalities (in AND OR branches) is built before we
simplify conditions (in particular remove always FALSE disjuncts)
as a result after simplification we must have new sets of
equalities and they must be consistent in any respect.
now they are not. this might cause any kind of problems when we
process these conditions.
the proper solution would be to propagate new AND equalities to
the upper levels.
btw on april 30 I tried to fix mdev-4355, spent more than a day
and realized that it would take me much more.
in a general case the procedure of equality propagation from a
degenerated OR branches is recursive, because new equality can
form new always FALSE disjuct oon upper levels of AND-OR formula

Comment by Patryk Pomykalski [ 2013-07-12 ]

Maybe mdev-4177 should be reverted until it's fixed properly?

Comment by Igor Babaev [ 2013-07-12 ]

Patryk,
We are working on the problem.
Do you have any real life query that has problems due to this bug?
So far we had complains of this bug only from RQG.
We can't just revert the patch for mdev-4177, because it fixes some other problems.

Comment by Patryk Pomykalski [ 2013-07-12 ]

We haven't upgraded to MariaDB yet, just testing it in dev environment. No problems yet, but it's hard to test everything with ~20 applications and a lot of generated queries. We hope to migrate to 5.5.32, thanks for the good work

Comment by Igor Babaev [ 2013-08-21 ]

The fix for this bug was pushed into the 5.3 tree and later merged into the 5.5 tree.

Generated at Thu Feb 08 06:55:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.