Bug #105200 Incorrect query result

• Submitted: 13 Oct 2021 7:12
• Modified: 14 Oct 2021 19:32
• Reporter: linfeng chen
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.20, 8.0.26
• OS: Any
• CPU Architecture: Any
• Tags: Incorrect query result

[13 Oct 2021 7:12] linfeng chen

Description:
The query result is incorrect after data is imported

How to repeat:
Create a test case that looks like this

--source include/have_debug.inc
--let $MYSQLD_DATADIR=`select @@datadir`
--let $DB = `SELECT DATABASE()`
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b DESC));
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 VALUES (2, 2);

DROP PROCEDURE IF EXISTS data_load_t1;
delimiter |;
CREATE PROCEDURE data_load_t1()
BEGIN
  DECLARE c1 INT DEFAULT 3;
  WHILE c1 < 1000 DO
   INSERT INTO t1 VALUES (c1, c1);
   SET c1 = c1 + 1;
  END WHILE;
END |
delimiter ;|
call data_load_t1();
DROP PROCEDURE data_load_t1;

SHOW CREATE TABLE t1;
SET GLOBAL DEBUG='+d,ib_export_use_cfg_version_3';
FLUSH TABLES t1 FOR EXPORT;
SET GLOBAL DEBUG='-d,ib_export_use_cfg_version_3';
--copy_file $MYSQLD_DATADIR/$DB/t1.ibd $MYSQLD_DATADIR/$DB/t1.ibd.descend
--copy_file $MYSQLD_DATADIR/$DB/t1.cfg $MYSQLD_DATADIR/$DB/t1.cfg.descend.v3
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b));
SHOW CREATE TABLE t1;
ALTER TABLE t1 DISCARD TABLESPACE;
--copy_file $MYSQLD_DATADIR/$DB/t1.ibd.descend $MYSQLD_DATADIR/$DB/t1.ibd
--copy_file $MYSQLD_DATADIR/$DB/t1.cfg.descend.v3 $MYSQLD_DATADIR/$DB/t1.cfg
ALTER TABLE t1 IMPORT TABLESPACE;

#No data was found

SELECT * FROM t1 where b < 3 and b > 0;
explain SELECT * FROM t1 where b < 3 and b > 0;
DROP TABLE t1;
--remove_file $MYSQLD_DATADIR/$DB/t1.ibd.descend
--remove_file $MYSQLD_DATADIR/$DB/t1.cfg.descend.v3

Suggested fix:
Hope it can be repaired soon.

[13 Oct 2021 7:23] linfeng chen

Use the DEBUG version to reproduce the problem

[13 Oct 2021 7:28] linfeng chen

The query result is incorrect
SELECT * FROM t1 where b < 3 and b > 0;

[13 Oct 2021 7:49] MySQL Verification Team

Hell linfeng chen,

Thank you for the report and test case.

regards,
Umesh

[14 Oct 2021 19:32] Roy Lyseng

Posted by developer:
 
A debug flag is used to trick the server into reading a .cfg file that is not aware of descending indexes.
Clsoing as not a bug.