[MDEV-21874] Window functions over a join with a view treat a null produced by outer join as a null from view, resulting in ifnull() taking values through nested result sets Created: 2020-03-04  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1, 10.2.31, 10.4.12, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Georgiy Vlasov Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Minimal example:

DROP TABLE IF EXISTS t;
 
CREATE TABLE t
 
(
 
    id    int unsigned not null,
 
    value int unsigned null
 
);
 
INSERT INTO t
 
values (1, null);
 
 
 
CREATE OR REPLACE VIEW v
 
as
 
select 1                 as id,
 
       ifnull(null, 999) as value
 
from t
 
;
 
 
 
select *,
 
       MIN(t.value) over (PARTITION BY t.id) as min_t,
 
       MIN(v.value) over (PARTITION BY t.id) as min_v
 
from t
 
         left join v
 
                   on t.id > v.id
 
;

With the affected MariaDB versions, the select returns:

id value id   value min_t min_v
 
1  null  null null  null  999

but I would expect it to return 

id value id   value min_t min_v
 
1  null  null null  null  null

Corresponding queries with other RDBMS:

Return null and 999:

MySQL 5.6 - it shouldn't have window functions, but it returns the same result as MariaDB http://sqlfiddle.com/#!9/7da5e/12

Return null and null:

Oracle 11g R2 - http://sqlfiddle.com/#!4/18d8e/1
PostgreSQL 9.3 - http://sqlfiddle.com/#!15/a1756/4
MySQL 8.0 - https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3a6b0308f7f889cbd0ab77ef65cd87db

See also: https://dba.stackexchange.com/questions/261229/is-it-expected-behavior-that-min-window-function-in-mariadb-mysql-can-return-a?noredirect=1#comment515417_261229

 Comments   
Comment by Alice Sherepa [ 2020-03-05 ]

Thanks a lot! Reproducible on 10.1-10.5, on 10.0 the query returns NULL as expected

CREATE TABLE t (id int);
 
INSERT INTO t values (1),(2),(3);
 
SELECT MIN(dt.id) FROM t LEFT JOIN (SELECT 5 as id from t)dt ON t.id > dt.id;


MariaDB [test]> SELECT MIN(dt.id) FROM t LEFT JOIN (SELECT 5 as id from t)dt ON t.id > dt.id;
 
+------------+
 
| MIN(dt.id) |
 
+------------+
 
|          5 |
 
+------------+
 
1 row in set (0.002 sec)

Generated at Thu Feb 08 09:10:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.