Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.8.0, 10.5, 10.6, 10.11, 11.4, 11.8
-
git rev-parse HEAD
11a6c1b30a12c448ddfe05e1b818a6a228e90e43
Description
Description:
When using the VAR_POP(x) function in a query and applying the WHERE VAR_POP(x) IS NOT NULL condition, MariaDB returns incorrect results when the column is indexed. The expected behavior is that both queries should return the same result, but the second query returns an empty set, even though the first query returns a result.
Steps to Reproduce:
DROP TABLE IF EXISTS `t1`;
|
CREATE TABLE `t1` (
|
`c1` double, |
KEY `i1` (`c1` DESC)
|
) ;
|
|
|
INSERT INTO `t1` VALUES (48),(48); |
query1:
select distinct
|
subq_0.c_0 as c_0,
|
subq_0.c_1 as c_1
|
from
|
(select distinct
|
ref_0.c1 as c_0,
|
VAR_POP(c1) as c_1
|
from
|
t1 as ref_0
|
group by ref_0.c1 ) as subq_0
|
output:
+------+------+
|
| c_0 | c_1 |
|
+------+------+
|
| 48 | 0 | |
+------+------+
|
1 row in set (0.00 sec) |
query1:
|
{code:java}
|
select distinct
|
subq_0.c_0 as c_0,
|
subq_0.c_1 as c_1
|
from
|
(select distinct
|
ref_0.c1 as c_0,
|
VAR_POP(c1) as c_1
|
from
|
t1 as ref_0
|
group by ref_0.c1 ) as subq_0
|
where
|
((subq_0.c_1) is not null)
|
output:
Empty set (0.00 sec) |
Expected Behavior: Both queries should return the same result.
Actual Behavior:
The first query returns the expected result, while the second query returns an empty set. This is incorrect behavior, as the result of both queries should be the same.
Thanks! I repeated as described on 10.5-11.8.
As a temporary workaround SET optimizer_switch='condition_pushdown_for_derived=off'; could be used.
Also repeatable with the other ~similar functions.
MariaDB [test]> SELECT * FROM (SELECT std(c1) AS a FROM t1) dt WHERE (dt.a) IS NOT NULL;Empty set (0,001 sec)MariaDB [test]> SET optimizer_switch='condition_pushdown_for_derived=off';Query OK, 0 rows affected (0,000 sec)MariaDB [test]> SELECT * FROM (SELECT std(c1) AS a FROM t1) dt WHERE (dt.a) IS NOT NULL;+--------+| a |+--------+| 1.0000 |+--------+1 row in set (0,001 sec)MariaDB [test]> SET optimizer_switch='condition_pushdown_for_derived=on';Query OK, 0 rows affected (0,001 sec)MariaDB [test]> explain extended SELECT * FROM (SELECT std(c1) AS a FROM t1) dt WHERE (dt.a) IS NOT NULL;+------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where || 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | |+------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+2 rows in set, 1 warning (0,004 sec)Note (Code 1003): /* select#1 */ select `dt`.`a` AS `a` from (/* select#2 */ select std(`test`.`t1`.`c1`) AS `a` from `test`.`t1` having `a` is not null) `dt` where `dt`.`a` is not null