[MDEV-20267] CREATE TABLE + SELECT behaves incoherently Created: 2019-08-06  Updated: 2020-05-29

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Create Table
Affects Version/s: 10.1.20, 10.2.16, 10.3, 10.4
Fix Version/s: 10.5

Type: Bug Priority: Minor
Reporter: Guillermo Schulman Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: alias, create_table
Environment:

Tested in ubuntu 16.04 (xenial)


 Description   

Creating a table with a PK plus a SELECT statement leads to 2 different behaviors depending on the column matching names or not.
Here are 3 examples, the first 2 cases with non matching column names and the third matching:

create-table-select.sql

 
MariaDB [test]> CREATE TABLE myTable (myField int, PRIMARY KEY(myField)) AS SELECT 1;
 
ERROR 1364 (HY000): Field 'myField' doesn't have a default value
 
 
 
MariaDB [test]> CREATE TABLE myTable (myField int, PRIMARY KEY(myField)) AS SELECT 1 as myField2;
 
ERROR 1364 (HY000): Field 'myField' doesn't have a default value
 
 
 
MariaDB [test]> CREATE TABLE myTable (myField int, PRIMARY KEY(myField)) AS SELECT 1 as myField;
 
Query OK, 1 row affected (0.00 sec)
 
Records: 1  Duplicates: 0  Warnings: 0



 Comments   
Comment by Elena Stepanova [ 2019-08-06 ]

What do you consider to be inconsistency? It seems quite logical.
In the first case, you are trying to do

CREATE TABLE `myTable` (
 
  `myField` int(11) NOT NULL,
 
  `1` int(1) NOT NULL,
 
  PRIMARY KEY (`myField`)
 
);
 
INSERT INTO myTable (`1`) VALUES (1);

Since `myField` doesn't have a default value (and you apparently run with one of strict sql modes), it fails.

The second one is the same, only instead of naming the 2nd column `1`, you name it `myField2`.

In the third case, you are doing

CREATE TABLE `myTable` (
 
  `myField` int(11) NOT NULL,
 
  PRIMARY KEY (`myField`)
 
) ENGINE=InnoDB;
 
INSERT INTO myTable (`myField`) VALUES (1);

It is all legit, hence no complaints.

Comment by Guillermo Schulman [ 2019-08-06 ]

You are right. I misunderstood the documentation and thought it just automatically links the first table column with the first recordset column, the second one with the second, etc as "INSERT INTO + SELECT" works, i.e., the order is the key to match regardless the column names/aliases. Now I see it doesn't, my bad.

Anyway, I would like to highlight also this case:

MariaDB [test]> CREATE TABLE myTable (myField int, PRIMARY KEY(myField)) AS SELECT 1 FROM dual WHERE false;
 
ERROR 1364 (HY000): Field 'myField' doesn't have a default value

It seems that the parser validates if there would be a column for the not nullable and no-default-value column before checking if there is any row in the results. A bit controversial, isn't it?
In any case, thanks for your fast response, we can just close this issue. Sorry for the confusion.

Comment by Elena Stepanova [ 2019-09-04 ]

bar, do you consider the part from the last comment to be a bug?
Please close the report if not.

Comment by Alexander Barkov [ 2020-05-29 ]

I'm not sure. Reassigning to serg.

Generated at Thu Feb 08 08:58:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.