[MDEV-30082] View definition losing brackets changes semantics of the query and causes wrong result Created: 2022-11-23  Updated: 2022-12-16  Resolved: 2022-12-02

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.3.38, 10.4.28, 10.5.19, 10.6.12, 10.7.8, 10.8.7, 10.9.5, 10.10.3

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-13460 Different result while executing a qu... Closed
is duplicated by MDEV-20742 View is created with incorrect defini... Closed

 Description    

CREATE TABLE t1 (a VARCHAR(1), b BOOL) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES ('u',1),('s',1);
 
 
 
SELECT * FROM t1 WHERE t1.b IN (t1.a <= ALL (SELECT 'a'));
 
CREATE VIEW v AS SELECT * FROM t1 WHERE t1.b IN (t1.a <= ALL (SELECT 'a'));
 
SELECT * FROM v;
 
SHOW CREATE VIEW v;
 
 
 
DROP VIEW v;
 
DROP TABLE t1;

The initial WHERE clause in the query makes sense: t1.a <= ALL (SELECT 'a') in the IN expression returns true or false, and boolean t1.b is compared to it.

But when the view is created, the brackets around the expression are omitted, and the whole query becomes very different:

10.10 bebe1939, SHOW CREATE VIEW

 
AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where `t1`.`b` = `t1`.`a` <= all (select 'a')

So, now the boolean t1.b is instead compared to the string t1.a, and the result is again compared to the string. It causes warnings about data type truncation and eventual wrong result.

10.3 f0820400

 
SELECT * FROM t1 WHERE t1.b IN (t1.a <= ALL (SELECT 'a'));
 
a	b
 
CREATE VIEW v AS SELECT * FROM t1 WHERE t1.b IN (t1.a <= ALL (SELECT 'a'));
 
SELECT * FROM v;
 
a	b
 
u	1
 
s	1
 
Warnings:
 
Warning	1292	Truncated incorrect DECIMAL value: 'u'
 
Warning	1292	Truncated incorrect DECIMAL value: 'a'
 
Warning	1292	Truncated incorrect DECIMAL value: 's'
 
SHOW CREATE VIEW v;
 
View	Create View	character_set_client	collation_connection
 
v	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where `t1`.`b` = `t1`.`a` <= all (select 'a')	latin1	latin1_swedish_ci

Reproducible on all of 10.3+.

 Comments   
Comment by Oleksandr Byelkin [ 2022-11-29 ]

I'd prefer do not have pure cosmetic changes in 10.3 if otherwise it is not changed:

   Item_func_not_all(THD *thd, Item *a):
 
-    Item_func_not(thd, a), test_sum_item(0), test_sub_item(0), show(0)
 
-    {}
 
+    Item_func_not(thd, a), test_sum_item(0), test_sub_item(0), show(0) {}
 
   table_map not_null_tables() const { return 0; }


   // only ALL subquery has upper not
 
-  subs_type substype() { return all?ALL_SUBS:ANY_SUBS; }
 
+  subs_type substype() { return all ? ALL_SUBS : ANY_SUBS; }
 
   bool select_transformer(JOIN *join);

Otherwise the patch is OK to push.

Generated at Thu Feb 08 10:13:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.