[MDEV-21388] Wrong result of DAYNAME()=xxx in combination with condition_pushdown_for_derived=on - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
Fix Version/s: 10.5
Component/s: Optimizer, Views
Labels:
None

Description

I create two tables and a view as follows:

DROP TABLE IF EXISTS t1, t2;

DROP VIEW IF EXISTS v1;

CREATE TABLE t1 (a INT, b DATE, c INT);

INSERT INTO t1 VALUES

  (1,'2001-01-21',345),

  (6,'2001-01-20',315),

  (6,'2001-01-20',214);

CREATE TABLE t2 (a INT, b INT);

INSERT INTO t2 VALUES (2,19), (7,20);

CREATE VIEW v1 AS SELECT a, b, max(c) AS max_c FROM t1

  GROUP BY a,b HAVING max_c < 707;

and run this query:

SELECT *, dayname(v1.b) FROM v1,t2 WHERE (v1.max_c>214) AND (t2.a>v1.a);

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

| a    | b          | max_c | a    | b    | dayname(v1.b) |

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

|    1 | 2001-01-21 |   345 |    2 |   19 | Sunday        |

|    1 | 2001-01-21 |   345 |    7 |   20 | Sunday        |

|    6 | 2001-01-20 |   315 |    7 |   20 | Saturday      |

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

So far so good.

Now I add and extra condition to select only Sunday records:

SET optimizer_switch='condition_pushdown_for_derived=off';

SELECT *, dayname(v1.b) FROM v1,t2 WHERE (v1.max_c>214) AND (t2.a>v1.a) AND dayname(v1.b)='Sunday';

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

| a    | b          | max_c | a    | b    | dayname(v1.b) |

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

|    1 | 2001-01-21 |   345 |    2 |   19 | Sunday        |

|    1 | 2001-01-21 |   345 |    7 |   20 | Sunday        |

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

Everything is still fine.

Now I change the optimizer switch and run the same query:

SET optimizer_switch='condition_pushdown_for_derived=on';

SELECT *, dayname(v1.b) FROM v1,t2 WHERE (v1.max_c>214) AND (t2.a>v1.a) AND dayname(v1.b)='Sunday';

It retuns empty set with the following warning:

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

| Level   | Code | Message                                    |

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

| Warning | 1292 | Truncated incorrect DOUBLE value: 'Sunday' |

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

The result of the last query looks wrong. It should return two records, like the previous query does.

Attachments

Issue Links

relates to

MDEV-9197 Pushdown conditions into non-mergeable views/derived tables

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2019-12-24 09:24

Alexander Barkov made changes - 2019-12-24 09:24

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-9197~~ [ ~~MDEV-9197~~ ]

Alexander Barkov made changes - 2019-12-24 10:03

Description

I create two tables and a view as follows:
{code:sql}
DROP TABLE IF EXISTS t1, t2;
DROP VIEW IF EXISTS v1;

CREATE TABLE t1 (a INT, b DATE, c INT);
INSERT INTO t1 VALUES
  (1,'2001-01-21',345),
  (6,'2001-01-20',315),
  (6,'2001-01-20',214);

CREATE TABLE t2 (a INT, b INT);
INSERT INTO t2 VALUES (2,19), (7,20);
CREATE VIEW v1 AS SELECT a, b, max(c) AS max_c FROM t1
  GROUP BY a,b HAVING max_c < 707;
{noformat}

and run this query:

{code:sql}
SELECT *, dayname(v1.b) FROM v1,t2 WHERE (v1.max_c>214) AND (t2.a>v1.a);
{code}
{noformat}
+------+------------+-------+------+------+---------------+
| a | b | max_c | a | b | dayname(v1.b) |
+------+------------+-------+------+------+---------------+
| 1 | 2001-01-21 | 345 | 2 | 19 | Sunday |
| 1 | 2001-01-21 | 345 | 7 | 20 | Sunday |
| 6 | 2001-01-20 | 315 | 7 | 20 | Saturday |
+------+------------+-------+------+------+---------------+
{noformat}

So far so good.

Now I add and extra condition to select only Sunday records:

{code:sql}
SET optimizer_switch='condition_pushdown_for_derived=off';
SELECT *, dayname(v1.b) FROM v1,t2 WHERE (v1.max_c>214) AND (t2.a>v1.a) AND dayname(v1.b)='Sunday';
{code}
{noformat}
+------+------------+-------+------+------+---------------+
| a | b | max_c | a | b | dayname(v1.b) |
+------+------------+-------+------+------+---------------+
| 1 | 2001-01-21 | 345 | 2 | 19 | Sunday |
| 1 | 2001-01-21 | 345 | 7 | 20 | Sunday |
+------+------------+-------+------+------+---------------+
{noformat}
Everything is still fine.

Now I change the optimizer switch and run the same query:
{code:sql}
SET optimizer_switch='condition_pushdown_for_derived=on';
SELECT *, dayname(v1.b) FROM v1,t2 WHERE (v1.max_c>214) AND (t2.a>v1.a) AND dayname(v1.b)='Sunday';
{code}
It retuns empty set with the following warning:
{noformat}
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Sunday' |
+---------+------+--------------------------------------------+
{noformat}

The result of the last query looks wrong. It should return two records, like the previous query does.

I create two tables and a view as follows:
{code:sql}
DROP TABLE IF EXISTS t1, t2;
DROP VIEW IF EXISTS v1;

CREATE TABLE t1 (a INT, b DATE, c INT);
INSERT INTO t1 VALUES
  (1,'2001-01-21',345),
  (6,'2001-01-20',315),
  (6,'2001-01-20',214);

CREATE TABLE t2 (a INT, b INT);
INSERT INTO t2 VALUES (2,19), (7,20);
CREATE VIEW v1 AS SELECT a, b, max(c) AS max_c FROM t1
  GROUP BY a,b HAVING max_c < 707;
{code}

and run this query:

{code:sql}
SELECT *, dayname(v1.b) FROM v1,t2 WHERE (v1.max_c>214) AND (t2.a>v1.a);
{code}
{noformat}
+------+------------+-------+------+------+---------------+
| a | b | max_c | a | b | dayname(v1.b) |
+------+------------+-------+------+------+---------------+
| 1 | 2001-01-21 | 345 | 2 | 19 | Sunday |
| 1 | 2001-01-21 | 345 | 7 | 20 | Sunday |
| 6 | 2001-01-20 | 315 | 7 | 20 | Saturday |
+------+------------+-------+------+------+---------------+
{noformat}

So far so good.

Now I add and extra condition to select only Sunday records:

{code:sql}
SET optimizer_switch='condition_pushdown_for_derived=off';
SELECT *, dayname(v1.b) FROM v1,t2 WHERE (v1.max_c>214) AND (t2.a>v1.a) AND dayname(v1.b)='Sunday';
{code}
{noformat}
+------+------------+-------+------+------+---------------+
| a | b | max_c | a | b | dayname(v1.b) |
+------+------------+-------+------+------+---------------+
| 1 | 2001-01-21 | 345 | 2 | 19 | Sunday |
| 1 | 2001-01-21 | 345 | 7 | 20 | Sunday |
+------+------------+-------+------+------+---------------+
{noformat}
Everything is still fine.

Now I change the optimizer switch and run the same query:
{code:sql}
SET optimizer_switch='condition_pushdown_for_derived=on';
SELECT *, dayname(v1.b) FROM v1,t2 WHERE (v1.max_c>214) AND (t2.a>v1.a) AND dayname(v1.b)='Sunday';
{code}
It retuns empty set with the following warning:
{noformat}
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Sunday' |
+---------+------+--------------------------------------------+
{noformat}

The result of the last query looks wrong. It should return two records, like the previous query does.

Sergei Golubchik made changes - 2020-01-23 16:21

Fix Version/s		10.3 [ 22126 ]
Fix Version/s	10.2.31 [ 24017 ]

Sergei Golubchik made changes - 2021-12-06 21:34

Workflow

MariaDB v3 [ 102653 ]

MariaDB v4 [ 141711 ]

Ralf Gebhardt made changes - 2022-08-04 08:41

Fix Version/s

10.2 [ 14601 ]

Julien Fritsch made changes - 2023-04-27 13:57

Fix Version/s

10.3 [ 22126 ]

Julien Fritsch made changes - 2024-09-10 15:05

Fix Version/s

10.4 [ 22408 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2019-12-24 09:24

Updated:: 2024-09-10 15:05

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration