"table.column in Nil" causes MySQLSyntaxErrorException with MySQL

[dflemstr]

The "in" operator assumes that it will not receive an empty list as its argument, and fails when it does.

[max-l]

Dave, what behavior would you suggest for an in () ? Should the "in ()" be replace with an expression that evaluates to false ?, ex.: 1=2 ?
notIn(Nil) should then return a tautology : 1=1 ...

[dflemstr]

Why not just omit the expression node? (The inhibition mechanism might be reusable)

[max-l]

"in(Nil)" always evaluates to false (it is logically false), omitting the node would evaluate to true, woudln't that be problematic ?

[dflemstr]

Well, there could be typed inhibitions, so that when "inhibit = Omissive" (bad naming, I know), the node is simply treated as if it would't change the query, and when "inhibit = Destructive", it is treated as if it would make the query not match. ("bar = 1 or foo in ()" should result in "bar = 1" using such transformations, since "or" can have one destructive operand)

Using Kiama (http://code.google.com/p/kiama/) would also be an alternative, especially for query optimizations and clean-ups in general, but maybe a little overkill?

[max-l]

Fixed as of :
http://github.com/max-l/Squeryl/commit/4cd99be940a3b7e7315290b32c8df7fbea126162

in () ---> always false (results in this condition being written : "(1 = 0)")

notIn () ---> always true (inhibits the condition)

[samhendley]

This only works if its actually a list, if its an Iterable it appears to bypass this check and blows up. An example with Map.keys follows but it seems to happen with any iterable. Workaround is to call .toList on iterable before passing it to squeryl but I dont think it should compile with an iterable if its going to have a runtime fault like this.

test("Empty list inhibition with iterable"){
  transaction {
    FooSchema.reset
    val foo1 = FooSchema.foos.insert(new Foo("test1"))

    val map1 = Map("test1" -> "test1")
    val result1 = FooSchema.foos.where(f => f.value in map1.keys).toList
    result1 should equal(List(foo1))

    //Select
    //  Foo1.data as Foo1_data,
    //  Foo1.id as Foo1_id,
    //  Foo1.value as Foo1_value
    //From
    //  Foo Foo1
    //Where
    //  (Foo1.value in ())   <-- EMPTY!
    intercept[Exception]{
      val map2 = Map.empty[String, String]
      val result2 = FooSchema.foos.where(f => f.value in map2.keys).toList
      result2 should equal(Nil)
    }
  }
}

Full Test Code at https://gist.github.com/795508

Tested against 0.9.4-RC2 and RC3 with mysql adapter.