[MDEV-18383] Change of behaviour of OR in IF-conditions 10.2 -> 10.3 - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.3.11, 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.2.23
Component/s: Optimizer
Labels:
- regression
Environment:
Windows 10, Windows Server 2008

Description

The behavior of the OR operator in the IF condition has changed from MariaDB 10.2 to 10.3.
now I noticed that the following syntax doesn't work in SELECT statements anymore:

SELECT * FROM tab t WHERE IF(@a = 1, t.a = 1 OR t.b = 1,1=1)

Now I'd have to write:

SELECT * FROM tab t WHERE (IF(@a = 1, t.a = 1, 1=1) OR IF(@a = 1, t.b = 1, 1=1))

Is it a bug or a deliberate change? Is there a setting with which I can reactivate the previous behavior?

Attachments

Issue Links

relates to

MDEV-16765 missing rows with condition on subselect

Closed

Activity

Ascending order - Click to sort in descending order

Friedemann Schmidt created issue - 2019-01-25 21:16

Friedemann Schmidt added a comment - 2019-01-26 11:12

Addendum: I have further investigated the problem. It only appears when using views in which a session variable @var is queried in the WHERE condition. I'll upload an example later.

Friedemann Schmidt added a comment - 2019-01-26 11:12 Addendum: I have further investigated the problem. It only appears when using views in which a session variable @var is queried in the WHERE condition. I'll upload an example later.

Elena Stepanova made changes - 2019-01-28 22:33

Field	Original Value	New Value
Labels	IF OR SELECT	IF OR SELECT need_feedback

Friedemann Schmidt added a comment - 2019-02-18 12:51

here is an example that shows the difference between 10.2 and 10.3

CREATE TABLE p1 (id int, p_name char(2), o_id int);

INSERT INTO p1 VALUES(1,'p1',1),(2,'p2',2);

CREATE TABLE t1 (id int, t_name char(2));

INSERT INTO t1 VALUES (1, 't1'),(2,'t2');

CREATE TABLE f1 (id int, p_id int, t_id int, b_id int);

INSERT INTO f1 VALUES (1,1,1,2),(2,1,2,2),(3,2,1,1),(4,2,2,2);

CREATE TABLE o1 (id int, o_name char(2));

INSERT INTO o1 VALUES (1, 'aa'), (2,'bb');

CREATE VIEW v1 AS SELECT

                         p_name,

                         o_id,

                         f1.id AS f_id,

                         p_id,

                         t_id,

                         b_id FROM p1 JOIN f1 ON p1.id = f1.p_id;

CREATE VIEW v2 AS SELECT

                         p_name,

                         o_id,

                         f1.id AS f_id,

                         p_id,

                         t_id,

                         b_id,

                         o_name FROM p1 JOIN f1 ON p1.id = f1.p_id JOIN o1 ON o1.id = p1.o_id GROUP BY p_id;

//unexpected output in 10.3: 0 rows

//normal output in 10.2: 2 rows

SELECT v2.p_name,

       t.t_name,

       b.t_name AS b_name

  FROM v2

  JOIN v1 ON v2.f_id = v1.f_id

  LEFT JOIN o1 AS o ON o.id = v2.o_id

  LEFT JOIN t1 AS t ON t.id = v1.t_id

  LEFT JOIN t1 AS b ON b.id = v1.b_id

WHERE IF('a' LIKE 'a',t.id = 1 OR b.id = 1, 1=1);

//solution1: drop the IF-Condition in WHERE-statement

SELECT v2.p_name,

       t.t_name,

       b.t_name AS b_name

  FROM v2

  JOIN v1 ON v2.f_id = v1.f_id

  LEFT JOIN o1 AS o ON o.id = v2.o_id

  LEFT JOIN t1 AS t ON t.id = v1.t_id

  LEFT JOIN t1 AS b ON b.id = v1.b_id

WHERE t.id = 1 OR b.id = 1;

//Solution2: o1-join after t1-joins

SELECT v2.p_name,

       t.t_name,

       b.t_name AS b_name

  FROM v2

  JOIN v1 ON v2.f_id = v1.f_id

  #LEFT JOIN o1 AS o ON o.id = v2.o_id

  LEFT JOIN t1 AS t ON t.id = v1.t_id

  LEFT JOIN t1 AS b ON b.id = v1.b_id

  LEFT JOIN o1 AS o ON o.id = v2.o_id

WHERE IF('a' LIKE 'a',t.id = 1 OR b.id = 1,1=1);

//solution3: Change of the If-Condition

SELECT v2.p_name,

       t.t_name,

       b.t_name AS b_name

  FROM v2

  JOIN v1 ON v2.f_id = v1.f_id

  LEFT JOIN o1 AS o ON o.id = v2.o_id

  LEFT JOIN t1 AS t ON t.id = v1.t_id

  LEFT JOIN t1 AS b ON b.id = v1.b_id

WHERE IF('a' LIKE 'a',t.id = 1,1=1) OR IF('a' LIKE 'a',b.id = 1, 1=1);

//Cleanup

DROP TABLE p1;

DROP TABLE t1;

DROP TABLE f1;

DROP TABLE o1;

DROP VIEW v1;

DROP VIEW v2;

Friedemann Schmidt added a comment - 2019-02-18 12:51 here is an example that shows the difference between 10.2 and 10.3 CREATE TABLE p1 (id int , p_name char ( 2 ), o_id int ); INSERT INTO p1 VALUES( 1 , 'p1' , 1 ),( 2 , 'p2' , 2 ); CREATE TABLE t1 (id int , t_name char ( 2 )); INSERT INTO t1 VALUES ( 1 , 't1' ),( 2 , 't2' ); CREATE TABLE f1 (id int , p_id int , t_id int , b_id int ); INSERT INTO f1 VALUES ( 1 , 1 , 1 , 2 ),( 2 , 1 , 2 , 2 ),( 3 , 2 , 1 , 1 ),( 4 , 2 , 2 , 2 ); CREATE TABLE o1 (id int , o_name char ( 2 )); INSERT INTO o1 VALUES ( 1 , 'aa' ), ( 2 , 'bb' ); CREATE VIEW v1 AS SELECT p_name, o_id, f1.id AS f_id, p_id, t_id, b_id FROM p1 JOIN f1 ON p1.id = f1.p_id; CREATE VIEW v2 AS SELECT p_name, o_id, f1.id AS f_id, p_id, t_id, b_id, o_name FROM p1 JOIN f1 ON p1.id = f1.p_id JOIN o1 ON o1.id = p1.o_id GROUP BY p_id; //unexpected output in 10.3: 0 rows //normal output in 10.2: 2 rows SELECT v2.p_name, t.t_name, b.t_name AS b_name FROM v2 JOIN v1 ON v2.f_id = v1.f_id LEFT JOIN o1 AS o ON o.id = v2.o_id LEFT JOIN t1 AS t ON t.id = v1.t_id LEFT JOIN t1 AS b ON b.id = v1.b_id WHERE IF( 'a' LIKE 'a' ,t.id = 1 OR b.id = 1 , 1 = 1 ); //solution1: drop the IF-Condition in WHERE-statement SELECT v2.p_name, t.t_name, b.t_name AS b_name FROM v2 JOIN v1 ON v2.f_id = v1.f_id LEFT JOIN o1 AS o ON o.id = v2.o_id LEFT JOIN t1 AS t ON t.id = v1.t_id LEFT JOIN t1 AS b ON b.id = v1.b_id WHERE t.id = 1 OR b.id = 1 ; //Solution2: o1-join after t1-joins SELECT v2.p_name, t.t_name, b.t_name AS b_name FROM v2 JOIN v1 ON v2.f_id = v1.f_id #LEFT JOIN o1 AS o ON o.id = v2.o_id LEFT JOIN t1 AS t ON t.id = v1.t_id LEFT JOIN t1 AS b ON b.id = v1.b_id LEFT JOIN o1 AS o ON o.id = v2.o_id WHERE IF( 'a' LIKE 'a' ,t.id = 1 OR b.id = 1 , 1 = 1 ); //solution3: Change of the If-Condition SELECT v2.p_name, t.t_name, b.t_name AS b_name FROM v2 JOIN v1 ON v2.f_id = v1.f_id LEFT JOIN o1 AS o ON o.id = v2.o_id LEFT JOIN t1 AS t ON t.id = v1.t_id LEFT JOIN t1 AS b ON b.id = v1.b_id WHERE IF( 'a' LIKE 'a' ,t.id = 1 , 1 = 1 ) OR IF( 'a' LIKE 'a' ,b.id = 1 , 1 = 1 ); //Cleanup DROP TABLE p1; DROP TABLE t1; DROP TABLE f1; DROP TABLE o1; DROP VIEW v1; DROP VIEW v2;

Elena Stepanova made changes - 2019-02-18 13:04

Labels

IF OR SELECT need_feedback

IF OR SELECT

Elena Stepanova made changes - 2019-02-18 13:05

Assignee		Alice Sherepa [ alice ]
Labels	IF OR SELECT	need_verification

Friedemann Schmidt added a comment - 2019-02-18 16:16

the problem occurs only if the view has a group by statement.

Friedemann Schmidt added a comment - 2019-02-18 16:16 the problem occurs only if the view has a group by statement.

Alice Sherepa added a comment - 2019-02-20 12:52

Thanks for the report and the test case!
Reproducible on current 10.2-10.4, (after 10.2.18). Introduced by commit

commit 0de3c423cc51d362c3a9b4dc8fa90d549ce0754e

Author: Galina Shalygina <galina.shalygina@mariadb.com>

Date:   Mon Aug 20 17:42:49 2018 +0300

    MDEV-16765: Missing rows with pushdown condition defined with CASE using Item_cond

    The bug appears because of the wrong pushdown into the WHERE clause of the

    materialized derived table/view work. For the excl_dep_on_grouping_fields()

    method that checks if the condition can be pushed into the WHERE clause

    the case when Item_cond is used is missing. For Item_cond elements this

    method always returns positive result (that condition can be pushed).

    So this condition is pushed even if is shouldn't be pushed.

    To fix it new Item_cond::excl_dep_on_grouping_fields() method is added.

Alice Sherepa added a comment - 2019-02-20 12:52 Thanks for the report and the test case! Reproducible on current 10.2-10.4, (after 10.2.18). Introduced by commit commit 0de3c423cc51d362c3a9b4dc8fa90d549ce0754e Author: Galina Shalygina <galina.shalygina@mariadb.com> Date: Mon Aug 20 17:42:49 2018 +0300 MDEV-16765: Missing rows with pushdown condition defined with CASE using Item_cond The bug appears because of the wrong pushdown into the WHERE clause of the materialized derived table/view work. For the excl_dep_on_grouping_fields() method that checks if the condition can be pushed into the WHERE clause the case when Item_cond is used is missing. For Item_cond elements this method always returns positive result (that condition can be pushed). So this condition is pushed even if is shouldn't be pushed. To fix it new Item_cond::excl_dep_on_grouping_fields() method is added.