3C41DC
3C41DC

Reputation: 63

Data truncation: Incorrect DATETIME value '' when using a CREATE TABLE SELECT statement

I am attempting to create a table on MariaDB 10.3 from a SELECT statement. The table contains the customer information and a column showing the missing elements for that customer row. Here is the example setup:

CREATE TABLE `customerTest` (
  `custid` int(10) NOT NULL PRIMARY KEY,
  `firstname` char(16) DEFAULT NULL,
  `lastname` char(21) DEFAULT NULL,
  `birthdate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `customerTest` VALUES (1,'Fred',NULL,'1950-01-01'),(2,'Barney',NULL,NULL),(3,'Wilma','Flintstone',NULL);

The query works when not being used in a CREATE TABLE statement.

SELECT custid, customerMissingDemographics FROM (
    SELECT custid, GROUP_CONCAT(CONCAT_WS(',',FName,LName,Birthdate)) AS customerMissingDemographics FROM (
            SELECT c.custid,
            CASE WHEN firstname = '' OR firstname IS NULL THEN 'First Name' ELSE NULL END AS FName,
            CASE WHEN lastname = '' OR lastname IS NULL THEN 'Last Name' ELSE NULL END LName,
            CASE WHEN birthdate = '' OR birthdate IS NULL THEN 'Birthdate' ELSE NULL END Birthdate
            FROM `customerTest` c
            GROUP BY custid
            ) computeMissingDemographics
    GROUP BY computeMissingDemographics.custid
    ) aggregateMissingDemographics
 WHERE customerMissingDemographics <> ''

The query returns:

custid|customerMissingDemographics
------|---------------------------
     1|Last Name                  
     2|Last Name,Birthdate        
     3|Birthdate                  

However, if I attempt to use CREATE TABLE test to create a table with the results from this query, it fails with SQL Error [1292] [22001]: Data truncation: Incorrect datetime value: ''

I have attempted to CAST the fields in the computeMissingDemographics table to no effect. Why is it doing this?

Upvotes: 0

Views: 874

Answers (2)

Rick James
Rick James

Reputation: 142306

The expression for Birthdate in the SELECT failed to be defined the way you wanted.

When using

CREATE TABLE t
    SELECT ...

You can optionally add/change columns/indexes. In your case, I suggest

CREATE TABLE t (
       `Birthdate` date DEFAULT NULL
               )
    SELECT ...

Upvotes: 1

Nick
Nick

Reputation: 147196

This is interesting; it seems some implicit casting in the SELECT query is disabled when it it used in a CREATE TABLE query. Your issue is in this test:

birthdate = ''

as '' is not a valid DATE value. You can resolve the issue by explicitly casting birthdate to CHAR before comparison i.e.

CAST(birthdate AS CHAR) = ''

Demo on dbfiddle

Upvotes: 1

Related Questions