| Bug #104450 | resultsof col_enum IN subquery are different | ||
|---|---|---|---|
| Submitted: | 29 Jul 2021 6:22 | Modified: | 29 Jul 2021 13:20 |
| Reporter: | jiangtao guo | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S4 (Feature request) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[29 Jul 2021 13:20]
MySQL Verification Team
Hi Mr. guo, Thank you for your bug report. We do require some additional clarification from you. First, are you aware that you are comparing and ENUM domain to DOUBLE domain ???? Actually, it is first query that looks like a true bug, not the second one. We managed to repeat the behaviour that you report, with a little bit changed queries: ----------------------------------------------- First Query: col_enum col_double ABC 0 Second one: ----------------------------------------------- Actually, this is a bug, but with a very low priority. First query should return zero rows, just like a second one. This is about strong typing and right now, MySQL is very lax with enforcing of the strong typing. It would, simply, break many applications. Verified as reported.
Description: drop table if exists t1; create table t1(col_datetime datetime, col_enum enum('ABC'), col_double double); insert into t1 values('2030-05-19', 'ABC', 0); select 1 from t1 where col_enum IN (select col_double where col_datetime); select 1 from t1 where col_enum IN (select col_double); The last two select should got same result. But the results are: mysql> select 1 from t1 where col_enum IN (select col_double where col_datetime); +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql> select 1 from t1 where col_enum IN (select col_double); Empty set (0.00 sec) How to repeat: drop table if exists t1; create table t1(col_datetime datetime, col_enum enum('ABC'), col_double double); insert into t1 values('2030-05-19', 'ABC', 0); select 1 from t1 where col_enum IN (select col_double where col_datetime); select 1 from t1 where col_enum IN (select col_double);