Reputation: 63
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
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
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) = ''
Upvotes: 1