Details
-
Bug
-
Status: In Review (View Workflow)
-
Critical
-
Resolution: Unresolved
-
13.0.2
-
None
-
Docker
Server version: 13.0.2-MariaDB-ubu2604
Source revision: 620a4b5624222048fece7fe1789978ae0675cae2
Description
Hi, I found a logic bug in MariaDB 13.0.2.
An IN subquery whose SELECT list contains a window function returns an empty set, and the
equivalent scalar comparison returns NULL. The same subquery materialized into a derived table
returns the correct rows, and a window function written by the user in HAVING is rejected with
ERROR 4015 — so the windowed comparison is being injected into HAVING internally.
Expected results:
+-------------------+
|
| materialized_form |
|
+-------------------+
|
| 1,2,3,4 |
|
+-------------------+
|
+------------+
|
| two_in_set |
|
+------------+
|
| 1 |
|
+------------+
|
Actual Results:
+--------------+
|
| failing_form |
|
+--------------+
|
| NULL |
|
+--------------+
|
+------------+
|
| two_in_set |
|
+------------+
|
| NULL |
|
+------------+
|
How to repeat:
CREATE TABLE t(a INT); |
INSERT INTO t VALUES (1),(2),(3),(4); |
|
|
-- correct: the same subquery materialized into a derived table
|
SELECT GROUP_CONCAT(a ORDER BY a) AS materialized_form |
FROM t WHERE a IN (SELECT rn FROM (SELECT row_number() over () AS rn FROM t UNION SELECT 100) d); |
|
|
-- wrong: window function directly in the IN subquery
|
SELECT GROUP_CONCAT(a ORDER BY a) AS failing_form |
FROM t WHERE a IN (SELECT row_number() over () FROM t UNION SELECT 100); |
|
|
SELECT (2 IN (SELECT row_number() over () FROM t UNION SELECT 100)) AS two_in_set; |
|
|
-- the same construct written by the user is rejected, not silently mis-evaluated
|
SELECT 1 AS one HAVING row_number() over () = 1; -- ERROR 4015 |
|
|
-- plan: the windowed comparison ends up in HAVING
|
EXPLAIN FORMAT=JSON SELECT a FROM t |
WHERE a IN (SELECT row_number() over () FROM t UNION SELECT 100); |