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

View is created with incorrect definition and returns wrong results

    XMLWordPrintable

    Details

      Description

      CREATE TABLE t1 (a CHAR(3));
      INSERT INTO t1 VALUES ('abc'),('bar');
       
      CREATE TABLE t2 (b CHAR(3));
      INSERT INTO t2 VALUES ('foo'),('qux');
       
      SELECT 1 IN ( a >= SOME ( SELECT b FROM t2 ) ) AS f FROM t1;
      CREATE VIEW v1 AS SELECT 1 IN ( a >= SOME ( SELECT b FROM t2 ) ) AS f FROM t1;
      SELECT * FROM v1;
       
      SHOW CREATE VIEW v1;
       
      # Cleanup
      DROP VIEW v1;
      DROP TABLE t1;
      

      10.4 9b5cdeeb

      MariaDB [bug]> SELECT 1 IN ( a >= SOME ( SELECT b FROM t2 ) ) AS f FROM t1;
      +------+
      | f    |
      +------+
      |    0 |
      |    0 |
      +------+
      2 rows in set (0.001 sec)
      

      MariaDB [bug]> SELECT * FROM v1;
      +------+
      | f    |
      +------+
      |    1 |
      |    1 |
      +------+
      2 rows in set, 5 warnings (0.001 sec)
       
      MariaDB [bug]> show warnings;
      +---------+------+-----------------------------------------+
      | Level   | Code | Message                                 |
      +---------+------+-----------------------------------------+
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'abc' |
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'abc' |
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'abc' |
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'bar' |
      +---------+------+-----------------------------------------+
      5 rows in set (0.000 sec)
      

      Looking at the view description, the result is not surprising:

      MariaDB [bug]> SHOW CREATE VIEW v1 \G
      *************************** 1. row ***************************
                      View: v1
               Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 = `t1`.`a` >= any (select `t2`.`b` from `t2`) AS `f` from `t1`
      character_set_client: utf8
      collation_connection: utf8_general_ci
      1 row in set (0.000 sec)
      

      Reproducible on all MariaDB and MySQL versions, including 8.0 (although I don't have the latest 8.0).

        Attachments

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated: