MySQL Bugs: #98896: CREATE TABLE ... SELECT fails if there is a functional index

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:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If a `CREATE TABLE ... SELECT` statement includes a functional index, it will fail.

The split version `CREATE TABLE` + `INSERT` works as intended:

```sql
CREATE TEMPORARY TABLE issue_functional_key_part (
  sold_on DATETIME NOT NULL,
  INDEX sold_on_date ((DATE(sold_on)))
);

INSERT INTO issue_functional_key_part VALUES (NOW());

-- Query OK, 1 row affected (0,00 sec)
```

How to repeat:
Execute:

```sql
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.
```

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

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.

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).

Hello Saverio M,

Thank you for the report and feedback.
Observed this with 8.0.19 builds on OL7.

regards,
Umesh

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.

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.

Posted by developer:
 
Fixed in 8.0.21.

CREATE TABLE ... SELECT failed if it included a functional index.