Framework: UNION queries missing parentheses

[andy3rdworld]

For MySQL UNIONs, when an ORDER BY is included, each statement must be surrounded by parentheses like so:

(SELECT * FROM table_name WHERE col1 = 'some value' ORDER BY col2 DESC )
UNION ALL
(SELECT * FROM table_name WHERE col1 = 'some other value' ORDER BY col2 DESC );

If parentheses are missing you'll get the following error:
SQLSTATE[HY000]: General error: 1221 Incorrect usage of UNION and ORDER BY...

Currently the query builder does not appear to include these parentheses:

protected function compileUnions(Builder $query)
{
$sql = '';

    foreach ($query->unions as $union)
    {
        $joiner = $union['all'] ? 'union all ' : 'union ';

        $sql = $joiner.$union['query']->toSql();
    }

    return $sql;
}

[taylorotwell]

Fixed.

[adam-boduch]

I think bug still exists in version 5.0.X. Although I'm using PostgreSQL...

$cols = ['id', 'name', 'date', 'description'];
$today = date('Y-m-d');

$before = DB::table('calendar')->select($cols)->where('date', '<', $today)->orderBy('date', 'DESC')->limit(5);
$after = DB::table('calendar')->select($cols)->where('date', '>=', $today)->orderBy('date', 'ASC')->limit(5);

$timeline = $after->union($before)->get();

QuerException:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "union"
LINE 1: ...r" where "date" >= $1 order by "date" asc limit 5 union sele...
^ (SQL: select "id", "name", "date", "description" from "calendar" where "date" >= 2015-06-30 order by "date" asc limit 5 union select "id", "name", "date", "description" from "calendar" where "date" < 2015-06-30 order by "date" desc limit 5)

As you can see there is no parentheses...

[GrahamCampbell]

I think bug still exists in version 5.0.X. Althought I'm using PostgreSQL...

5.0 isn't supported.

[manithchhuon]

I think bug still exists in version 5.0.X. Althought I'm using PostgreSQL...

5.2.X isn't supported either.

[gtapps]

Still exists on 5.3

[jorgevilaca82]

Still exists on 5.6

[rockfridrich]

Still exists in 5.6 👎

[robsontenorio]

Still exists in 5.8 . Any solution?

[driesvints]

@staudenmeir any chance you can help check if this still exists for 5.7?

[staudenmeir]

@driesvints Yes, this is still an issue on PostgreSQL and SQL Server. I'll take care of it.

[driesvints]

@staudenmeir thanks man 👍

[celloman]

@staudenmeir are there any updates to this? Is the fix going to be similar to what had been requested in https://github.com/laravel/framework/issues/5457 ?

[staudenmeir]

@celloman I'm still working on it. Are you affected by #5457?

[celloman]

@staudenmeir Meh, not too much. I was having a syntax issue due to extra parentheses using union. After I asked that question I ended up finding a workaround by using a builder with a where closure to group a whereIn and an orWhereIn to accomplish the same result (though the resulting sql is not as pretty and I don't know if it is equal performance-wise since there is more nesting in the sql)...

Mostly now I'm wondering if this fix will address the same issue I was seeing... I'll just follow along to see if I can refactor back to a union at some point, thanks!

[staudenmeir]

I'm asking because I can't reproduce #5457.

What query caused your syntax error?

[celloman]

@staudenmeir ah I see. The query in question was something like the following:

select * from item where item.user_id in ((select users.id from users inner join ( select users.id as user_id from users where (users.id = 1) and users.client_id= 1 and users.deleted_at is null and users.state = 1 order by users.id asc ) as rule_query on users.id = rule_query.user_id where users.client_id = 1) union (select users.id from users where users.points > 100))

which works when you remove the parentheses around the statements being unioned together, like hte following:

select * from item where item.user_id in (select users.id from users inner join ( select users.id as user_id from users where (users.id = 1) and users.client_id= 1 and users.deleted_at is null and users.state = 1 order by users.id asc ) as rule_query on users.id = rule_query.user_id where users.client_id = 1 union select users.id from users where users.points > 100)

Note: I am running MySql (Percona 5.7.22)

[staudenmeir]

What query builder generates this SQL?

[celloman]

@staudenmeir A user model is what is being union'ed together (selecting only ids under various conditions and then unioning those together), and then the resulting builder from this union'ing is passed to some other model (depending on the need) using a whereIn('user_id', ) type of thing

[staudenmeir]

Can you provide a simple example of a query builder that causes the syntax error described in #5457?

[celloman]

App\User::withoutGlobalScopes()->select('users.id')->whereIn('users.id', App\User::select('users.id')->withoutGlobalScopes()->where('id', 1)->union(App\User::select('users.id')->withoutGlobalScopes()->where('id', 2)))->get()

Though this query obviously doesn't accomplish much, this is about as simple of an example as I can think of (the withoutGlobalScopes calls probably aren't necessary, I was just trying to ensure minimal sql output).

produces: select `users`.`id` from `users` where `users`.`id` in ((select `users`.`id` from `users` where `id` = 1) union (select `users`.`id` from `users` where `id` = 2))

[staudenmeir]

@celloman Thanks for the example. Interesting bug, I'm wondering why MySQL can't parse the subquery although it works as a standalone query.

[driesvints]

Did https://github.com/laravel/framework/pull/27589 fix this?

[staudenmeir]

@driesvints Partially, it's still an issue on SQL Server. I'm working on that.

[bhuvidya]

FWIW I'm getting this error with MySQL 5.7.*, and Laravel 5.8. An example query that generates it is:

$user = User::find(1000);

Company::whereIn('company_id', function ($query) use ($user) {
    $games_query = Game::select('company_id')
                        ->whereIn('game_id', function ($query2) use ($user) {
                            $query2->select('game_id')
                                ->from(game_players)
                                ->where('user_id', $user->getKey());
                        });

    $query->select('company_id')
        ->from(company_roles)
        ->where('user_id', $user->getKey())
        ->union($games_query);
})->get();

where the relevant fields of the models are:

User: user_id
Company: company_id
Game: company_id, game_id
GamePlayer: game_id, user_id
CompanyRole: company_id, user_id

The query I end up with from the query builder is:

select * from `companies` where `company_id` in (
  (select `company_id` from `company_roles` where `user_id` = ?) 
  union
  (select `company_id` from `games` where `game_id` in (select `game_id` from `game_players` where `user_id` = ?))
)

When I run this query directly in phpMyAdmin, I also get a syntax error. The problem is the brackets around the first query of the union. Take those away and MySQL is happy. That is:

select * from `companies` where `company_id` in (
  select `company_id` from `company_roles` where `user_id` = ?
  union
  (select `company_id` from `games` where `game_id` in (select `game_id` from `game_players` where `user_id` = ?))
)

will work. It seems like this has been an issue with MySQL for a while, and is fixed in v8.0.0 (see https://bugs.mysql.com/bug.php?id=25734). But I imagine there would be enough folks running MySQL 5.[78].* to make fixing this worthwhile. If I get time over the next few days I'll try and put together a PR. Thanks!

[staudenmeir]

@bhuvidya We can't fix that. Some UNION queries require these parentheses.

[bhuvidya]

@staudenmeir Yeh that's fair enough - it really is a bug in MySQL and as it's fixed in 8.* at some stage most or all folks will be running that version. Thanks!

[staudenmeir]

@celloman Did you see @bhuvidya's comment? The WHERE IN bug has been fixed in MySQL 8.

[staudenmeir]

@driesvints The issue can be closed. With #29496, we now wrap UNION subqueries on all databases.