On Thursday, January 24, 2019 at 4:49:56 PM UTC-8, Janko Marohnić wrote:
>
> 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.
>
#coerce only applies to numeric operations in Ruby, you shouldn't expect it
to apply to String.
What you are running into is "x" =~ foo calls foo =~ "x"
(https://ruby-doc.org/core-2.2.0/String.html#method-i-3D~). And
Sequel::SQL::Function#=~ uses an equality expression. Sequel cannot know
that you called "x" =~ foo instead of foo =~ "x", so that case cannot be
handled correctly. You've already figured out that you can turn the string
into a Sequel expression to work around the issue. You could also use the
hash syntax:
DB[:table].where("string" => Sequel.function(:ANY,
DB[:some_dataset].select(:value)))
Thanks,
Jeremy
--
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.
