Bug #98896 | CREATE TABLE ... SELECT fails if there is a functional index | ||
---|---|---|---|
Submitted: | 10 Mar 2020 18:22 | Modified: | 21 Apr 2020 17:30 |
Reporter: | Saverio Miroddi | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
Version: | 8.0.19 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[10 Mar 2020 18:22]
Saverio Miroddi
[11 Mar 2020 7:15]
MySQL Verification Team
Hello Saverio M, Thank you for the report and test case. I'm not seeing any issues at my end on 8.0.19 with provided test case. May I request you to please provide exact test case(pls mark it as private if you wish to) which to reproduce this issue at our end? Thank you. - bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> DROP DATABASE IF EXISTS test; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE DATABASE test; Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> DROP TEMPORARY TABLE IF EXISTS issue_functional_key_part; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TEMPORARY TABLE issue_functional_key_part ( -> sold_on DATETIME NOT NULL, -> INDEX sold_on_date ((DATE(sold_on))) -> ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO issue_functional_key_part VALUES (NOW()); Query OK, 1 row affected (0.00 sec) mysql> SELECT NOW() `sold_on`; +---------------------+ | sold_on | +---------------------+ | 2020-03-11 06:34:35 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT NOW() `sold_on` from issue_functional_key_part; +---------------------+ | sold_on | +---------------------+ | 2020-03-11 06:34:45 | +---------------------+ 1 row in set (0.00 sec) mysql> DROP TEMPORARY TABLE IF EXISTS issue_functional_key_part; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TEMPORARY TABLE issue_functional_key_part ( -> sold_on DATETIME NOT NULL, -> INDEX sold_on_date ((DATE(sold_on))) -> ); Query OK, 0 rows affected (0.01 sec) mysql> SELECT NOW() `sold_on` from issue_functional_key_part; Empty set (0.00 sec) mysql> SELECT * from issue_functional_key_part; Empty set (0.00 sec) mysql> SELECT sold_on from issue_functional_key_part; Empty set (0.00 sec) mysql> SELECT now(),sold_on from issue_functional_key_part; Empty set (0.00 sec) regards, Umesh
[11 Mar 2020 9:04]
Saverio Miroddi
Hello, sure! I was referring to the "CREATE from SELECT" form (as opposed to CREATE + INSERT): CREATE TEMPORARY TABLE issue_functional_key_part ( sold_on DATETIME NOT NULL, INDEX sold_on_date ((DATE(sold_on))) ) SELECT NOW() `sold_on`; -- ERROR 3105 (HY000): The value specified for generated column '3351ae78dcbae4f473d53aebdc350681' in table 'issue_functional_index' is not allowed.
[11 Mar 2020 9:06]
Saverio Miroddi
For the sake of precision, the `TEMPORARY` clause I've used is irrelevant to the bug (I generally use it when testing issue, so it can be considered a copy/paste artefact).
[11 Mar 2020 10:37]
MySQL Verification Team
Hello Saverio M, Thank you for the report and feedback. Observed this with 8.0.19 builds on OL7. regards, Umesh
[11 Mar 2020 11:07]
Saverio Miroddi
Hello Umesh, so, I have the suspicion that this happens because a beharior I've observed with `CREATE from SELECT` statements: CREATE TEMPORARY TABLE create_from_select_column_ordering ( col1 INT, col2 INT, col3 INT ) SELECT 0 `col2` ; SHOW CREATE TABLE create_from_select_column_ordering; -- CREATE TEMPORARY TABLE `create_from_select_column_ordering` ( -- `col1` int DEFAULT NULL, -- `col3` int DEFAULT NULL, -- `col2` int DEFAULT NULL -- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci if you look at the columns ordering, you'll see it doesn't reflect the table definition given. I've checked the manpage (https://dev.mysql.com/doc/refman/8.0/en/create-table-select.html), but I couldn't find any relevant information. I have a few considerations. The current bug may be (but it's a guess) and effect of the "unexpected" table ordering. However, I don't known if the "unexpected" table ordering is a bug or not. If it is, then this but may be a consequence of it. Additionally, if the "unexpected" table ordering is regular behavior, this qualifies (IMO) as a documentation bug, and this should be added to the manpage (unless I've missed something while reading it). Let me know what you think, so I can open a separate bug if needed.
[11 Mar 2020 12:31]
Saverio Miroddi
I had a third look at the manpage, and the ordering behavior is documented: > Columns named in both parts or only in the SELECT part come after that. So you can ignore the last comment.
[21 Apr 2020 17:30]
Paul DuBois
Posted by developer: Fixed in 8.0.21. CREATE TABLE ... SELECT failed if it included a functional index.