#733 ✓resolved
danhixon

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)

    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

    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)

    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

    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

    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, name FROM people WHERE (name IN (NULL)) ORDER BY id

    Thanks!

    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)

    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.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

People watching this ticket

Attachments

Pages