[sqlite] regression: invalid parentheses with UNION/INTERSECT

[evgeniy-terekhin]

this PR introduced regression for sqlite
it renders parentheses around UNION/INTERSECT right arg. however it is illegal syntax in sqlite

sqlite> select 1 union all (select 1);
Parse error: near "(": syntax error
  select 1 union all (select 1);
                     ^--- error here

[evgeniy-terekhin]

just to support my claim here is an issue from some other repo
kayak/pypika#537

[billy1624]

Hey @evgeniy-terekhin, thanks for the bug report!

I think we can remove the parentheses around select statement after UNION operators. Some testing on Postgres, MySQL, MariaDB and SQLite.

The source code:

sea-query/src/backend/query_builder.rs

Lines 122 to 133 in 28cf151

	if !select.unions.is_empty() {
	select.unions.iter().for_each(|(union_type, query)| {
	match union_type {
	UnionType::Intersect => write!(sql, " INTERSECT (").unwrap(),
	UnionType::Distinct => write!(sql, " UNION (").unwrap(),
	UnionType::Except => write!(sql, " EXCEPT (").unwrap(),
	UnionType::All => write!(sql, " UNION ALL (").unwrap(),
	}
	self.prepare_select_statement(query, sql);
	write!(sql, ")").unwrap();
	});
	}

[evgeniy-terekhin]

@billy1624 please consider the linked fix