CompoundSelect never groups first selectable even if CompoundSelect

When constructing a CompoundSelect, the first selectable in the argument list is never grouped. Therefore if the CompoundSelect is INTERSECT, and the first selectable is a CompoundSelect of UNION then, the union will not be grouped in parenthesis. This causes a problem in PostgreSQL as INTERSECT is evaluated before UNION.

Consider the program below:

import  pkg_resources
pkg_resources.require("SQLAlchemy==0.6beta1dev")

import sqlalchemy
from sqlalchemy import sql

engine = sqlalchemy.create_engine('postgresql+psycopg2://postgres@/test')
exp_union1 = sql.expression.union(
    sql.select([sql.text('1')](sql.text('1'))),
    sql.select([sql.text('2')](sql.text('2'))),
    )

exp_union2 = sql.expression.union(
    sql.select([sql.text('3')](sql.text('3'))),
    sql.select([sql.text('4')](sql.text('4'))),
    )

exp_intersect = sql.expression.intersect(exp_union1, exp_union2)

print "using SQLAlchemy %s" %(sqlalchemy.__version__)
print "union 1: %s" %exp_union1
print "union 2: %s" %exp_union2
print "intersection: %s" %exp_intersect
print "scalar: %s" %(engine.scalar(exp_intersect))

Running on trunk this produces the output:

using SQLAlchemy 0.6beta1
union 1: SELECT 1 UNION SELECT 2
union 2: SELECT 3 UNION SELECT 4
intersection: SELECT 1 UNION SELECT 2 INTERSECT (SELECT 3 UNION SELECT 4)
scalar: 1

Where the actual desired output is:

using SQLAlchemy 0.6beta1
union 1: SELECT 1 UNION SELECT 2
union 2: SELECT 3 UNION SELECT 4
intersection: (SELECT 1 UNION SELECT 2) INTERSECT (SELECT 3 UNION SELECT 4)
scalar: None

I've patched my installation to fix the problem for myself, but I don't know how good this is for a general solution as the precedence for union and intersect is different among different databases.

Comments (3)