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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.058 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.