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

Incorrect query result when NOT inside BETWEEN is used in VIEW

    XMLWordPrintable

    Details

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

      Description

      create table t0 (a int);
      insert into t0 values (0);
      

      select * from t0 where (a=0) between 1000 and 2000;
      a
      

      Ok, we got empty result as the row doesn't match the WHERE.
      Now, let's try the same through a view:

      create view v0 as 
      select * from t0 where (a=0) between 1000 and 2000;
      select * from v0;
      a
      0
      

      This is incorrect.

      The cause can be seen here:

      show create view v0;
      View    Create View     character_set_client    collation_connection
      v0      CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v0` 
      AS select `t0`.`a` AS `a` from `t0` 
      where `t0`.`a` = (0 between 1000 and 2000)     latin1  latin1_swedish_ci
      

      The brackets are printed in a way that doesn't match the original WHERE clause. Do CMP_PRECEDENCE and BETWEEN_PRECENDENCE have wrong values?

        Attachments

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            psergey Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated: