Issue: UNION query is not using parenthesis
Author: Polsaker
State: closed
Created: 2019-02-12 17:27:06

Hello, I'm doing the following query (on mysql):

```
    s1 = SiteLog.select(SiteLog.time).order_by(SiteLog.time.desc())
    s2 = SubLog.select(SubLog.time).order_by(SubLog.time.desc())
    final = (s1 | s2)
```

and the sql result is

```
SELECT `t1`.`time` FROM `site_log` AS `t1` ORDER BY `t1`.`time` DESC UNION SELECT `t2`.`time` FROM `sub_log` AS `t2` ORDER BY `t2`.`time` DESC
```

This lacks the parenthesis around each query so the mysql server ends up throwing a syntax error:

```
peewee.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT `t2`.`time` FROM `sub_log` AS `t2` ORDER BY `t2`.`time` DESC' at line 1")
```

What am I doing wrong?

So the issue (I didn't realize this, just saw it in [the mysql docs](https://dev.mysql.com/doc/refman/8.0/en/union.html)) is that MySQL wants parentheses when either of the subqueries contains an order by clause.

In the meantime, is it possible for you to avoid ordering the subqueries, and simply apply the ordering afterwards to the resulting union? It looks like both use the same column, "time", so this should be easy.

I tried this:

```
    s1 = SiteLog.select(SiteLog.time)
    s2 = SubLog.select(SubLog.time)
    final = (s1 | s2)
    final = final.order_by(final.c.time.desc())
```

The resulting query is

```
SELECT `t1`.`time` FROM `site_log` AS `t1` UNION SELECT `t2`.`time` FROM `sub_log` AS `t2` ORDER BY `t3`.`time` DESC
```

and of course it throws an error because a `t3` appeared there for some reason...

I ended up using `final = final.order_by(SQL('time DESC'))` as a workaround

Yeah, I'm sorry that your intuition (final.c.time.desc()) did not work -- that would be the first thing I would try, too, of course. I'll look at fixing that particular issue -- referencing the top-level columns from a compound query is a little tricky, but I think Peewee can do better. In the meantime, yeah, SQL() is the way to go.

I've pushed a patch that tells MySQL to use parentheses around the parts of a compound select query (be815ae), which should address the syntax issue hopefully.

Yeah, MySQL is seemingly a bit inconsistent. Here the ci is running against MariaDB and the compound-select queries are failing once parentheses were added:

https://travis-ci.org/coleifer/peewee/jobs/492305967

I'm going to revert the change to `MySQLDatabase.compound_select_parentheses`.

The main issue is fixed here:

36bd887ac07647c60dfebe610b34efabec675706

Sqlite never allows parentheses in compound select. Postgres allows parentheses, including nesting (e.g. if you have more than 2 queries). MySQL allows one layer of parentheses (but it looks like in MariaDB 10.4 nesting will be supported).

Added another patch (aff2a92) to allow using ".c" to reference parts of a compound select query in the order-by clause, as you had tried to do originally.

Thanks! This all was pretty quick!

See also #2014 -- mysql and mariadb are a pile of inconsistency when it comes to parsing sql.

Bug for MySQL (opened in 2007) was finally fixed for MySQL 8.0 release: https://bugs.mysql.com/bug.php?id=25734

MariaDB thought they fixed it for 10.4: https://jira.mariadb.org/browse/MDEV-11953

But they missed some scenarios, including using compound select queries inside an IN expression (e.g.). I've opened a ticket, so we'll see what happens: https://jira.mariadb.org/browse/MDEV-20606