Issue: Query with Syntax Error on MySQL
Author: theosotr
State: closed
Created: 2020-08-14 10:12:51

I have the following peewee query (over-simplified for opening the issue).

```python
q1 = Table1.select(Table1.colA).objects()
q2 = Table2.select(Table2.colB).objects()
q3 = Table3.select(Table3.colC).objects()

comb = q1.union(q2).order_by(Table1.colA).limit(10)
q3.union(comb).order_by(Table3.colC)
```

this crashes with the following exception on MySQL

```
Traceback (most recent call last):
  File "/home/.env/lib/python3.6/site-packages/peewee-3.13.3-py3.6.egg/peewee.py", line 3119, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/.env/lib/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/.env/lib/python3.6/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/.env/lib/python3.6/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY `col`' at line 1")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "tmp.py", line 31, in <module>
    for r in ret5:
  File "/home/.env/lib/python3.6/site-packages/peewee-3.13.3-py3.6.egg/peewee.py", line 6833, in __iter__
    self.execute()
  File "/home/.env/lib/python3.6/site-packages/peewee-3.13.3-py3.6.egg/peewee.py", line 1891, in inner
    return method(self, database, *args, **kwargs)
  File "/home/.env/lib/python3.6/site-packages/peewee-3.13.3-py3.6.egg/peewee.py", line 1962, in execute
    return self._execute(database)
  File "/home/.env/lib/python3.6/site-packages/peewee-3.13.3-py3.6.egg/peewee.py", line 2134, in _execute
    cursor = database.execute(self)
  File "/home/.env/lib/python3.6/site-packages/peewee-3.13.3-py3.6.egg/peewee.py", line 3132, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/home/.env/lib/python3.6/site-packages/peewee-3.13.3-py3.6.egg/peewee.py", line 3126, in execute_sql
    self.commit()
  File "/home/.env/lib/python3.6/site-packages/peewee-3.13.3-py3.6.egg/peewee.py", line 2892, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/home/.env/lib/python3.6/site-packages/peewee-3.13.3-py3.6.egg/peewee.py", line 183, in reraise
    raise value.with_traceback(tb)
  File "/home/.env/lib/python3.6/site-packages/peewee-3.13.3-py3.6.egg/peewee.py", line 3119, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/.env/lib/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/.env/lib/python3.6/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/.env/lib/python3.6/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
peewee.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY `colC`' at line 1")
```

the generated SQL is

```sql
(SELECT `t1`.`colC` FROM `table3` AS `t1`) UNION (SELECT `t2`.`colA` FROM `table1` AS `t2`) UNION (SELECT `t3`.`colB` FROM `table2` AS `t3`) ORDER BY `colA` LIMIT 10 ORDER BY `colC`
```

If you want your ORDER BY or LIMIT to be applied to an individual query within the union, then you need to apply that ordering before you union them together. Otherwise peewee thinks you're trying to apply that to the overall compound result set. It's complicated because the ordering can be either applied to the individual query, or the overall result-set. So if you want the former, you apply it before union-ing.

You can fix this by:

```python
q1 = Table1.select(Table1.colA).order_by(Table1.colA).limit(10)
q2 = Table2.select(Table2.colB)
q3 = Table3.select(Table3.colC).order_by(Table3.colC)

union = q1.union(q2).union(q3)
```

Thanks.
What happens if I want to apply `order_by` to the result of the union? similar to my example above? I think the semantics of the proposed fix is different from that of the initial query. Am I wrong?

You can do that. Just once you've unioned everything together, specify ordering by column number (since they have different names):

```python

union.order_by(SQL('1'))
```

No, I meant what happens if I want to apply `order_by` to the result of a *sub-union*. This has different semantics from applying order_by to the individual sub-query (e.g., q1).

```
comb = q1.union(q2).order_by(SQL('1')).limit(10)
q3.union(comb).order_by(SQL('1'))
```
the query above still fails on MySQL (note that in Postgres runs fine!)

I'm not sure how well supported that will be, in that case.

So, this kind of query is not supported by peewee. Thanks!

>So, this kind of query is not supported by peewee. Thanks!

That doesn't seem correct to me.

Does MySQL even support doing that? I believe that MySQL **does not** support nested parentheses on UNION queries - thus there's no way to clearly specify the ordering for the sub-union. Postgres does, which is why it works on Postgres.

You can try to force Peewee to put the extra parentheses there by specifying

```python

db.compound_select_parentheses = CSQ_PARENTHESES_ALWAYS  # 1
```

Alternatively, make your first UNION, then SELECT FROM it.

```python

u1 = q1.union(q2)
u1 = u1.order_by(SQL('1'))

sub_union = Select(SQL('1')).from_(u1)
total = sub_union.union(q3)
```

I'm sure there are other ways, if you just put your mind to it.

I notice you are using lots of different databases -- what exactly are you trying to do? You've brought up sqlite, postgres and mysql so far.


> 
> Alternatively, make your first UNION, then SELECT FROM it.
> 
> ```python
> u1 = q1.union(q2)
> u1 = u1.order_by(SQL('1'))
> 
> sub_union = Select(SQL('1')).from_(u1)
> total = sub_union.union(q3)
> ```

Thanks again.
I've just tried this solution, and peewee seems to reach an infinite loop (it never terminates). You can check it on your own if you want.


> 
> I notice you are using lots of different databases -- what exactly are you trying to do? You've brought up sqlite, postgres and mysql so far.

I and my research team differentially test well-established ORMs (e.g., Django, peewee, SQLAalchemy, and more) for finding bugs.



I didn't test that, the correct syntax was:

```python

sub_union = Select(from_list=[u1], columns=[SQL('1')])
```

that works.