Thanks, I suspected that it was Ruby's "fault". I'm not sure why they decided to support the coercion protocol only exists for numeric operations, it seems like it would fit nicely for other datatypes as well. Thanks for the suggested alternative.
Kind regards, Janko On 25 Jan 2019, 02:33 +0100, Jeremy Evans <[email protected]>, wrote: > 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 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 a topic in the Google > Groups "sequel-talk" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sequel-talk/w4hdRGfV3QM/unsubscribe. > To unsubscribe from this group and all its topics, 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. -- 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.
