MySql5 view parenthesis

[jdbodyfelt]

Describe the bug

A consistent failure on view creation within MySQL5.x, especially those containing UNION, throws

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 'union

A traceback to /target/run shows the offending *.sql of form:

create view schema.table__dbt_tmp as (
  (
    select *
    from schema.table_1A
  )
  union
  (
    select *
    from schema.table_1B
  )
);

Removing the first parenthetical grouping, so that the query reads

create view schema.table__dbt_tmp as 
  (
    select *
    from schema.table_1A
  )
  union
  (
    select *
    from schema.table_1B
  )

results in a working query. Likely a final parsing step just needs amending, but not certain yet where that is in the adapter stack...

[fong-kinduct]

also running into this problem

changing the offending model to {{config(materialized="table")}} rather than {{config(materialized="view")}} works fine as no parenthesis are added.

obviously a table is not as useful as a view, but just figured it was worth adding more data points.

[fong-kinduct]

funnily enough, ommitting the leading and proceeding parenthesis from your model generates a working view.

select * from table_a ) union ( select * from table_b

[abdel-87]

Same here. Will this be fixed somewhere soon? The syntax in Mysql 5.7 should be as simple as. So the wrapping brackets must be removed.

create view [view_name] as select * from table