[#MDEV-15296] wrong result with window function inside a subquery

[MDEV-15296] wrong result with window function inside a subquery Created: 2018-02-13 Updated: 2023-12-07
Status:	Stalled
Project:	MariaDB Server
Component/s:	Optimizer - Window functions
Affects Version/s:	10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s:	10.4, 10.5, 10.6

Type:

Bug

Priority:

Major

Reporter:

Alice Sherepa

Assignee:

Sergei Petrunia

Resolution:

Unresolved

Votes:

Labels:

11.0-sel

Description

CREATE TABLE t(i INT);

INSERT INTO t VALUES (1), (2);

SELECT (SELECT SUM(i) OVER (partition BY i)) FROM t;

10.2 26432e49d37a37
MariaDB [test]> SELECT (SELECT SUM(i) OVER (partition BY i)) FROM t;
+---------------------------------------+
\| (SELECT SUM(i) OVER (partition BY i)) \|
+---------------------------------------+
\| NULL \|
\| NULL \|
+---------------------------------------+
2 rows in set (0.00 sec)

Expected result:

{1,2}

Comments

Comment by Varun Gupta (Inactive) [ 2019-04-29 ]

MariaDB [test]> explain extended SELECT (SELECT SUM(t.i) OVER (partition BY t.i)) FROM t1 t;

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+

|    1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 |       |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+

1 row in set, 3 warnings (0.01 sec)

MariaDB [test]> show warnings;

+-------+------+------------------------------------------------------------------------------------------------------------------------------------+

| Level | Code | Message                                                                                                                            |

+-------+------+------------------------------------------------------------------------------------------------------------------------------------+

| Note  | 1276 | Field or reference 'test.t.i' of SELECT #2 was resolved in SELECT #1                                                               |

| Note  | 1249 | Select 2 was reduced during optimization                                                                                           |

| Note  | 1003 | select sum(`test`.`t`.`i`) over ( partition by `test`.`t`.`i`) AS `(SELECT SUM(t.i) OVER (partition BY t.i))` from `test`.`t1` `t` |

+-------+------+------------------------------------------------------------------------------------------------------------------------------------+

3 rows in set (0.00 sec)

Here we see that it shows that SELECT #2 is reduced during optimization, this is only expected for constant items.

Comment by Varun Gupta (Inactive) [ 2019-04-29 ]

Comment by Oleksandr Byelkin [ 2019-04-30 ]

Please check my answer.
I would be happy to review related patch or patches.

Comment by Varun Gupta (Inactive) [ 2020-08-25 ]

Generated at Thu Feb 08 08:20:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.