Syntax Error when Creating a Temporary Table Using A Subquery That Contains A Union All

Question

So what I'm trying to do is create a temporary table with a unioned set of results that have distinct conditions. The information is largely the same, just in different places that need to be matched.

My query is below;

DROP TABLE IF EXISTS temp_data;
CREATE TEMPORARY TABLE IF NOT EXISTS temp_data AS (SELECT * FROM 
  ((SELECT
      src.value AS 'sr_code',
      DATE(rd.value) AS 'release_date',
      sw.value AS 'songwriter',
      SEC_TO_TIME(rt.value) AS 'runtime',
      COALESCE(track.original, 0) AS 'original',
      COALESCE(track.exclude_mlc, 0) AS 'exclude_mlc'
  FROM catalog_product_entity prod
  LEFT JOIN catalog_product_entity_datetime rd ON rd.row_id = prod.row_id AND rd.store_id = 0 AND rd.attribute_id = 249
  LEFT JOIN catalog_product_entity_varchar src ON src.row_id = prod.row_id AND src.store_id = 0 AND src.attribute_id = 218
  LEFT JOIN catalog_product_entity_varchar sw ON sw.row_id = prod.row_id AND sw.store_id = 0 AND sw.attribute_id = 308
  LEFT JOIN catalog_product_entity_int rt ON rt.row_id = prod.row_id AND rt.store_id = 0 AND rt.attribute_id = 226
  LEFT JOIN downloadable_link link ON prod.row_id = link.product_id
  LEFT JOIN downloadable_link_track track ON track.link_id = link.link_id 
  WHERE prod.attribute_set_id = 13 AND src.value IS NOT NULL AND track.original = 0 AND track.exclude_mlc = 0)
  UNION ALL
  (SELECT
      track.sr_code AS 'sr_code',
      DATE(rd.value) AS 'release_date',
      track.songwriter AS 'songwriter',
      SEC_TO_TIME(track.runtime) AS 'runtime',
      COALESCE(track.original, 0) AS 'original',
      COALESCE(track.exclude_mlc, 0) AS 'exclude_mlc'
  FROM catalog_product_entity prod
  LEFT JOIN catalog_product_entity_datetime rd ON rd.row_id = prod.row_id AND rd.store_id = 0 AND rd.attribute_id = 249
  LEFT JOIN downloadable_link link ON link.product_id = prod.row_id
  LEFT JOIN downloadable_link_track track ON track.link_id = link.link_id
  LEFT JOIN downloadable_link_title title ON title.link_id = link.link_id
  WHERE prod.attribute_set_id = 12 AND track.sr_code IS NOT NULL AND track.original = 0 AND track.exclude_mlc = 0))
AS 'temp_data_table');

But when I run the query, I'm returned with a syntax error. At first, I was just running the union selects as the create statement, which I learned you cannot do. I wrapped it in another select to use that statement as a subquery (as indicated here - https://stackoverflow.com/a/31751787/18582667). I'm able to get the SELECT + UNION + SELECT to run without the temp table syntax so my query itself seems to be fine.

This is what I receive: ERROR 1064 (42000): 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 ''temp_data_table')' at line 31 I'm not sure what I did wrong and why it's throwing that at me. I'm leaning towards parens, but I don't have any non-paired ones in there.

I am working with a Magento 2 install, but I don't believe that's entirely relevant. Throwing it in there just to be thorough.

Running MySQL 5.6.42-84.2 Percona Server (GPL), Release 84.2, Revision 6b2b987 on Red Hat Enterprise Linux Server 7.9 (Maipo).

Answer 1

Aliasing a table or subquery with a quoted string will error.

DROP TABLE IF EXISTS temp_data;
CREATE TEMPORARY TABLE IF NOT EXISTS temp_data AS (SELECT * FROM 
  ((SELECT
      src.value AS 'sr_code',
      DATE(rd.value) AS 'release_date',
      sw.value AS 'songwriter',
      SEC_TO_TIME(rt.value) AS 'runtime',
      COALESCE(track.original, 0) AS 'original',
      COALESCE(track.exclude_mlc, 0) AS 'exclude_mlc'
  FROM catalog_product_entity prod
  LEFT JOIN catalog_product_entity_datetime rd ON rd.row_id = prod.row_id AND rd.store_id = 0 AND rd.attribute_id = 249
  LEFT JOIN catalog_product_entity_varchar src ON src.row_id = prod.row_id AND src.store_id = 0 AND src.attribute_id = 218
  LEFT JOIN catalog_product_entity_varchar sw ON sw.row_id = prod.row_id AND sw.store_id = 0 AND sw.attribute_id = 308
  LEFT JOIN catalog_product_entity_int rt ON rt.row_id = prod.row_id AND rt.store_id = 0 AND rt.attribute_id = 226
  LEFT JOIN downloadable_link link ON prod.row_id = link.product_id
  LEFT JOIN downloadable_link_track track ON track.link_id = link.link_id 
  WHERE prod.attribute_set_id = 13 AND src.value IS NOT NULL AND track.original = 0 AND track.exclude_mlc = 0)
  UNION ALL
  (SELECT
      track.sr_code AS 'sr_code',
      DATE(rd.value) AS 'release_date',
      track.songwriter AS 'songwriter',
      SEC_TO_TIME(track.runtime) AS 'runtime',
      COALESCE(track.original, 0) AS 'original',
      COALESCE(track.exclude_mlc, 0) AS 'exclude_mlc'
  FROM catalog_product_entity prod
  LEFT JOIN catalog_product_entity_datetime rd ON rd.row_id = prod.row_id AND rd.store_id = 0 AND rd.attribute_id = 249
  LEFT JOIN downloadable_link link ON link.product_id = prod.row_id
  LEFT JOIN downloadable_link_track track ON track.link_id = link.link_id
  LEFT JOIN downloadable_link_title title ON title.link_id = link.link_id
  WHERE prod.attribute_set_id = 12 AND track.sr_code IS NOT NULL AND track.original = 0 AND track.exclude_mlc = 0))
AS temp_data_table);

See this demo https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=888a778cf10ce741efca9503d312691a

Answer 2

I don't think you need the outer SELECT *. Instead simply use parens:

CREATE ... AS
    (
      ( SELECT ... )
      UNION ALL
      ( SELECT ... )
    );

If you were running MySQL 8.0, a WITH clause might be useful.