Details
Major Description
Starting with MariaDB 10.2 the SQL snippet below does no longer return the same result on all three SELECTs at its end as expected, instead the last query, which uses the view with ORDER BY in it, returns an empty result set now:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
dt datetime,
|
INDEX(dt),
|
foo int
|
);
|
|
|
INSERT INTO t1 VALUES (1,'2020-09-26 12:00:00',1);
|
INSERT INTO t1 VALUES (2,'2020-09-26 13:00:00',1);
|
INSERT INTO t1 VALUES (3,'2020-09-27 13:00:00',1);
|
INSERT INTO t1 VALUES (4,'2020-09-27 12:00:00',1);
|
INSERT INTO t1 VALUES (5,'2020-09-28 12:00:00',1);
|
INSERT INTO t1 VALUES (6,'2020-09-28 13:00:00',1);
|
INSERT INTO t1 VALUES (7,'2020-09-25 12:00:00',1);
|
INSERT INTO t1 VALUES (8,'2020-09-25 13:00:00',1);
|
INSERT INTO t1 VALUES (9,'2020-09-26 13:00:00',1);
|
|
|
DROP VIEW IF EXISTS v1;
|
CREATE VIEW v1 AS SELECT * FROM t1;
|
|
|
DROP VIEW IF EXISTS v2;
|
CREATE VIEW v2 AS SELECT * FROM t1 ORDER BY dt;
|
|
|
SELECT dt, sum(foo) AS foo FROM t1 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt;
|
|
|
SELECT dt, sum(foo) AS foo FROM v1 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt;
|
|
|
SELECT dt, sum(foo) AS foo FROM v2 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt;
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Assignee | Varun Gupta [ varun ] |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Varun Gupta [ varun ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Petrunia [ psergey ] | Michael Widenius [ monty ] |
| Attachment | mdev-23826-new.diff [ 55616 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Assignee | Michael Widenius [ monty ] | Varun Gupta [ varun ] |
| Component/s | Optimizer [ 10200 ] | |
| Fix Version/s | 10.2.37 [ 25112 ] | |
| Fix Version/s | 10.3.28 [ 25111 ] | |
| Fix Version/s | 10.4.18 [ 25110 ] | |
| Fix Version/s | 10.5.9 [ 25109 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 113931 ] | MariaDB v4 [ 158407 ] |
Shouldn't the change you did to Item_direct_view_ref be done to Item_direct_ref ?
Item_direct_view_ref::val_time_packed() should probably be written as the other val methods in Item_direct_view_ref:
double val_real()
{ if (check_null_ref()) return 0; else return Item_direct_ref::val_real(); }A suggestion to make this kind of bugs a bit easier to avoid in the future:
In Item_direct_ref, we should keep all val methods the same as we have in Item_ref, including adding a comment what we are overloading.
I have attached a "fixed" patch to this Jira entry with the above changes. Feel free to push it after you have checked and agreed to my changes.