Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21388

Wrong result of DAYNAME()=xxx in combination with condition_pushdown_for_derived=on

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: