Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.3
-
None
Description
For the following table
create table t1 (id int);
|
insert into t1 values (1), (2), (3), (2);
|
the query
select sum(max(id)) over (order by max(id)) from t1;
|
returns the correct result:
MariaDB [test]> select sum(max(id)) over (order by max(id)) from t1;
|
+--------------------------------------+
|
| sum(max(id)) over (order by max(id)) |
|
+--------------------------------------+
|
| 3 |
|
+--------------------------------------+
|
However after creation of an index on t1(id) the same query returns a wrong result:
|
MariaDB [test]> create index idx on t1(id);
|
Query OK, 0 rows affected (0.05 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select sum(max(id)) over (order by max(id)) from t1;
|
+--------------------------------------+
|
| sum(max(id)) over (order by max(id)) |
|
+--------------------------------------+
|
| NULL |
|
+--------------------------------------+
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Component/s | Optimizer - Window functions [ 13502 ] | |
| Affects Version/s | 10.2.3 [ 22115 ] | |
| Description |
For the following table
{noformat} create table t1 (id int); insert into t1 values (1), (2), (3), (2); {noformat} the query {noformat} select sum(max(id)) over (order by max(id)) from t1; {noformat} returns the correct result: {noformat} MariaDB [test]> select sum(max(id)) over (order by max(id)) from t1; +--------------------------------------+ | sum(max(id)) over (order by max(id)) | +--------------------------------------+ | 3 | +--------------------------------------+ {noformat} However after creation of an index on t1(id) the same query returns a wrong result: {noformat} MariaDB [test]> create index idx on t1(id); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test]> select sum(max(id)) over (order by max(id)) from t1; +--------------------------------------+ | sum(max(id)) over (order by max(id)) | +--------------------------------------+ | NULL | +--------------------------------------+ {noformat} |
| Assignee | Igor Babaev [ igor ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Summary | Wrong result for query with window function when all tables are optimized away | t |
| Summary | t | Wrong result for query with window function when all tables are optimized away |
| Assignee | Igor Babaev [ igor ] | Vicentiu Ciorbaru [ cvicentiu ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Assignee | Vicentiu Ciorbaru [ cvicentiu ] | Igor Babaev [ igor ] |
| Fix Version/s | 10.2.6 [ 22527 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Link |
This issue relates to |
| Workflow | MariaDB v3 [ 79512 ] | MariaDB v4 [ 151662 ] |
Ok to push.