builder.Union() generates incorrect SQL on SQLite and Oracle #71

New Issue

guillep2k · 2020-02-24T16:06:34Z

guillep2k commented

2020-02-24 16:06:34 +00:00

The UNION operator is unnecessarily enclosing the queries in parenthesis, and that's not supported by all databases (and AFAIK required by none).

This code:

usersBuilder := builder.Select("email")
usersBuilder = usersBuilder.From("`user`")
usersBuilder = usersBuilder.Where(builder.Eq{"type": UserTypeIndividual})
emailsBuilder := builder.Select("email")
emailsBuilder = emailsBuilder.From("`email_address`")
emailsBuilder = emailsBuilder.Union("ALL", usersBuilder)
sql, args, err := emailsBuilder.ToSQL()
assert.NoError(t, err)

fmt.Printf("SQL: [%+v]\n", sql)
fmt.Printf("ARGS: [%+v]\n", args)

Produces:

SQL: [(SELECT email FROM `email_address`) UNION ALL (SELECT email FROM `user` WHERE type=?)]
ARGS: [[0]]

sqlite> (SELECT email FROM `email_address`) UNION ALL (SELECT email FROM `user` WHERE type=0);
Error: near "(": syntax error

However, without parenthesis, it's supported:

sqlite> SELECT email FROM `email_address` UNION ALL SELECT email FROM `user` WHERE type=0;
email
----------
hola
user101@ex
user11@exa
user12@exa
user21@exa
...

The same happens with subqueries (e.g. .Count()):

builder.Select("count(*) AS count").From(emailsBuilder).ToSQL()

This syntax is not supported by SQLite or Oracle; PGSQL, MSSQL and MySQL seem to work fine with or without parenthesis, but AFAIK they are not required.

SQL Fiddle

MySQL: http://sqlfiddle.com/#!9/b18ec8d/5/0
PostgreSQL 9.6: http://sqlfiddle.com/#!17/b18ec/1
SQLite js: http://sqlfiddle.com/#!5/b18ec/2/0
MSSQL 2016: http://sqlfiddle.com/#!18/b18ec/1/0
Oracle 11g: http://sqlfiddle.com/#!4/80e88/1/0

The `UNION` operator is unnecessarily enclosing the queries in parenthesis, and that's not supported by all databases (and AFAIK required by none). This code: ```golang usersBuilder := builder.Select("email") usersBuilder = usersBuilder.From("`user`") usersBuilder = usersBuilder.Where(builder.Eq{"type": UserTypeIndividual}) emailsBuilder := builder.Select("email") emailsBuilder = emailsBuilder.From("`email_address`") emailsBuilder = emailsBuilder.Union("ALL", usersBuilder) sql, args, err := emailsBuilder.ToSQL() assert.NoError(t, err) fmt.Printf("SQL: [%+v]\n", sql) fmt.Printf("ARGS: [%+v]\n", args) ``` Produces: ```sql SQL: [(SELECT email FROM `email_address`) UNION ALL (SELECT email FROM `user` WHERE type=?)] ARGS: [[0]] ``` ```sql sqlite> (SELECT email FROM `email_address`) UNION ALL (SELECT email FROM `user` WHERE type=0); Error: near "(": syntax error ``` However, without parenthesis, it's supported: ```sql sqlite> SELECT email FROM `email_address` UNION ALL SELECT email FROM `user` WHERE type=0; email ---------- hola user101@ex user11@exa user12@exa user21@exa ... ``` The same happens with subqueries (e.g. `.Count()`): ```golang builder.Select("count(*) AS count").From(emailsBuilder).ToSQL() ``` This syntax is not supported by SQLite or Oracle; PGSQL, MSSQL and MySQL seem to work fine with or without parenthesis, but AFAIK they are not required. #### SQL Fiddle - MySQL: http://sqlfiddle.com/#!9/b18ec8d/5/0 - PostgreSQL 9.6: http://sqlfiddle.com/#!17/b18ec/1 - SQLite js: http://sqlfiddle.com/#!5/b18ec/2/0 - MSSQL 2016: http://sqlfiddle.com/#!18/b18ec/1/0 - Oracle 11g: http://sqlfiddle.com/#!4/80e88/1/0

lunny added the

bug

label 2020-02-25 00:03:53 +00:00

betacat0 self-assigned this 2020-03-21 16:26:28 +00:00

Sign in to join this conversation.