SELECT statements are enclosed in a parenthesis when using UNION / INTERSECTION operators

[mukund109]

Pypika encloses the SELECT statements in a parenthesis when using a UNION or INTERSECTION operator (haven't tried other set operations)

>>> pypika.__version__
'0.47.0'
>>> from pypika import SQLLiteQuery as Query
>>> q = Query.from_('t1').select('*') + Query.from_('t2').select('*')
>>> q.get_sql()
'(SELECT * FROM "t1") UNION (SELECT * FROM "t2")'

SQLite throws a syntax error for this. It doesn't allow the SELECT statements to be enclosed in parenthesis - see SQLite docs

According to SQLite this is the correct way of doing it

SELECT * FROM "t1" UNION SELECT * FROM "t2"

[jubele]

I am experiencing same issue while executing SELECT statements using EXCEPT operator.

>>>pypika.__version__
'0.48.0'
>>>from pypika import Query
>>>from pypika import Table
>>>t1 = Table('t1')
>>>q1 = Query.from_(t1).select('*').orderby(t1.r1)
>>>q2 = Query.from_(t1).select('*').where(t1.r1.lte(10))
>>>q_ex = q1.except_of(q2)
>>>q_ex.get_sql()
'(SELECT * FROM "t1" ORDER BY "r1") EXCEPT (SELECT * FROM "t1" WHERE "r1"<=10)'

TarantoolDB (SQL-101 syntax) throws also syntax error due to parenthesis enclosure of the statements tarantool.error.DatabaseError: (184, "Syntax error at line 1 near '('")

Correct syntax in my case is same as @mukund109 proposed.

SELECT * FROM "t1" ORDER BY "r1" EXCEPT SELECT * FROM "t1" WHERE "r1"<=10

[daidai21]

pypika/pypika/tests/test_joins.py

Lines 708 to 712 in 7d0bc58

	'(SELECT "foo" FROM "abc") UNION (SELECT "bar" FROM "efg")',
	str(query1 + query2),
	)
	self.assertEqual(
	'(SELECT "foo" FROM "abc") UNION (SELECT "bar" FROM "efg")',

pypika/pypika/tests/test_joins.py

Lines 1114 to 1117 in 7d0bc58

	'(SELECT "foo" FROM "abc") EXCEPT '
	'(SELECT "bar" FROM "efg") EXCEPT '
	'(SELECT "baz" FROM "hij") EXCEPT '
	'(SELECT "faz" FROM "lmn")',

test shell:

docker pull nouchka/sqlite3
docker run -it --rm nouchka/sqlite3

test sql:

CREATE TABLE a ( id int );
INSERT INTO a (id) VALUES ( 1 );
INSERT INTO a (id) VALUES ( 2 );
CREATE TABLE b ( id int );
INSERT INTO b (id) VALUES ( 3 );
INSERT INTO b (id) VALUES ( 4 );
SELECT * FROM "a" UNION SELECT * FROM "b";
(SELECT * FROM "a") UNION (SELECT * FROM "b");

test log:

sqlite> SELECT * FROM "a" UNION SELECT * FROM "b";
1
2
3
4
sqlite> (SELECT * FROM "a") UNION (SELECT * FROM "b");
Error: near "(": syntax error

I feel this problem requires a lot of code modification.