Yep, that's a good approach. - Micke
On Tue, Nov 20, 2018 at 5:12 PM Ariel Weisberg <adwei...@fastmail.fm> wrote: > Hi, > > +1 > > This is a public API so we will be much better off if we get it right the > first time. > > Ariel > > > On Nov 16, 2018, at 10:36 AM, Jonathan Haddad <j...@jonhaddad.com> wrote: > > > > Sounds good to me. > > > > On Fri, Nov 16, 2018 at 5:09 AM Benedict Elliott Smith < > bened...@apache.org> > > 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 <ar...@weisberg.ws> 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 <ar...@weisberg.ws> 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 <(214)%20748-3647>::bigint*1.0::double precision > returns double > >>>>>> precision 2147483647 <(214)%20748-3647> > >>>>>> SELECT 2147483647 <(214)%20748-3647>::bigint*1.0 returns numeric > 2147483647 <(214)%20748-3647>.0 > >>>>>> SELECT 2147483647 <(214)%20748-3647>::bigint*1.0::real returns > double > >>>>>> SELECT 2147483647 <(214)%20748-3647>::double precision*1::bigint > returns double > >> 2147483647 <(214)%20748-3647> > >>>>>> SELECT 2147483647 <(214)%20748-3647>::double precision*1.0::bigint > returns double > >> 2147483647 <(214)%20748-3647> > >>>>>> > >>>>>> 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 < > >> bened...@apache.org> > >>>>>> 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.moha...@gmail.com> > >>>>>>> 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.le...@datastax.com> > >>>>>>>> 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-unsubscr...@cassandra.apache.org > >>>>>>> For additional commands, e-mail: dev-h...@cassandra.apache.org > >>>>>>> > >>>>>>> > >>>>> > >>>>> --------------------------------------------------------------------- > >>>>> To unsubscribe, e-mail: dev-unsubscr...@cassandra.apache.org > >>>>> For additional commands, e-mail: dev-h...@cassandra.apache.org > >>>>> > >>>> > >>>> > >>>> --------------------------------------------------------------------- > >>>> To unsubscribe, e-mail: dev-unsubscr...@cassandra.apache.org > >>>> For additional commands, e-mail: dev-h...@cassandra.apache.org > >>>> > >>> > >>> --------------------------------------------------------------------- > >>> To unsubscribe, e-mail: dev-unsubscr...@cassandra.apache.org > >>> For additional commands, e-mail: dev-h...@cassandra.apache.org > >>> > >> > >> > >> --------------------------------------------------------------------- > >> To unsubscribe, e-mail: dev-unsubscr...@cassandra.apache.org > >> For additional commands, e-mail: dev-h...@cassandra.apache.org > >> > >> -- > > Jon Haddad > > http://www.rustyrazorblade.com > > twitter: rustyrazorblade > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: dev-unsubscr...@cassandra.apache.org > For additional commands, e-mail: dev-h...@cassandra.apache.org > >
