[#MDEV-20742] View is created with incorrect definition and returns wrong results

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).

[MDEV-20742] View is created with incorrect definition and returns wrong results Created: 2019-10-03 Updated: 2022-12-06 Resolved: 2022-12-06
Status:	Closed
Project:	MariaDB Server
Component/s:	Views
Affects Version/s:	5.5, 10.1, 10.2, 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

[MDEV-20742] View is created with incorrect definition and returns wrong results Created: 2019-10-03 Updated: 2022-12-06 Resolved: 2022-12-06