Bug #100453 Drop TABLESPACE of a table has inconsistent impact on the query
Submitted: 7 Aug 12:14 Modified: 7 Aug 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

[7 Aug 12:14] Yushan ZHANG
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.
[7 Aug 15:52] MySQL Verification Team
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.
[12 Aug 15:12] Justin Swanhart
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.
[12 Aug 15:14] Justin Swanhart
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.
[13 Aug 12:24] MySQL Verification Team
Justin,

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