osdir.com


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Implicit Casts for Arithmetic Operators


Sounds good to me.

On Fri, Nov 16, 2018 at 5:09 AM Benedict Elliott Smith <benedict@xxxxxxxxxx>
wrote:

> So, this thread somewhat petered out.
>
> There are still a number of unresolved issues, but to make progress I
> wonder if it would first be helpful to have a vote on ensuring we are ANSI
> SQL 92 compliant for our arithmetic?  This seems like a sensible baseline,
> since we will hopefully minimise surprise to operators this way.
>
> If people largely agree, I will call a vote, and we can pick up a couple
> of more focused discussions afterwards on how we interpret the leeway it
> gives.
>
>
> > On 12 Oct 2018, at 18:10, Ariel Weisberg <ariel@xxxxxxxxxxx> wrote:
> >
> > Hi,
> >
> > From reading the spec. Precision is always implementation defined. The
> spec specifies scale in several cases, but never precision for any type or
> operation (addition/subtraction, multiplication, division).
> >
> > So we don't implement anything remotely approaching precision and scale
> in CQL when it comes to numbers I think? So we aren't going to follow the
> spec for scale. We are already pretty far down that road so I would leave
> it alone.
> >
> > I don't think the spec is asking for the most approximate type. It's
> just saying the result is approximate, and the precision is implementation
> defined. We could return either float or double. I think if one of the
> operands is a double we should return a double because clearly the schema
> thought a double was required to represent that number. I would also be in
> favor of returning a double all the time so that people can expect a
> consistent type from expressions involving approximate numbers.
> >
> > I am a big fan of widening for arithmetic expressions in a database to
> avoid having to error on overflow. You can go to the trouble of only
> widening the minimum amount, but I think it's simpler if we always widen to
> bigint and double. This would be something the spec allows.
> >
> > Definitely if we can make overflow not occur we should and the spec
> allows that. We should also not return different types for the same operand
> types just to work around overflow if we detect we need more precision.
> >
> > Ariel
> > On Fri, Oct 12, 2018, at 12:45 PM, Benedict Elliott Smith wrote:
> >> If it’s in the SQL spec, I’m fairly convinced.  Thanks for digging this
> >> out (and Mike for getting some empirical examples).
> >>
> >> We still have to decide on the approximate data type to return; right
> >> now, we have float+bigint=double, but float+int=float.  I think this is
> >> fairly inconsistent, and either the approximate type should always win,
> >> or we should always upgrade to double for mixed operands.
> >>
> >> The quoted spec also suggests that decimal+float=float, and decimal
> >> +double=double, whereas we currently have decimal+float=decimal, and
> >> decimal+double=decimal
> >>
> >> If we’re going to go with an approximate operand implying an
> approximate
> >> result, I think we should do it consistently (and consistent with the
> >> SQL92 spec), and have the type of the approximate operand always be the
> >> return type.
> >>
> >> This would still leave a decision for float+double, though.  The most
> >> consistent behaviour with that stated above would be to always take the
> >> most approximate type to return (i.e. float), but this would seem to me
> >> to be fairly unexpected for the user.
> >>
> >>
> >>> On 12 Oct 2018, at 17:23, Ariel Weisberg <ariel@xxxxxxxxxxx> wrote:
> >>>
> >>> Hi,
> >>>
> >>> I agree with what's been said about expectations regarding expressions
> involving floating point numbers. I think that if one of the inputs is
> approximate then the result should be approximate.
> >>>
> >>> One thing we could look at for inspiration is the SQL spec. Not to
> follow dogmatically necessarily.
> >>>
> >>> From the SQL 92 spec regarding assignment
> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt section 4.6:
> >>> "
> >>>        Values of the data types NUMERIC, DECIMAL, INTEGER, SMALLINT,
> >>>        FLOAT, REAL, and DOUBLE PRECISION are numbers and are all
> mutually
> >>>        comparable and mutually assignable. If an assignment would
> result
> >>>        in a loss of the most significant digits, an exception condition
> >>>        is raised. If least significant digits are lost, implementation-
> >>>        defined rounding or truncating occurs with no exception
> condition
> >>>        being raised. The rules for arithmetic are generally governed by
> >>>        Subclause 6.12, "<numeric value expression>".
> >>> "
> >>>
> >>> Section 6.12 numeric value expressions:
> >>> "
> >>>        1) If the data type of both operands of a dyadic arithmetic
> opera-
> >>>           tor is exact numeric, then the data type of the result is
> exact
> >>>           numeric, with precision and scale determined as follows:
> >>> ...
> >>>        2) If the data type of either operand of a dyadic arithmetic op-
> >>>           erator is approximate numeric, then the data type of the re-
> >>>           sult is approximate numeric. The precision of the result is
> >>>           implementation-defined.
> >>> "
> >>>
> >>> And this makes sense to me. I think we should only return an exact
> result if both of the inputs are exact.
> >>>
> >>> I think we might want to look closely at the SQL spec and especially
> when the spec requires an error to be generated. Those are sometimes in the
> spec to prevent subtle paths to wrong answers. Any time we deviate from the
> spec we should be asking why is it in the spec and why are we deviating.
> >>>
> >>> Another issue besides overflow handling is how we determine precision
> and scale for expressions involving two exact types.
> >>>
> >>> Ariel
> >>>
> >>> On Fri, Oct 12, 2018, at 11:51 AM, Michael Burman wrote:
> >>>> Hi,
> >>>>
> >>>> I'm not sure if I would prefer the Postgres way of doing things,
> which is
> >>>> returning just about any type depending on the order of operators.
> >>>> Considering it actually mentions in the docs that using
> numeric/decimal is
> >>>> slow and also multiple times that floating points are inexact. So
> doing
> >>>> some math with Postgres (9.6.5):
> >>>>
> >>>> SELECT 2147483647::bigint*1.0::double precision returns double
> >>>> precision 2147483647
> >>>> SELECT 2147483647::bigint*1.0 returns numeric 2147483647.0
> >>>> SELECT 2147483647::bigint*1.0::real returns double
> >>>> SELECT 2147483647::double precision*1::bigint returns double
> 2147483647
> >>>> SELECT 2147483647::double precision*1.0::bigint returns double
> 2147483647
> >>>>
> >>>> With + - we can get the same amount of mixture of returned types.
> There's
> >>>> no difference in those calculations, just some casting. To me
> >>>> floating-point math indicates inexactness and has errors and whoever
> mixes
> >>>> up two different types should understand that. If one didn't want
> exact
> >>>> numeric type, why would the server return such? The floating point
> value
> >>>> itself could be wrong already before the calculation - trying to say
> we do
> >>>> it lossless is just wrong.
> >>>>
> >>>> Fun with 2.65:
> >>>>
> >>>> SELECT 2.65::real * 1::int returns double 2.65000009536743
> >>>> SELECT 2.65::double precision * 1::int returns double 2.65
> >>>>
> >>>> SELECT round(2.65) returns numeric 4
> >>>> SELECT round(2.65::double precision) returns double 4
> >>>>
> >>>> SELECT 2.65 * 1 returns double 2.65
> >>>> SELECT 2.65 * 1::bigint returns numeric 2.65
> >>>> SELECT 2.65 * 1.0 returns numeric 2.650
> >>>> SELECT 2.65 * 1.0::double precision returns double 2.65
> >>>>
> >>>> SELECT round(2.65) * 1 returns numeric 3
> >>>> SELECT round(2.65) * round(1) returns double 3
> >>>>
> >>>> So as we're going to have silly values in any case, why pretend
> something
> >>>> else? Also, exact calculations are slow if we crunch large amount of
> >>>> numbers. I guess I slightly deviated towards Postgres' implemention
> in this
> >>>> case, but I wish it wasn't used as a benchmark in this case. And most
> >>>> importantly, I would definitely want the exact same type returned
> each time
> >>>> I do a calculation.
> >>>>
> >>>> - Micke
> >>>>
> >>>> On Fri, Oct 12, 2018 at 4:29 PM Benedict Elliott Smith <
> benedict@xxxxxxxxxx>
> >>>> wrote:
> >>>>
> >>>>> As far as I can tell we reached a relatively strong consensus that we
> >>>>> should implement lossless casts by default?  Does anyone have
> anything more
> >>>>> to add?
> >>>>>
> >>>>> Looking at the emails, everyone who participated and expressed a
> >>>>> preference was in favour of the “Postgres approach” of upcasting to
> decimal
> >>>>> for mixed float/int operands?
> >>>>>
> >>>>> I’d like to get a clear-cut decision on this, so we know what we’re
> doing
> >>>>> for 4.0.  Then hopefully we can move on to a collective decision on
> Ariel’s
> >>>>> concerns about overflow, which I think are also pressing -
> particularly for
> >>>>> tinyint and smallint.  This does also impact implicit casts for mixed
> >>>>> integer type operations, but an approach for these will probably
> fall out
> >>>>> of any decision on overflow.
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>> On 3 Oct 2018, at 11:38, Murukesh Mohanan <
> murukesh.mohanan@xxxxxxxxx>
> >>>>> wrote:
> >>>>>>
> >>>>>> I think you're conflating two things here. There's the loss
> resulting
> >>>>> from
> >>>>>> using some operators, and loss involved in casting. Dividing an
> integer
> >>>>> by
> >>>>>> another integer to obtain an integer result can result in loss, but
> >>>>> there's
> >>>>>> no implicit casting there and no loss due to casting.  Casting an
> integer
> >>>>>> to a float can also result in loss. So dividing an integer by a
> float,
> >>>>> for
> >>>>>> example, with an implicit cast has an additional avenue for loss:
> the
> >>>>>> implicit cast for the operands so that they're of the same type. I
> >>>>> believe
> >>>>>> this discussion so far has been about the latter, not the loss from
> the
> >>>>>> operations themselves.
> >>>>>>
> >>>>>> On Wed, 3 Oct 2018 at 18:35 Benjamin Lerer <
> benjamin.lerer@xxxxxxxxxxxx>
> >>>>>> wrote:
> >>>>>>
> >>>>>>> Hi,
> >>>>>>>
> >>>>>>> I would like to try to clarify things a bit to help people to
> understand
> >>>>>>> the true complexity of the problem.
> >>>>>>>
> >>>>>>> The *float *and *double *types are inexact numeric types. Not only
> at
> >>>>> the
> >>>>>>> operation level.
> >>>>>>>
> >>>>>>> If you insert 676543.21 in a *float* column and then read it, you
> will
> >>>>>>> realize that the value has been truncated to 676543.2.
> >>>>>>>
> >>>>>>> If you want accuracy the only way is to avoid those inexact types.
> >>>>>>> Using *decimals
> >>>>>>> *during operations will mitigate the problem but will not remove
> it.
> >>>>>>>
> >>>>>>>
> >>>>>>> I do not recall PostgreSQL behaving has described. If I am not
> mistaken
> >>>>> in
> >>>>>>> PostgreSQL *SELECT 3/2* will return *1*. Which is similar to what
> MS SQL
> >>>>>>> server and Oracle do. So all thoses databases will lose precision
> if you
> >>>>>>> are not carefull.
> >>>>>>>
> >>>>>>> If you truly need precision you can have it by using exact numeric
> types
> >>>>>>> for your data types. Of course it has a cost on performance,
> memory and
> >>>>>>> disk usage.
> >>>>>>>
> >>>>>>> The advantage of the current approach is that it give you the
> choice.
> >>>>> It is
> >>>>>>> up to you to decide what you need for your application. It is also
> in
> >>>>> line
> >>>>>>> with the way CQL behave everywhere else.
> >>>>>>>
> >>>>>> --
> >>>>>>
> >>>>>> Muru
> >>>>>
> >>>>>
> >>>>> ---------------------------------------------------------------------
> >>>>> To unsubscribe, e-mail: dev-unsubscribe@xxxxxxxxxxxxxxxxxxxx
> >>>>> For additional commands, e-mail: dev-help@xxxxxxxxxxxxxxxxxxxx
> >>>>>
> >>>>>
> >>>
> >>> ---------------------------------------------------------------------
> >>> To unsubscribe, e-mail: dev-unsubscribe@xxxxxxxxxxxxxxxxxxxx
> >>> For additional commands, e-mail: dev-help@xxxxxxxxxxxxxxxxxxxx
> >>>
> >>
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: dev-unsubscribe@xxxxxxxxxxxxxxxxxxxx
> >> For additional commands, e-mail: dev-help@xxxxxxxxxxxxxxxxxxxx
> >>
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: dev-unsubscribe@xxxxxxxxxxxxxxxxxxxx
> > For additional commands, e-mail: dev-help@xxxxxxxxxxxxxxxxxxxx
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@xxxxxxxxxxxxxxxxxxxx
> For additional commands, e-mail: dev-help@xxxxxxxxxxxxxxxxxxxx
>
> --
Jon Haddad
http://www.rustyrazorblade.com
twitter: rustyrazorblade