MySQL Bugs: #111355: Derived condition pushdown return wrong results

Bug #111355	Derived condition pushdown return wrong results
Submitted:	12 Jun 2023 2:44	Modified:	4 Feb 14:45
Reporter:	hel le	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.29+	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Derived condition pushdown return wrong results with view.

How to repeat:
CREATE TABLE `t1` (
  `c_id` int(255) unsigned zerofill NOT NULL
) ENGINE=InnoDB;
INSERT INTO `t1` VALUES (298);

CREATE TABLE `t2` (
  `c_id` int(255) unsigned zerofill NOT NULL,
  `c_first` varchar(255) DEFAULT NULL,
  `c_discount` decimal(62,4) DEFAULT NULL,
  `c_ytd_payment` decimal(62,2) DEFAULT NULL,
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB;

CREATE TABLE `t3` (
  `C_BIT` bit(1) DEFAULT NULL
) ENGINE=InnoDB;

INSERT INTO `t3` VALUES (NULL);

create view tx_cmplx_003 as select subq_1.c1 as c0, subq_1.c0 as c1, subq_1.c0 as c2, subq_1.c0 as c3, subq_1.c0 as c4, subq_1.c0 as c5, subq_1.c1 as c6, subq_1.c1 as c7, subq_1.c0 as c8, subq_1.c1 as c9, subq_1.c0 as c10, subq_1.c0 as c11 from (select pi() as c0, subq_0.c1 as c1 from t3 as ref_0 left join (select ref_1.c_ytd_payment as c0, pi() as c1, ref_1.c_discount as c2 from t2 as ref_1 where ref_1.c_first is NULL ) as subq_0 on (subq_0.c0 is not NULL) where true ) as subq_1 where true group by 1,2,3,4,5,6,7,8,9,10,11,12 order by 1,2,3,4,5,6,7,8,9,10,11,12;

mysql> set optimizer_switch="derived_condition_pushdown=on";
Query OK, 0 rows affected (0.00 sec)

mysql> select 1 as c0 from t1 as ref_4 inner join tx_cmplx_003 as ref_5 where (ref_5.c6 is not NULL);
+----+
| c0 |
+----+
|  1 |
+----+
1 row in set, 12 warnings (0.00 sec)

mysql> set optimizer_switch="derived_condition_pushdown=off";
Query OK, 0 rows affected (0.00 sec)

mysql> select 1 as c0 from t1 as ref_4 inner join tx_cmplx_003 as ref_5 where (ref_5.c6 is not NULL);
Empty set, 12 warnings (0.00 sec)

Hello hel le,

Thank you for the report and test case.

regards,
Umesh

- 8.0.33

 ./mtr bug111355 --nocheck-testcases
Logging: ./mtr  bug111355 --nocheck-testcases
MySQL Version 8.0.33
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
 - WARNING: Using the 'mysql-test/var' symlink
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.33/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE t1 (
c_id int(255) unsigned zerofill NOT NULL
) ENGINE=InnoDB;
Warnings:
Warning 1681    The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
Warning 1681    Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES (298);
CREATE TABLE t2 (
c_id int(255) unsigned zerofill NOT NULL,
c_first varchar(255) DEFAULT NULL,
c_discount decimal(62,4) DEFAULT NULL,
c_ytd_payment decimal(62,2) DEFAULT NULL,
PRIMARY KEY (c_id)
) ENGINE=InnoDB;
Warnings:
Warning 1681    The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
Warning 1681    Integer display width is deprecated and will be removed in a future release.
CREATE TABLE t3 (
C_BIT bit(1) DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO t3 VALUES (NULL);
create view tx_cmplx_003 as select subq_1.c1 as c0, subq_1.c0 as c1, subq_1.c0 as c2, subq_1.c0 as c3, subq_1.c0 as c4, subq_1.c0 as c5, subq_1.c1 as c6, subq_1.c1 as c7, subq_1.c0 as c8, subq_1.c1 as c9, subq_1.c0 as c10, subq_1.c0 as c11 from (select pi() as c0, subq_0.c1 as c1 from t3 as ref_0 left join (select ref_1.c_ytd_payment as c0, pi() as c1, ref_1.c_discount as c2 from t2 as ref_1 where ref_1.c_first is NULL ) as subq_0 on (subq_0.c0 is not NULL) where true ) as subq_1 where true group by 1,2,3,4,5,6,7,8,9,10,11,12 order by 1,2,3,4,5,6,7,8,9,10,11,12;
set optimizer_switch="derived_condition_pushdown=on";
select 1 as c0 from t1 as ref_4 inner join tx_cmplx_003 as ref_5 where (ref_5.c6 is not NULL);
c0
1
Warnings:
Warning 1052    Column 'c1' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c1' in group statement is ambiguous
Warning 1052    Column 'c1' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c1' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
show warnings;
Level   Code    Message
Warning 1052    Column 'c1' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c1' in group statement is ambiguous
Warning 1052    Column 'c1' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c1' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
set optimizer_switch="derived_condition_pushdown=off";
select 1 as c0 from t1 as ref_4 inner join tx_cmplx_003 as ref_5 where (ref_5.c6 is not NULL);
c0
Warnings:
Warning 1052    Column 'c1' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c1' in group statement is ambiguous
Warning 1052    Column 'c1' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c1' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous
Warning 1052    Column 'c0' in group statement is ambiguous

According to the release notes [1], this looks fixed in 8.2.  But nothing in the 8.0.35 RN [2], will this be fixed in 8.0 ?

[1]:https://dev.mysql.com/doc/relnotes/mysql/8.2/en/news-8-2-0.html

[2]: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-35.html

Also fixed in MySQL 8.0.42. 

Changelog entry as in previous. 

Closed.