[MDEV-32107] Unexpected Results by table_elimination Created: 2023-09-06  Updated: 2023-09-06

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5.22, 11.1.2
Fix Version/s: 10.5

Type: Bug Priority: Major
Reporter: Jinsheng Ba Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: not-10.4
Environment:

docker run -it -p 3307:3306 -e MARIADB_ROOT_PASSWORD=root mariadb:latest


 Description   

The same query returns different results if changing table_elimination:

CREATE TABLE t0(c0 FLOAT);
 
CREATE TABLE t1(c0 FLOAT);
 
INSERT INTO t0 VALUES (DEFAULT), (DEFAULT);
 
INSERT INTO t1 VALUES (1);
 
CREATE UNIQUE INDEX ic0 USING BTREE ON t0(c0 ASC);
 
 
 
SELECT t1.c0 FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL); -- {1}
 
SET SESSION optimizer_switch = 'table_elimination=off';
 
SELECT t1.c0 FROM t1 LEFT JOIN t0 ON (t0.c0 IS NULL); -- {1}, {1}


Generated at Thu Feb 08 10:28:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.