Model.all(:name=>[]) invalid SQL executed
Reported by danhixon | December 31st, 2008 @ 12:14 PM
This is not a problem when using sqlite3 but happens with mySQL.
This code: people_to_kill = [] Person.all(:name=>people_to_kill)
sends the following invalid sql statement to mySQL: SELECT "id", "name" FROM "people" WHERE ("name" IN ()) ORDER BY "id"
In sqlite3 this statement is executed: SELECT "id", "name" FROM "people" WHERE ("name" IN (NULL)) ORDER BY "id"
Since this will always be an empty set perhaps no SQL should be sent to the database.
Find a script to reproduce this behavior attached. (You'll have to create a mysql database and change the DataMapper.setup line.
Comments and changes to this ticket
-
Michael Klishin (antares) January 10th, 2009 @ 06:36 AM
- Tag changed from “bug, do_mysql, mysql, problem” to “bug, do_mysql, mysql, query”
what do you expect it to do instead? Raise invalid query exception?
-
danhixon January 10th, 2009 @ 11:11 AM
I would expect an empty set returned as it is in sqlite. But I can understand if you disagree.
-
Dan Kubb (dkubb) January 10th, 2009 @ 12:12 PM
I would expect that a query which could not be satisfied would short-circuit and not even be sent to the backend adapter.
-
Ken Robertson January 10th, 2009 @ 12:19 PM
I tested the sample script this morning on DM 0.9.9 and DataObjects 0.9.10.1 and it worked just fine. It returned an empty array instead of an error.
Have you tried it on the latest gems?
-
danhixon January 13th, 2009 @ 11:54 AM
With the latest gems it generates this SQL which executes without error and returns an empty set:
Tue, 13 Jan 2009 17:51:51 GMT ~ debug ~ (0.000438) SELECT
id,nameFROMpeopleWHERE (nameIN (NULL)) ORDER BYidThanks!
Short circuiting by returning an empty array when an empty array of values is part of the criteria would qualify as an enhancement instead of a bug. This ticket may be closed "RESOLVED!"
-
Piotr Solnica (solnic) January 22nd, 2009 @ 12:41 PM
- State changed from “unconfirmed” to “resolved”
Please Sign in or create a free account to add a new ticket.
With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.
Create new ticket
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
danhixon