32

I've been playing around in the rails console trying to get things to work, and I notice that one of my queries keeps returning nil when it shouldn't. Upon looking at the generated SQL query I notice that it has AND (1=0) appended to it each time. This is kind of annoying, and I'm not sure why it's doing this.

Note: Using the actable gem.

Steps to reproduce:

(After connecting to tables in rails console)

2.1.2 :xxxx > @parent = Parent.take
Parent Load (38.1ms)  SELECT  `parents`.* FROM `parents`  LIMIT 1
 => #<Parent id: 37, ...>

2.1.2 :xxxx > @child = Child.where(id: @parent.actable_id)
SQL (0.7ms)  SELECT `childs`.`id` AS t0_r0, `childs`.`attribute` AS t0_r1, FROM `childs`
...
LEFT OUTER JOIN `parents` ON `parents`.`actable_id` = `childs`.`id` AND `parents`.`actable_type` = 'child type' WHERE `childs`.`id` = 20 AND (1=0)
 => #<ActiveRecord::Relation []>

Why is this happening? How do I make it stop?

kingsfoil
  • 3,795
  • 7
  • 32
  • 56
  • 2
    I notice that Rails does this for this query: `User.where(:id => [])`, since you can't write `SELECT * FROM users WHERE id in ()` in SQL. So I guess this is the approach is takes for queries it knows will return nothing, but it can't express in SQL. – MaxGabriel Oct 24 '14 at 18:23
  • @MaxGabriel this was the answer for me. Maybe put this as an answer instead of just a comment? – jgraft Feb 08 '15 at 20:05
  • @jgraft Good idea, I made this an answer like you suggested. – MaxGabriel Feb 08 '15 at 21:53

1 Answers1

68

Rails will generate SQL like AND (1=0) when you query for a column whose value is in an empty array:

Child.where(id: [])

Intuitively, you'd expect that to generate SQL like SELECT * FROM children WHERE id IN (), but () isn't actually valid SQL. Since no rows will match that query, though, Rails works around this by generating an equivalent query that also returns no rows:

SELECT * FROM children WHERE 1=0;
MaxGabriel
  • 7,617
  • 4
  • 35
  • 82
  • Thanks! This answer helped me out with the same confusion! Too bad it was never accepted as the correct answer. – ChrisDekker Aug 18 '16 at 15:43
  • @alex0112 Would you consider accepting this answer? – MaxGabriel Aug 18 '16 at 18:57
  • This also seems to happen if you specify empty hashes Child.joins(:parent).where(:parents => {}) – olivervbk Dec 12 '16 at 16:50
  • Yep, confirmed with a test query. Thanks @OliverKruster – MaxGabriel Dec 13 '16 at 00:53
  • 1
    Thanks for the instant clarification, I was already wondering where it came from but a quick google search saved me probably hours, what I don't understand is, if Rails actually executes the query (runs it against the DB) when it knows it'll return no rows, why bother with running it? – SidOfc Mar 16 '17 at 16:05
  • 1
    I'm not certain, but ideas would be: 1) The query that returns nothing could be a subquery that's part of a bigger query which actually does return results, or modifies database state in some way 2) It adds complexity to the implementation 3) It might be unexpected for developers that a database query doesn't actually hit the database – MaxGabriel Mar 18 '17 at 00:52