[MDEV-23022] Incorrect query result when NOT inside BETWEEN is used in VIEW Created: 2020-06-26  Updated: 2022-06-23  Resolved: 2022-06-23

Status: Closed
Project: MariaDB Server
Component/s: Server, Views
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.3.36, 10.4.26, 10.5.17, 10.6.9, 10.7.5, 10.8.4, 10.9.2

Type: Bug Priority: Critical
Reporter: Sergei Petrunia Assignee: Oleksandr Byelkin
Resolution: Cannot Reproduce Votes: 0
Labels: None

Issue Links:
Problem/Incident

 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?

 Comments   
Comment by Sergei Petrunia [ 2020-06-26 ]

I was using 10.4 but probably eariler versions are affected as well.

Comment by Oleksandr Byelkin [ 2020-06-30 ]

Printing of an expression looks like this:

`a` = 0 between 1000 and 2000

(taken from debug)

Comment by Oleksandr Byelkin [ 2020-06-30 ]

probably problem is here starting from 10.2 where print_parenthesised was introduced.

Comment by Oleksandr Byelkin [ 2020-06-30 ]

And as far as I can see :

  BETWEEN_PRECEDENCE,   // BETWEEN, CASE, WHEN, THEN, ELSE
 
  CMP_PRECEDENCE,       // =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN

and so 

(`a` = 0) between 1000 and 2000

and 

`a` = 0 between 1000 and 2000

shoud be equal

Comment by Oleksandr Byelkin [ 2020-06-30 ]

Normal test suite

create table t0 (a int);
 
insert into t0 values (0);
 
 
 
create view v0 as 
select * from t0 where (a=0) between 1000 and 2000;
 
 
 
show create view v0;
 
 
 
drop view v0;
 
drop table t0;

Comment by Oleksandr Byelkin [ 2020-06-30 ]

10.1 is OK.

So problem is that according to print rules "=" has higher priority then BETWEEN but it is contradict reality of our parser and following:

`a` = 0 between 1000 and 2000

parsed to

`a` = (0 between 1000 and 2000)

Comment by Oleksandr Byelkin [ 2022-06-23 ]

fixed already:

create table t0 (a int);
 
insert into t0 values (0);
 
create view v0 as 
select * from t0 where (a=0) between 1000 and 2000;
 
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
 
select * from v0;
 
a
 
select * from t0 where (a=0) between 1000 and 2000;
 
a
 
drop view v0;
 
drop table t0;

Generated at Thu Feb 08 09:19:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.