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:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.23 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[30 Jan 8:52] soheil rahsaz
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.
[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>
[5 Oct 1:09] Yoseph Phillips
This is also impacting us.

For us doing SET optimizer_switch = "block_nested_loop=off"; first prevents the issue. 
This might be related to the hash join implementation as the developer might still be padding the values instead of trimming them, see my last comment on https://bugs.mysql.com/bug.php?id=97605 for more information.

For us there are also other ways of working around this issue such as using UNION ALL instead of UNION, however obviously the bug needs to be urgently fixed as the hash join optimisation can be very helpful, and queries cannot be changed on a global scale especially when it is not specified exactly what is causing this issue.

Maybe this will be a further insensitive for Oracle to change the hash join to trim instead of pad.
[8 Nov 3:18] yangyang wang
This is also impacting us.
And optimizer_switch='derived_merge=off' has been set to ensure normal services. When will this problem be fixed?