Hello again,
Recently I was writing a query with the ANY Postgres operator, and was
trying out the following:
DB[:table].where{"string" =~ ANY(DB[:some_dataset].select(:value))}
The query wasn't working, and then I realized it's because Sequel had
swapped the arguments:
SELECT * FROM table WHERE (ANY((SELECT value FROM some_dataset)) =
'string')
And the ANY operator requires the value to be on the left-hand side.
Anyway, creating an expression object from the string worked:
DB[:table].where{Sequel["string"] =~
ANY(DB[:some_dataset].select(:value))}
# SELECT * FROM table WHERE ('string' = ANY((SELECT value FROM
some_dataset)))
>From the source code I'm not sure why using implicit coercion didn't work.
But I don't think I'm understanding it correctly, because I was expecting
to find #coerce method defined for various types of operands, but I only
found it Numeric objects in SQL::NumericExpression. Since I didn't find
#coerce method for Strings, I would expect an exception to be raised, not
that it still works but with reversed order of operands.
My question is whether there is something that can be modified in Sequel to
have it support that correctly (maybe adding a #coerce method for Strings
to SQL::StringMethods?). I would gladly submit a PR.
I noticed some other inconsistencies as well:
DB[:table].where("string" =~ Sequel[:column]) # this is what we've seen
before
# SELECT * FROM table WHERE (column = 'string')
DB[:table].where(1 =~ Sequel[:column])
# SELECT * FROM table WHERE NULL
DB[:table].where(1 >= Sequel[:column])
# SELECT * FROM table WHERE (1 >= column)
DB[:table].where("string" >= Sequel[:column])
#~> ArgumentError: comparison of String with Sequel::SQL::Identifier
failed
Actually, now that I've dug into operators and coercion a bit more, it
seems that Ruby language itself is shockingly inconsistent in this area.
I've created a gist
<https://gist.github.com/janko-m/fcd843fa2e1743b0ad10500e4094d2ba> showing
the inconsistency.
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
