On Sunday, February 15, 2015 at 12:52:27 AM UTC-8, Matt Palmer wrote:
>
> Hi everyone,
>
> Short version: is there a particularly good reason why the modulus
> operator
> (%) isn't supported in Sequel VirtualRow, and neither are the equality
> operators (==, !=)? I've produced a PoC patch to do the former, and would
> have a crack at the latter, if they would be accepted. However, given how
> simple it was to implement modulus, I can't help but think there is a very
> good reason why they aren't in there.
>
They are there, but it may not be obvious how.
>
> Long version:
>
> I tried to define a where clause which contained a modulus calculation,
> like
> so:
>
> db[:ex].where { col % 3 == 0 }
>
> Much to my surprise, this bombed, telling me
>
> NoMethodError: undefined method `%' for #<Sequel::SQL::Identifier
> @value=>:col>
>
This is wrong in two places. First, equality is done via hashes, second,
the modulus operator is only defined for columns known to be numeric.
db[:ex].where{{col.sql_number % 3 => 0}}
>
> "Hmm, OK", says I, "I'll see if I'm doing this totally wrong, by trying
> division":
>
> db[:ex].where { col / 3 == 0 }
>
> This doesn't exception out, but instead produces the SQL
>
> SELECT * FROM "ex" WHERE false
>
> That's... not right.
>
Sequel doesn't override the equality operator (#==), as that would make it
very difficult to determine equality at the ruby level. Equality is done
with hashes in Sequel.
db[:ex].where{{col / 3 => 0}}
> I've verified that the following *does*, in fact, work in PgSQL, at least:
>
> SELECT * FROM ex WHERE col % 3 = 0
>
> With a bit of digging around, I've discovered that, unlike the "basic"
> arithmetic operators (+, -, *, /), modulus isn't part of the SQL standard.
> However, neither is !=, and Sequel seems quite happy to use that in
> preference to the standard version, <>, so my intuition is that Sequel
> isn't
> *absolutely* strict about SQL standards-compliance. Every DB engine I've
> looked into seems to support modulus, either via '%' or 'MOD' (or, if
> you're
> MySQL, *both*). There even appears to be support for converting '%' to
> 'MOD' in the Access adapter, so it's very strange that Sequel is
> overlooking
> it elsewhere. I keep coming back to the idea that there *must* be a good
> reason for not supporting it, but I'm stumped for what that might be.
>
Sequel supports the modulus operator and emulates support on databases that
doesn't support it directly. However, just like the bitwise operators (&,
|, ^, <<, >>), the only expression type that has the methods defined is the
NumericExpression. So before you can use the method, you need to convert
the expression to the appropriate class.
> On the equality operator issue, I know that it's quite a bit trickier to
> support != (since Ruby just calls #== and inverts the result), but even if
> it weren't practical to support, it'd be nice if Sequel complained about
> its
> use, rather than chucking out an absolute 'false' (or 'true', as happens
> if
> you use != somewhere -- that could get nasty).
>
Complaining would break proper use. You can use equality at the ruby level
to determine if two expression objects are equal:
Sequel.expr(:col) + 3 == Sequel.expr(:col) + 3 # true
Sequel.expr(:col) + 3 == Sequel.expr(:col) + 4 # false
If you want to use expressions as hash keys, this is used implicitly.
> I'm happy to implement either of those two ideas -- either trying to get
> ==/!= to work in VirtualRow, or else throwing an exception when they're
> used, so that at least people don't accidentally end up with unexpected
> results. Just let me know which one would be better.
>
We should do neither. Yes, it is a point of confusion for newcomers, but
there is a good reason for the behavior, and changing it would break things.
I'd definitely welcome documentation updates that better explain things,
though.
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 http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
