Details
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+.
Attachments
Issue Links
- is duplicated by
-
MDEV-13460 Different result while executing a query directly and via a view
-
- Closed
-
-
-
- Closed
-