SELECT with group by UNION select drops the group by clause

We have a table with two, disjoint, subtypes: single-row entities, and entities which must be summed and grouped. Clearly, these could have been put in different tables, since they represent different granularity of information.

Here's the table:

stuff = Table('twoKinds', metadata, 
    Column('key', Integer, primary_key=True),
    Column('subtype', String(10), nullable=True),
    Column('status', String(10), nullable=True),
    Column('groupName', String(10), nullable=True),
    Column('amount', Numeric(10,2), nullable=True),
)

The subtype is a discriminator between two kinds of queries.

The single-row query is obvious.

qrySingle= select(
    [stuff.c.groupName,stuff.c.amount,literal(1)](stuff.c.groupName,stuff.c.amount,literal(1)),
    and_(stuff.c.status=='unmatched',
         stuff.c.subtype=='single'))

The output is the desired SQL.

INFO:bug:Single: SELECT "twoKinds"."groupName", "twoKinds".amount, ? 
FROM "twoKinds" 
WHERE "twoKinds".status = ? AND "twoKinds".subtype = ?

The multi-row query, similarly, is obvious.

qryMulti= select(
    [stuff.c.groupName,func.sum(stuff.c.amount),literal(2)](stuff.c.groupName,func.sum(stuff.c.amount),literal(2)),
    and_(stuff.c.status=='unmatched',
         stuff.c.subtype=='multi'),
    group_by=[stuff.c.groupName](stuff.c.groupName))

This query creates the desired SQL.

INFO:bug:Multiple: SELECT "twoKinds"."groupName", sum("twoKinds".amount), ? 
FROM "twoKinds" 
WHERE "twoKinds".status = ? AND "twoKinds".subtype = ? GROUP BY "twoKinds"."groupName"

However, the union of these two queries, drops the GROUP BY information.

invQry= union( qrySingle, qryMulti )

The output is

INFO:bug:Union: SELECT "twoKinds"."groupName", "twoKinds".amount, ? 
FROM "twoKinds" 
WHERE "twoKinds".status = ? AND "twoKinds".subtype = ? UNION SELECT "twoKinds"."groupName", sum("twoKinds".amount), ? 
FROM "twoKinds" 
WHERE "twoKinds".status = ? AND "twoKinds".subtype = ?

Comments (2)