CREATE table with UNION select

Help Procedural SQL
1

Hey there!

Strangeness in memsql with a create table with union select.

the following select statement works as expected:

SELECT DISTINCT iiu.synthetic_case_id, ii.synthetic_reconciliation_group_id FROM analytics_store.coten_inventory_item_usages as iiu LEFT JOIN analytics_store.coten_inventory_items as ii ON ii.synthetic_id = iiu.synthetic_inventory_item_id ;
UNION 
SELECT synthetic_case_id, synthetic_reconciliation_group_id FROM analytics_store.coten_reconciliation_group_summaries;

putting it into a create table statement, however, fails:

create table t (INDEX(synthetic_case_id))
SELECT DISTINCT iiu.synthetic_case_id, ii.synthetic_reconciliation_group_id FROM analytics_store.coten_inventory_item_usages as iiu LEFT JOIN analytics_store.coten_inventory_items as ii ON ii.synthetic_id = iiu.synthetic_inventory_item_id ;
UNION
SELECT synthetic_case_id, synthetic_reconciliation_group_id FROM analytics_store.coten_reconciliation_group_summaries;

with error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT synthetic_case_id, synthetic_reconciliation_group_id FROM analytic' at line 2
Error code 1064.
2

We’ve had similar issues using CTAS (create table as) syntax when the select using common table expressions. Not sure if this is helpful, but to workaround it, we are doing two statements: CREATE TABLE IF NOT EXISTS… and INSERT INTO. I’d hazard a guess creating a view of the union then doing a CTAS would work if you really don’t want to specify the DDL.

3

You are also missing AS as in CREATE TABLE AS. I think it’s optional, but worth a try.

4

We don’t currently support UNION directly inside CREATE TABLE and INSERT SELECT. If you wrap it in SELECT * it will work:

create table t (INDEX(synthetic_case_id))
SELECT * FROM (
SELECT DISTINCT iiu.synthetic_case_id, ii.synthetic_reconciliation_group_id FROM analytics_store.coten_inventory_item_usages as iiu LEFT JOIN analytics_store.coten_inventory_items as ii ON ii.synthetic_id = iiu.synthetic_inventory_item_id
UNION
SELECT synthetic_case_id, synthetic_reconciliation_group_id FROM analytics_store.coten_reconciliation_group_summaries) s;