| Bug #116895 | View using IN(SELECT LIMIT) has wrong result | ||
|---|---|---|---|
| Submitted: | 6 Dec 2024 11:45 | Modified: | 6 Dec 2024 14:30 |
| Reporter: | Guilhem Bichot | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 9.0,8.0, 8.0.40, 8.4.3 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[6 Dec 2024 11:54]
MySQL Verification Team
Hello Guilhem, Thank you for the report and test case. regards, Umesh
[6 Dec 2024 14:30]
Guilhem Bichot
Same problem if LIMIT 1,1 is replaced with LIMIT 0.
[25 Dec 2024 3:09]
huahua xu
The bug might be traced back to the commit: https://github.com/mysql/mysql-server/commit/cedf0269d5b698278067397392bd57633c1a66d5. @@ -2270,20 +2267,7 @@ void st_select_lex::print_limit(THD *thd, if (subs_type == Item_subselect::EXISTS_SUBS || subs_type == Item_subselect::IN_SUBS || subs_type == Item_subselect::ALL_SUBS) - { - DBUG_ASSERT(!item->fixed || - /* - If not using materialization both: - select_limit == 1, and there should be no offset_limit. - */ - (((subs_type == Item_subselect::IN_SUBS) && - ((Item_in_subselect*)item)->exec_method == - Item_in_subselect::EXEC_MATERIALIZATION) ? - TRUE : - (select_limit->val_int() == LL(1)) && - offset_limit == 0)); return; - } } if (explicit_limit) {
Description: verified with the latest: commit 61a3a1d8ef15512396b4c2af46e922a19bf2b174 (HEAD -> trunk, tag: mysql-cluster-9.1.0, tag: mysql-9.1.0, origin/trunk) Author: Georgi Kodinov <georgi.kodinov@oracle.com> Date: Fri Sep 20 13:31:13 2024 +0200 Bug#37083943: The V$OPTION component should not be collecting ... from github, and also present in 8.0.22. In How-to-repeat: the standalone SELECT returns no rows (correct), but when in a view it returns rows (incorrect). How to repeat: CREATE TABLE t1 (c1 int, c2 char(10)) ; INSERT INTO t1 VALUES (1, 'name1'); INSERT INTO t1 VALUES (2, 'name2'); SELECT a.c2 FROM t1 AS a WHERE EXISTS (SELECT 1 FROM t1 AS b WHERE b.c2 = a.c2 LIMIT 1,1); create view v as SELECT a.c2 FROM t1 AS a WHERE EXISTS (SELECT 1 FROM t1 AS b WHERE b.c2 = a.c2 LIMIT 1,1); select * from v; Suggested fix: The problem is in SELECT_LEX::print_limit() (used when printing the view's definition for storing in the data dictionary): void SELECT_LEX::print_limit(const THD *thd, String *str, enum_query_type query_type) { SELECT_LEX_UNIT *unit = master_unit(); Item_subselect *item = unit->item; if (item && unit->global_parameters() == this) { => Item_subselect::subs_type subs_type = item->substype(); if (subs_type == Item_subselect::EXISTS_SUBS || subs_type == Item_subselect::IN_SUBS || subs_type == Item_subselect::ALL_SUBS) return; } As a result the LIMIT clause of the subquery vanishes. Also visible if you do the standalone SELECT and then SHOW WARNINGS: LIMIT is missing from the shown query.