Description:
The results obtained by executing the query using a view are incorrect, possibly due to the loss of the offset condition in the subquery, and it can reproduce in 8.0.30, 8.4.4
How to repeat:
1. create tables with `source schema.sql`
2. init datas with `source dt.sql`
3. directly execute query
```
SELECT count(1)
FROM `shardkey_list_1`
WHERE EXISTS
(SELECT *
FROM
(SELECT *,
`col_mediumtext` AS c302
FROM
(SELECT *
FROM
(SELECT DISTINCT *
FROM `shardkey_2`
LIMIT 83) AS t
WHERE `col_set` < 76
) AS t
WHERE `col_binary_8` IN
(SELECT `col_binary_8`
FROM `noshard_2`
WHERE `col_smallint` IS NULL)
) AS t
LIMIT 178 OFFSET 9);
```
will got
```
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
```
4. execute with created view
```
create view v1 as SELECT count(1)
FROM `shardkey_list_1`
WHERE EXISTS
(SELECT *
FROM
(SELECT *,
`col_mediumtext` AS c302
FROM
(SELECT *
FROM
(SELECT DISTINCT *
FROM `shardkey_2`
LIMIT 83) AS t
WHERE `col_set` < 76
) AS t
WHERE `col_binary_8` IN
(SELECT `col_binary_8`
FROM `noshard_2`
WHERE `col_smallint` IS NULL)
) AS t
LIMIT 178 OFFSET 9);
select * from v1;
```
but will got
```
+----------+
| count(1) |
+----------+
| 1000 |
+----------+
1 row in set (0.01 sec)
```
5. The `SHOW CREATE VIEW` command reveals that the `OFFSET` clause has been lost.
```
show create view v1\G
*************************** 1. row ***************************
View: v1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v1` AS select count(1) AS `count(1)` from `shardkey_list_1` where exists(select 1 from (select `t`.`col_int_auto_not_null` AS `col_int_auto_not_null`,`t`.`col_pk_char` AS `col_pk_char`,`t`.`col_pk_varchar` AS `col_pk_varchar`,`t`.`col_pk_date` AS `col_pk_date`,`t`.`col_int` AS `col_int`,`t`.`col_int__1` AS `col_int__1`,`t`.`col_int_8` AS `col_int_8`,`t`.`col_int_8__1` AS `col_int_8__1`,`t`.`col_bigint` AS `col_bigint`,`t`.`col_smallint` AS `col_smallint`,`t`.`col_tinyint` AS `col_tinyint`,`t`.`col_float` AS `col_float`,`t`.`col_double` AS `col_double`,`t`.`col_decimal` AS `col_decimal`,`t`.`col_bit` AS `col_bit`,`t`.`col_bit__1` AS `col_bit__1`,`t`.`col_enum` AS `col_enum`,`t`.`col_set` AS `col_set`,`t`.`col_char_2` AS `col_char_2`,`t`.`col_char_255` AS `col_char_255`,`t`.`col_varchar_1` AS `col_varchar_1`,`t`.`col_varchar_64` AS `col_varchar_64`,`t`.`col_varchar_1024` AS `col_varchar_1024`,`t`.`col_binary_8` AS `col_binary_8`,`t`.`col_varbinary_8` AS `col_varbinary_8`,`t`.`col_date` AS `col_date`,`t`.`col_time` AS `col_time`,`t`.`col_datetime` AS `col_datetime`,`t`.`col_timestamp` AS `col_timestamp`,`t`.`col_year` AS `col_year`,`t`.`col_text` AS `col_text`,`t`.`col_bool` AS `col_bool`,`t`.`col_boolean` AS `col_boolean`,`t`.`col_longtext` AS `col_longtext`,`t`.`col_mediumtext` AS `col_mediumtext`,`t`.`col_tinyblob` AS `col_tinyblob`,`t`.`col_mediumblob` AS `col_mediumblob`,`t`.`col_longblob` AS `col_longblob`,`t`.`col_blob` AS `col_blob`,`t`.`col_mediumtext` AS `c302` from (select `t`.`col_int_auto_not_null` AS `col_int_auto_not_null`,`t`.`col_pk_char` AS `col_pk_char`,`t`.`col_pk_varchar` AS `col_pk_varchar`,`t`.`col_pk_date` AS `col_pk_date`,`t`.`col_int` AS `col_int`,`t`.`col_int__1` AS `col_int__1`,`t`.`col_int_8` AS `col_int_8`,`t`.`col_int_8__1` AS `col_int_8__1`,`t`.`col_bigint` AS `col_bigint`,`t`.`col_smallint` AS `col_smallint`,`t`.`col_tinyint` AS `col_tinyint`,`t`.`col_float` AS `col_float`,`t`.`col_double` AS `col_double`,`t`.`col_decimal` AS `col_decimal`,`t`.`col_bit` AS `col_bit`,`t`.`col_bit__1` AS `col_bit__1`,`t`.`col_enum` AS `col_enum`,`t`.`col_set` AS `col_set`,`t`.`col_char_2` AS `col_char_2`,`t`.`col_char_255` AS `col_char_255`,`t`.`col_varchar_1` AS `col_varchar_1`,`t`.`col_varchar_64` AS `col_varchar_64`,`t`.`col_varchar_1024` AS `col_varchar_1024`,`t`.`col_binary_8` AS `col_binary_8`,`t`.`col_varbinary_8` AS `col_varbinary_8`,`t`.`col_date` AS `col_date`,`t`.`col_time` AS `col_time`,`t`.`col_datetime` AS `col_datetime`,`t`.`col_timestamp` AS `col_timestamp`,`t`.`col_year` AS `col_year`,`t`.`col_text` AS `col_text`,`t`.`col_bool` AS `col_bool`,`t`.`col_boolean` AS `col_boolean`,`t`.`col_longtext` AS `col_longtext`,`t`.`col_mediumtext` AS `col_mediumtext`,`t`.`col_tinyblob` AS `col_tinyblob`,`t`.`col_mediumblob` AS `col_mediumblob`,`t`.`col_longblob` AS `col_longblob`,`t`.`col_blob` AS `col_blob` from (select distinct `shardkey_2`.`col_int_auto_not_null` AS `col_int_auto_not_null`,`shardkey_2`.`col_pk_char` AS `col_pk_char`,`shardkey_2`.`col_pk_varchar` AS `col_pk_varchar`,`shardkey_2`.`col_pk_date` AS `col_pk_date`,`shardkey_2`.`col_int` AS `col_int`,`shardkey_2`.`col_int__1` AS `col_int__1`,`shardkey_2`.`col_int_8` AS `col_int_8`,`shardkey_2`.`col_int_8__1` AS `col_int_8__1`,`shardkey_2`.`col_bigint` AS `col_bigint`,`shardkey_2`.`col_smallint` AS `col_smallint`,`shardkey_2`.`col_tinyint` AS `col_tinyint`,`shardkey_2`.`col_float` AS `col_float`,`shardkey_2`.`col_double` AS `col_double`,`shardkey_2`.`col_decimal` AS `col_decimal`,`shardkey_2`.`col_bit` AS `col_bit`,`shardkey_2`.`col_bit__1` AS `col_bit__1`,`shardkey_2`.`col_enum` AS `col_enum`,`shardkey_2`.`col_set` AS `col_set`,`shardkey_2`.`col_char_2` AS `col_char_2`,`shardkey_2`.`col_char_255` AS `col_char_255`,`shardkey_2`.`col_varchar_1` AS `col_varchar_1`,`shardkey_2`.`col_varchar_64` AS `col_varchar_64`,`shardkey_2`.`col_varchar_1024` AS `col_varchar_1024`,`shardkey_2`.`col_binary_8` AS `col_binary_8`,`shardkey_2`.`col_varbinary_8` AS `col_varbinary_8`,`shardkey_2`.`col_date` AS `col_date`,`shardkey_2`.`col_time` AS `col_time`,`shardkey_2`.`col_datetime` AS `col_datetime`,`shardkey_2`.`col_timestamp` AS `col_timestamp`,`shardkey_2`.`col_year` AS `col_year`,`shardkey_2`.`col_text` AS `col_text`,`shardkey_2`.`col_bool` AS `col_bool`,`shardkey_2`.`col_boolean` AS `col_boolean`,`shardkey_2`.`col_longtext` AS `col_longtext`,`shardkey_2`.`col_mediumtext` AS `col_mediumtext`,`shardkey_2`.`col_tinyblob` AS `col_tinyblob`,`shardkey_2`.`col_mediumblob` AS `col_mediumblob`,`shardkey_2`.`col_longblob` AS `col_longblob`,`shardkey_2`.`col_blob` AS `col_blob` from `shardkey_2` limit 83) `t` where (`t`.`col_set` < 76)) `t` where `t`.`col_binary_8` in (select `noshard_2`.`col_binary_8` from `noshard_2` where (`noshard_2`.`col_smallint` is null))) `t`)
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
```
Description: The results obtained by executing the query using a view are incorrect, possibly due to the loss of the offset condition in the subquery, and it can reproduce in 8.0.30, 8.4.4 How to repeat: 1. create tables with `source schema.sql` 2. init datas with `source dt.sql` 3. directly execute query ``` SELECT count(1) FROM `shardkey_list_1` WHERE EXISTS (SELECT * FROM (SELECT *, `col_mediumtext` AS c302 FROM (SELECT * FROM (SELECT DISTINCT * FROM `shardkey_2` LIMIT 83) AS t WHERE `col_set` < 76 ) AS t WHERE `col_binary_8` IN (SELECT `col_binary_8` FROM `noshard_2` WHERE `col_smallint` IS NULL) ) AS t LIMIT 178 OFFSET 9); ``` will got ``` +----------+ | count(1) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) ``` 4. execute with created view ``` create view v1 as SELECT count(1) FROM `shardkey_list_1` WHERE EXISTS (SELECT * FROM (SELECT *, `col_mediumtext` AS c302 FROM (SELECT * FROM (SELECT DISTINCT * FROM `shardkey_2` LIMIT 83) AS t WHERE `col_set` < 76 ) AS t WHERE `col_binary_8` IN (SELECT `col_binary_8` FROM `noshard_2` WHERE `col_smallint` IS NULL) ) AS t LIMIT 178 OFFSET 9); select * from v1; ``` but will got ``` +----------+ | count(1) | +----------+ | 1000 | +----------+ 1 row in set (0.01 sec) ``` 5. The `SHOW CREATE VIEW` command reveals that the `OFFSET` clause has been lost. ``` show create view v1\G *************************** 1. row *************************** View: v1 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v1` AS select count(1) AS `count(1)` from `shardkey_list_1` where exists(select 1 from (select `t`.`col_int_auto_not_null` AS `col_int_auto_not_null`,`t`.`col_pk_char` AS `col_pk_char`,`t`.`col_pk_varchar` AS `col_pk_varchar`,`t`.`col_pk_date` AS `col_pk_date`,`t`.`col_int` AS `col_int`,`t`.`col_int__1` AS `col_int__1`,`t`.`col_int_8` AS `col_int_8`,`t`.`col_int_8__1` AS `col_int_8__1`,`t`.`col_bigint` AS `col_bigint`,`t`.`col_smallint` AS `col_smallint`,`t`.`col_tinyint` AS `col_tinyint`,`t`.`col_float` AS `col_float`,`t`.`col_double` AS `col_double`,`t`.`col_decimal` AS `col_decimal`,`t`.`col_bit` AS `col_bit`,`t`.`col_bit__1` AS `col_bit__1`,`t`.`col_enum` AS `col_enum`,`t`.`col_set` AS `col_set`,`t`.`col_char_2` AS `col_char_2`,`t`.`col_char_255` AS `col_char_255`,`t`.`col_varchar_1` AS `col_varchar_1`,`t`.`col_varchar_64` AS `col_varchar_64`,`t`.`col_varchar_1024` AS `col_varchar_1024`,`t`.`col_binary_8` AS `col_binary_8`,`t`.`col_varbinary_8` AS `col_varbinary_8`,`t`.`col_date` AS `col_date`,`t`.`col_time` AS `col_time`,`t`.`col_datetime` AS `col_datetime`,`t`.`col_timestamp` AS `col_timestamp`,`t`.`col_year` AS `col_year`,`t`.`col_text` AS `col_text`,`t`.`col_bool` AS `col_bool`,`t`.`col_boolean` AS `col_boolean`,`t`.`col_longtext` AS `col_longtext`,`t`.`col_mediumtext` AS `col_mediumtext`,`t`.`col_tinyblob` AS `col_tinyblob`,`t`.`col_mediumblob` AS `col_mediumblob`,`t`.`col_longblob` AS `col_longblob`,`t`.`col_blob` AS `col_blob`,`t`.`col_mediumtext` AS `c302` from (select `t`.`col_int_auto_not_null` AS `col_int_auto_not_null`,`t`.`col_pk_char` AS `col_pk_char`,`t`.`col_pk_varchar` AS `col_pk_varchar`,`t`.`col_pk_date` AS `col_pk_date`,`t`.`col_int` AS `col_int`,`t`.`col_int__1` AS `col_int__1`,`t`.`col_int_8` AS `col_int_8`,`t`.`col_int_8__1` AS `col_int_8__1`,`t`.`col_bigint` AS `col_bigint`,`t`.`col_smallint` AS `col_smallint`,`t`.`col_tinyint` AS `col_tinyint`,`t`.`col_float` AS `col_float`,`t`.`col_double` AS `col_double`,`t`.`col_decimal` AS `col_decimal`,`t`.`col_bit` AS `col_bit`,`t`.`col_bit__1` AS `col_bit__1`,`t`.`col_enum` AS `col_enum`,`t`.`col_set` AS `col_set`,`t`.`col_char_2` AS `col_char_2`,`t`.`col_char_255` AS `col_char_255`,`t`.`col_varchar_1` AS `col_varchar_1`,`t`.`col_varchar_64` AS `col_varchar_64`,`t`.`col_varchar_1024` AS `col_varchar_1024`,`t`.`col_binary_8` AS `col_binary_8`,`t`.`col_varbinary_8` AS `col_varbinary_8`,`t`.`col_date` AS `col_date`,`t`.`col_time` AS `col_time`,`t`.`col_datetime` AS `col_datetime`,`t`.`col_timestamp` AS `col_timestamp`,`t`.`col_year` AS `col_year`,`t`.`col_text` AS `col_text`,`t`.`col_bool` AS `col_bool`,`t`.`col_boolean` AS `col_boolean`,`t`.`col_longtext` AS `col_longtext`,`t`.`col_mediumtext` AS `col_mediumtext`,`t`.`col_tinyblob` AS `col_tinyblob`,`t`.`col_mediumblob` AS `col_mediumblob`,`t`.`col_longblob` AS `col_longblob`,`t`.`col_blob` AS `col_blob` from (select distinct `shardkey_2`.`col_int_auto_not_null` AS `col_int_auto_not_null`,`shardkey_2`.`col_pk_char` AS `col_pk_char`,`shardkey_2`.`col_pk_varchar` AS `col_pk_varchar`,`shardkey_2`.`col_pk_date` AS `col_pk_date`,`shardkey_2`.`col_int` AS `col_int`,`shardkey_2`.`col_int__1` AS `col_int__1`,`shardkey_2`.`col_int_8` AS `col_int_8`,`shardkey_2`.`col_int_8__1` AS `col_int_8__1`,`shardkey_2`.`col_bigint` AS `col_bigint`,`shardkey_2`.`col_smallint` AS `col_smallint`,`shardkey_2`.`col_tinyint` AS `col_tinyint`,`shardkey_2`.`col_float` AS `col_float`,`shardkey_2`.`col_double` AS `col_double`,`shardkey_2`.`col_decimal` AS `col_decimal`,`shardkey_2`.`col_bit` AS `col_bit`,`shardkey_2`.`col_bit__1` AS `col_bit__1`,`shardkey_2`.`col_enum` AS `col_enum`,`shardkey_2`.`col_set` AS `col_set`,`shardkey_2`.`col_char_2` AS `col_char_2`,`shardkey_2`.`col_char_255` AS `col_char_255`,`shardkey_2`.`col_varchar_1` AS `col_varchar_1`,`shardkey_2`.`col_varchar_64` AS `col_varchar_64`,`shardkey_2`.`col_varchar_1024` AS `col_varchar_1024`,`shardkey_2`.`col_binary_8` AS `col_binary_8`,`shardkey_2`.`col_varbinary_8` AS `col_varbinary_8`,`shardkey_2`.`col_date` AS `col_date`,`shardkey_2`.`col_time` AS `col_time`,`shardkey_2`.`col_datetime` AS `col_datetime`,`shardkey_2`.`col_timestamp` AS `col_timestamp`,`shardkey_2`.`col_year` AS `col_year`,`shardkey_2`.`col_text` AS `col_text`,`shardkey_2`.`col_bool` AS `col_bool`,`shardkey_2`.`col_boolean` AS `col_boolean`,`shardkey_2`.`col_longtext` AS `col_longtext`,`shardkey_2`.`col_mediumtext` AS `col_mediumtext`,`shardkey_2`.`col_tinyblob` AS `col_tinyblob`,`shardkey_2`.`col_mediumblob` AS `col_mediumblob`,`shardkey_2`.`col_longblob` AS `col_longblob`,`shardkey_2`.`col_blob` AS `col_blob` from `shardkey_2` limit 83) `t` where (`t`.`col_set` < 76)) `t` where `t`.`col_binary_8` in (select `noshard_2`.`col_binary_8` from `noshard_2` where (`noshard_2`.`col_smallint` is null))) `t`) character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci ```