While debugging some code the other day I spent some time following a stack trace that pointed to some invalid SQL:
SQLSTATE: Syntax error: 7 ERROR: syntax error at or near ")" LINE 1: select * from "products" where "product_id" in () ^ (SQL: select * from "product" where "product_id" in ())
Admittantly the issue here was pretty straight forward: passing an empty value array to \Illuminate\Database\Query::whereIn() will result in that broken sql being made by the Laravel query builder. This was tested only under the Postgres driver, but I imagine its the same across the board.
The super funtimes began when I went back through the trace and dove under the hood to find out exactly where that faulty SQL was being constructed.
My journey led me down the following trace:
- Illuminate\Database\Query\Builder::whereIn() @ line 654
- Illuminate\Database\Query\Builder::get() @ line 1261
- Illuminate\Database\Query\Builder::runSelect() @ line 1284
- Illuminate\Database\Query\Builder::toSql() @ line 1159
- Illuminate\Database\Query\Grammars\Grammar::compileSelect() @ line 45
- Illuminate\Database\Query\Grammars\Grammar::compileComponents() @ line 67
- Illuminate\Database\Query\Grammars\Grammar::compileWheres() @ line 204
- Illuminate\Database\Query\Grammars\Grammar::whereIn() @ line 307
At this point I had gone full circle from the Query\Builder::whereIn() to the Grammers\Grammar::whereIn(). We can see that the Grammar calling $this->wrap() to assign column aliases and $this->parameterize() to convert the values into a parameter list as it builds out an SQL string.
Our syntax error occurs at the $this->parameterize() call on line 311 of the Grammars/Grammar::whereIn() function.
The parameterize() function is taking the values and imploding them into a comma sparated list and all know that the output of:
string(0) // or ""
So the resulting SQL string is of course:
WHERE column_name IN ()
So after this brief excursion into the underlying Laravel 4 query builder, we observed some interesting things:
- How the Query\Builder uses the Grammars\Grammar to build out SQL syntax.
- How the values are made into a parameter list.
- How the columns are wrapped with any existing aliases.
- Why we recieve faulty SQL when we pass an empty array as values for whereIn().
How about some solutions?
Well in this case, I wouldn't recommend making any changes to the current builder or grammar classes. Logically, you are asking SQL to find the products that in the subset of no products:
WHERE product_id IN ()
If you are writing code that generates this SQL, changes are you have a logic error or should be performing an empty check per business requirements.
Could you add some detection, lets say, an exception raised if the passed values are empty? Sure, you could. But in this case it might be better to let the SQL error propogate up, giving you reason to double check your logic and/or business rules around the problem.