Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3.11, 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
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
-
-
- Closed
-
Activity
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;
|
|
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.
|
Is there a relation to https://jira.mariadb.org/browse/MDEV-18605 too ?
FriedemannS No, I guess not. MDEV-18605 is not repeatable on the latest 10.2
This bug occurs because the condition that shouldn't be pushed is pushed into the derived table.
This issue is similar with MDEV-16765.
In this special case OR-condition inside the IF-function is defined as dependent only on fields
of the derived table while it is not. It happens because Item_cond::excl_dep_on_table() is missing.
When going through the IF-function virtual function excl_dep_on_table() reaches OR condition
it defines it as Item_func element and tries to take its arguments. It tries to take them from 'args' array.
Item_cond has empty 'args' array. All its elements are stored in the list 'list'.
So 'args' array is empty and this function defines that this OR condition depends only on the fields of the derived table and it can be pushed.
That causes a wrong result.
To fix it Item_cond::excl_dep_on_table() needs to be added.
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.