MySQL Bugs: #100453: Drop TABLESPACE of a table has inconsistent impact on the query

Bug #100453	Drop TABLESPACE of a table has inconsistent impact on the query
Submitted:	7 Aug 2020 12:14	Modified:	7 Aug 2020 15:52
Reporter:	Yushan ZHANG	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7.31	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, innodb, Tablespace

Description:
mysql> SELECT ALL col_smallint_signed FROM table_1_latin1_undef WHERE 1;                                                      +---------------------+                                                                                                        | col_smallint_signed |                                                                                                        +---------------------+                                                                                                        |                   0 |                                                                                                        +---------------------+                                                                                                        1 row in set (0.00 sec)     
                                                                                                                                                                                                                                                                                                                                                     mysql> SELECT ALL col_smallint_signed FROM table_1_latin1_undef WHERE 0;                                                       Empty set (0.01 sec)   
                                                                                                                                                                                                                                                                                                                                                               mysql> SELECT ALL col_smallint_signed FROM table_1_latin1_undef WHERE NULL;                                                    
Empty set (0.00 sec)

mysql> ALTER TABLE table_1_latin1_undef DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT ALL col_smallint_signed FROM table_1_latin1_undef WHERE 1;
ERROR 1814 (HY000): Tablespace has been discarded for table 'table_1_latin1_undef'

-- incorrect
mysql> SELECT ALL col_smallint_signed FROM table_1_latin1_undef WHERE 0;
Empty set (0.00 sec)

-- incorrect
mysql> SELECT ALL col_smallint_signed FROM table_1_latin1_undef WHERE NULL;
Empty set (0.00 se

How to repeat:
create table table_1_latin1_undef (
`pk` int primary key,
`col_bigint_signed` bigint ,
`col_float_signed` float ,
`col_double_signed` double ,
`col_char(20)_signed` char(20) ,
`col_varchar(20)_signed` varchar(20) ,
`col_tinyint_signed` tinyint ,
`col_smallint_signed` smallint
) character set latin1 ;

insert into table_1_latin1_undef values (0,12.991,1,-121,'w',null,null,'j');

SELECT ALL col_smallint_signed FROM table_1_latin1_undef WHERE 1;

SELECT ALL col_smallint_signed FROM table_1_latin1_undef WHERE 0;

SELECT ALL col_smallint_signed FROM table_1_latin1_undef WHERE NULL;

mysql> ALTER TABLE col_smallint_signed DISCARD TABLESPACE;

SELECT ALL col_smallint_signed FROM table_1_latin1_undef WHERE 1;

-- incorrect
SELECT ALL col_smallint_signed FROM table_1_latin1_undef WHERE 0;

-- incorrect
SELECT ALL col_smallint_signed FROM table_1_latin1_undef WHERE NULL;

Suggested fix:
They should all return the same error code to inform the user the tablespace is discarded.

Hi Mr. ZHANG,

Thank you for your bug report.

I have changed your test case so that the error throwing statement is removed.

With latest 8.0, I get the following:

col_smallint_signed
1

With latest 5.7, I get the following:

col_smallint_signed
1

Hence, your test case is repeated.

Verified as reported.

Just a note:
The MySQL optimizer notes impossible where clauses, and does not try to read from the table when an impossible where clause is detected.  Because MySQL does not try to read from the table, it does not detect that the table has no tablespace.  If MySQL were not to do this, it would read all rows from the table and discard them all, which would not be optimal, so I do not think this is a bug.

The InnoDB storage engine does not evaluate conditions, except in the case of ICP, and thus it would not be possible to abandon the scan if this optimization were to be removed.

Justin,

True, but it is still a bug and a very low priority one ......