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

XML

Word

Printable

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.

relates to

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

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