| Bug #102429 | Table #sql13c8_4a4_d' uses an extension that doesn't exist in this MySQL version | ||
|---|---|---|---|
| Submitted: | 30 Jan 8:52 | Modified: | 30 Jan 11:32 |
| Reporter: | soheil rahsaz | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.23 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[30 Jan 8:53]
soheil rahsaz
also tested on 8.22 and centos and ubuntu.
[30 Jan 10:09]
Valeriy Kravchuk
Would you mind to send also SHOW CREATE TABLE and minimal sample data for the tables involved, as well as the definition of that getEId() function? So that we have a full test to copy/paste.
[30 Jan 11:32]
MySQL Verification Team
Hello Soheil, Thank you for the report and test case. Verified as described. regards, Umesh
[30 Jan 11:36]
MySQL Verification Team
- 8.0.23 (Both windows and Linux builds confirms the reported problem) -build cat docs/INFO_SRC commit: c586d55f06bf915d6506e599deb87dbb89f2496a date: 2020-12-10 06:56:16 +0100 build-date: 2020-12-11 07:43:19 +0000 short: c586d55f06b branch: mysql-8.0.23-release MySQL source 8.0.23 - MySQL Server 8.0.23 on OL7 ERROR 1112 (42000): Table '/tmp/#sql7247_7_3' uses an extension that doesn't exist in this MySQL version - MySQL Server 8.0.23 on Windows Error Code: 1112. Table 'C:\WINDOWS\SERVIC~1\NETWOR~1\AppData\Local\Temp\#sqle10_9_e' uses an extension that doesn't exist in this MySQL version 0.000 sec
[30 Jan 11:36]
MySQL Verification Team
- 5.7.33 not affected with provided test case
.
mysql>
mysql> SELECT t_2.*
-> FROM (SELECT columnId1 AS workbookTemplateColumnId
-> FROM `hsms_workbookTemplate` t1
-> WHERE t1.id = 2
-> UNION
-> SELECT columnId2 AS workbookTemplateColumnId
-> FROM `hsms_workbookTemplate` t2
-> UNION
-> SELECT columnId6 AS workbookTemplateColumnId
-> FROM `hsms_workbookTemplate` t6
-> UNION
-> SELECT columnId7 AS workbookTemplateColumnId
-> FROM `hsms_workbookTemplate` t7) t_2
->
->
-> JOIN `hsms_studentRegistration` t_3 ON getEId() = t_3.eduYearId
-> JOIN `hsms_workbookTemplateColumn` t_4 ON t_2.workbookTemplateColumnId = t_4.id;
+--------------------------+
| workbookTemplateColumnId |
+--------------------------+
| 225 |
| 225 |
| 225 |
| 225 |
| 272 |
| 272 |
| 272 |
| 272 |
| 353 |
| 353 |
| 353 |
| 353 |
| 359 |
| 359 |
| 359 |
| 359 |
| 362 |
| 362 |
| 362 |
| 362 |
| 387 |
| 387 |
| 387 |
| 387 |
| 400 |
| 400 |
| 400 |
| 400 |
| 428 |
| 428 |
| 428 |
| 428 |
| 357 |
| 357 |
| 357 |
| 357 |
| 358 |
| 358 |
| 358 |
| 358 |
+--------------------------+
40 rows in set (0.00 sec)
[30 Jan 11:39]
MySQL Verification Team
- Lowest checked version 8.0.14 - not affected
mysql>
mysql> SELECT t_2.*
-> FROM (SELECT columnId1 AS workbookTemplateColumnId
-> FROM `hsms_workbookTemplate` t1
-> WHERE t1.id = 2
-> UNION
-> SELECT columnId2 AS workbookTemplateColumnId
-> FROM `hsms_workbookTemplate` t2
-> UNION
-> SELECT columnId6 AS workbookTemplateColumnId
-> FROM `hsms_workbookTemplate` t6
-> UNION
-> SELECT columnId7 AS workbookTemplateColumnId
-> FROM `hsms_workbookTemplate` t7) t_2
->
->
-> JOIN `hsms_studentRegistration` t_3 ON getEId() = t_3.eduYearId
-> JOIN `hsms_workbookTemplateColumn` t_4 ON t_2.workbookTemplateColumnId = t_4.id;
+--------------------------+
| workbookTemplateColumnId |
+--------------------------+
| 225 |
| 225 |
| 225 |
| 225 |
| 272 |
| 272 |
| 272 |
| 272 |
| 353 |
| 353 |
| 353 |
| 353 |
| 359 |
| 359 |
| 359 |
| 359 |
| 362 |
| 362 |
| 362 |
| 362 |
| 387 |
| 387 |
| 387 |
| 387 |
| 400 |
| 400 |
| 400 |
| 400 |
| 428 |
| 428 |
| 428 |
| 428 |
| 357 |
| 357 |
| 357 |
| 357 |
| 358 |
| 358 |
| 358 |
| 358 |
+--------------------------+
40 rows in set (0.00 sec)
mysql>
mysql> show variables like '%version%';
+--------------------------+------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------+
| immediate_server_version | 999999 |
| innodb_version | 8.0.14 |
| original_server_version | 999999 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
| version | 8.0.14 |
| version_comment | MySQL Community Server - GPL |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.12 |
| version_compile_zlib | 1.2.11 |
+--------------------------+------------------------------+
11 rows in set (0.01 sec)
mysql>
Description: Running this select query produces the following error: Table 'C:\Windows\TEMP\#sql13c8_4a4_d' uses an extension that doesn't exist in this MySQL version: SELECT t_2.* FROM (SELECT column1 AS colName FROM `table1` t1 WHERE t1.id = 34 UNION SELECT column2 AS colName FROM `table1` t2 WHERE t2.id = 34 UNION SELECT column3 AS colName FROM `table1` t6 WHERE t6.id = 34 UNION SELECT column4 AS colName FROM `table1` t7 WHERE t7.id = 34) t_2 JOIN `table2` t_3 ON getEId() = t_3.eId JOIN `table3` t_4 ON t_2.colName = t_4.id; explain result: +----+--------------+----------------+------------+--------+---------------------+---------------------+---------+------------------------------+------+----------+---------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+----------------+------------+--------+---------------------+---------------------+---------+------------------------------+------+----------+---------------------------------------------------------+ | 1 | PRIMARY | t_3 | NULL | index | NULL | termId | 4 | NULL | 60 | 10.00 | Using index | | 1 | PRIMARY | <derived2> | NULL | range | <auto_distinct_key> | <auto_distinct_key> | 5 | NULL | 10 | 100.00 | Using where; Using index; Using join buffer (hash join) | | 1 | PRIMARY | t_4 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | t_2.colName | 1 | 100.00 | Using where; Using index | | 2 | DERIVED | t1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 3 | UNION | t2 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 4 | UNION | t6 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 5 | UNION | t7 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | NULL | UNION RESULT | <union2,3,4,5> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+----------------+------------+--------+---------------------+---------------------+---------+------------------------------+------+----------+---------------------------------------------------------+ But if I remove one union select or remove the getEId() function, it runs with no problem. example with one less union: SELECT t_2.* FROM (SELECT column1 AS colName FROM `table1` t1 WHERE t1.id = 34 UNION SELECT column2 AS colName FROM `table1` t2 WHERE t2.id = 34 UNION SELECT column4 AS colName FROM `table1` t7 WHERE t7.id = 34) t_2 JOIN `table2` t_3 ON getEId() = t_3.eId JOIN `table3` t_4 ON t_2.colName = t_4.id explain result: +----+--------------+--------------+------------+--------+---------------------+---------+---------+------------------------------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+--------------+------------+--------+---------------------+---------+---------+------------------------------+------+----------+--------------------------------------------+ | 1 | PRIMARY | t_3 | NULL | index | NULL | termId | 4 | NULL | 60 | 10.00 | Using index | | 1 | PRIMARY | <derived2> | NULL | ALL | <auto_distinct_key> | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (hash join) | | 1 | PRIMARY | t_4 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | t_2.colName | 1 | 100.00 | Using where; Using index | | 2 | DERIVED | t1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 3 | UNION | t2 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 4 | UNION | t7 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | NULL | UNION RESULT | <union2,3,4> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+--------------+------------+--------+---------------------+---------+---------+------------------------------+------+----------+--------------------------------------------+ How to repeat: Running the query repeats the problem.