Calcite supports infinities, but has some trouble computing using infinities as
literals, because literals are represented as decimal values internally. The
following PR https://github.com/apache/calcite/pull/3663 fixes this problem,
but has not been reviewed. The net outcome of the PR is that programs that
evaluate expresions that produce infinity results (or NaN) will now be
simplified at compilation-time. Previously this evaluation was deferred to
runtime only.
But even without this PR you can certainly write CAST('Infinity' as DOUBLE),
and Calcite will represent the expression correctly (but not simplify it).
Mihai
________________________________
From: Tanner Clary <tannercl...@google.com.INVALID>
Sent: Friday, February 23, 2024 8:07 AM
To: dev@calcite.apache.org <dev@calcite.apache.org>
Subject: Re: Some questions about calcite
Cancai,
I have authored a commit that shows you how to allow functions with
non-null operands to return null [1]. Feel free to use this in your PR.
As for infinity, Calcite supports infinity, although I believe you need to
wrap it in a cast to double (at least in my experience). If some dialect
handles infinity differently maybe we can look at supporting that.
Tanner
[1]
https://github.com/tanclary/calcite/commit/a6ee349f73aa8bf34aea38f33ad39581001c932e
On Thu, Feb 22, 2024 at 6:43 PM Ran Tao <chucheng...@gmail.com> wrote:
> Thanks cancai for opening this discussion.
>
> One additional point, regarding your so-called multi-version implementation
> of a certain library, such as Spark.
> Personally, I think we can implement it according to the latest release
> version.
> There will be updates in the future, which can be aligned in calcite.
> For example, ArrayInsert had a bug in early versions of Spark[1], and
> calcite's implementation based on it was already wrong.
>
> In fact, there are many Library functions in calcite, including
> mysql/pg/spark, etc., and their implementations are outdated.
> It is true that we have not discovered or planned to fix them.
>
> I bring this up because one of these open source projects is always moving
> forward, calcite also needs to synchronize their modifications.
>
> [1] https://issues.apache.org/jira/browse/SPARK-45078
>
> Best Regards,
> Ran Tao
>
>
> Cancai Cai <can...@apache.org> 于2024年2月23日周五 10:10写道：
>
> > Maybe I also overlooked some issues in the whole jira case
> >
> > Cancai Cai
> >
> > On 2024/02/23 02:01:53 Cancai Cai wrote:
> > > I am still happy to implement them. As far as adapting the log2
> function
> > is
> > > concerned, at present I may just not be able to guarantee that log2(0)
> > > returns null, and I have encountered some stuck points here. But you
> can
> > > see that I've raised two other jira issues [CALCITE-6259]
> > > <https://issues.apache.org/jira/browse/CALCITE-6259>， [CALCITE-6277]
> > > <https://issues.apache.org/jira/browse/CALCITE-6277> about this, and
> I'm
> > > going to want to fix the jira issue that I raised, because that's what
> I
> > > found, and I should do it. This is why I keep saying that I can merge
> > this
> > > jira first. Regarding extreme scenarios, I will continue to adapt the
> > type
> > > of discourse in the next jira, because I am not sure whether my current
> > > writing method is appropriate in the next jira.
> > >
> > > Cancai Cai <can...@apache.org> 于2024年2月23日周五 09:50写道：
> > >
> > > > Hi, everyone
> > > > Thank you very much for your answers. In fact, my doubts may not only
> > be
> > > > about adapting log2 (function), as I have discovered a lot when
> > adapting
> > > > log2 functions, such as log10(0) about the difference in return
> values
> > of
> > > > mysql and postgres, about calcite Regarding the handling of
> > infinities, I
> > > > don't know why I haven't discovered them before. As clay said,
> calcite
> > may
> > > > have had its own set of standards before, but I may not be clear
> about
> > > > this. I hope that calcite’s return value of the math function in
> > certain
> > > > calculation situations will meet the requirements of mathematics or
> > SQL.
> > > > rule. If mysql is lazy from the beginning, should we still follow it
> > > > instead of just adapting to an extreme situation?
> > > >
> > > > Best wishes.
> > > > Cancai Cai
> > > >
> > > > On 2024/02/23 00:45:13 Tanner Clary wrote:
> > > > > I feel like I'm missing something about this whole issue. We have
> > > > > implemented so many functions that there's probably an existing
> > pattern
> > > > for
> > > > > just about any issue with dialect parity we encounter. What's the
> > core
> > > > > problem? What behavior is so difficult to emulate and why? Caican
> > let me
> > > > > know if you want to pair I'm happy to manage the edge cases if you
> > want.
> > > > >
> > > > > Tanner
> > > > >
> > > > > On Thu, Feb 22, 2024 at 4:34 PM Julian Hyde <
> jhyde.apa...@gmail.com>
> > > > wrote:
> > > > >
> > > > > > I agree. https://issues.apache.org/jira/browse/CALCITE-6224 and
> > its
> > > > > > accompanying PR muddies the waters because it also mentions
> Spark,
> > > > Postgres
> > > > > > and “many databases”. The case should state that the function is
> > > > consistent
> > > > > > with MySQL and returns NULL if the argument is non-positive.
> > > > > >
> > > > > > > On Feb 22, 2024, at 4:24 PM, Mihai Budiu <mbu...@gmail.com>
> > wrote:
> > > > > > >
> > > > > > > In the case of log2 it's simple, because the documentation says
> > that
> > > > it
> > > > > > comes from the MySQL dialect. So there is a spec and a golden
> > > > > > implementation to compare against.
> > > > > > >
> > > > > > > I certainly won't object to implementing a separate log2
> function
> > > > that
> > > > > > is undefined for 0 and negative values (i.e., can return any
> value
> > for
> > > > such
> > > > > > arguments), let's just not pretend it's the MySQL function.
> > > > > > >
> > > > > > > Mihai
> > > > > > > ________________________________
> > > > > > > From: Julian Hyde <jhyde.apa...@gmail.com>
> > > > > > > Sent: Thursday, February 22, 2024 4:05 PM
> > > > > > > To: dev@calcite.apache.org <dev@calcite.apache.org>
> > > > > > > Subject: Re: Some questions about calcite
> > > > > > >
> > > > > > > But what is the spec of the LOG2 function? It’s not in the SQL
> > > > standard.
> > > > > > So, we need to write our own spec. We can say that LOG2(0)
> returns
> > 42,
> > > > if
> > > > > > we wish, and go implement our own spec.
> > > > > > >
> > > > > > > Yes, Calcite is a compiler, but it is also a standard library,
> > and
> > > > it is
> > > > > > also an extended library. LOG2 is in the latter category. If you,
> > as a
> > > > > > vendor, don’t trust the implementation of LOG2 then you can
> > exclude it
> > > > from
> > > > > > your distribution.
> > > > > > >
> > > > > > > As an open source project we have to BOTH improve the quality
> of
> > our
> > > > > > core and lower the barrier to contributions to the non-core code.
> > We
> > > > have
> > > > > > to recognize that not everything is the same standard. And I
> think
> > > > vendors,
> > > > > > like your company, who want to deliver a high-quality experience
> > > > should put
> > > > > > barriers around what features are trusted.
> > > > > > >
> > > > > > > Julian
> > > > > > >
> > > > > > >> On Feb 22, 2024, at 3:42 PM, Mihai Budiu <mbu...@gmail.com>
> > wrote:
> > > > > > >>
> > > > > > >> If we can't even implement correctly the log2 function
> > according to
> > > > its
> > > > > > spec, there is no hope that we will implement anything correctly.
> > > > > > >>
> > > > > > >> I am not a QA person, but I am spending more than 50% of my
> time
> > > > > > diagnosing and fixing bugs in Calcite. It's not fun. I would
> rather
> > > > > > implement interesting new functionality. But I cannot tell a user
> > of
> > > > our
> > > > > > tools "I have no idea whether the results you get using this tool
> > will
> > > > be
> > > > > > correct. If you are lucky, they will be, don't worry about corner
> > > > cases."
> > > > > > Our goal is to use Calcite in a production environment. If
> Calcite
> > is
> > > > > > designed to be just a research tool, maybe we should make that
> > clear.
> > > > > > >>
> > > > > > >> There are fundamental bugs in Calcite which have been there
> for
> > a
> > > > > > decade. Even basic things like arithmetic casts are still
> > incorrect.
> > > > Which
> > > > > > is proof that once a bug is in, people are not incentivized to
> fix
> > > > them. We
> > > > > > should not let bugs in deliberately. They may essentially never
> get
> > > > fixed.
> > > > > > >>
> > > > > > >> I don't think a compiler can cut any corners. The compiler is
> > the
> > > > > > foundation of an entire software ecosystem. If the foundation is
> > > > broken,
> > > > > > everything crumbles.
> > > > > > >>
> > > > > > >> Mihai
> > > > > > >>
> > > > > > >> ________________________________
> > > > > > >> From: Julian Hyde <jhyde.apa...@gmail.com>
> > > > > > >> Sent: Thursday, February 22, 2024 3:28 PM
> > > > > > >> To: dev@calcite.apache.org <dev@calcite.apache.org>
> > > > > > >> Subject: Re: Some questions about calcite
> > > > > > >>
> > > > > > >> Don’t let the perfect be the enemy of the good. Or as they say
> > in
> > > > open
> > > > > > source, “Release early and often”.
> > > > > > >>
> > > > > > >> Just about everyone who wants a LOG2 function is intending to
> > apply
> > > > it
> > > > > > to positive numbers. So they won’t notice, or care, that the
> > function
> > > > > > doesn’t do exactly what they expected when you apply it to zero.
> > You
> > > > should
> > > > > > release a LOG2 function that does the right thing for the
> positive
> > > > numbers,
> > > > > > if it’s less effort than handling all non-negative numbers.
> > > > > > >>
> > > > > > >> Don’t listen too much to the QA folks. Their job is to find
> the
> > > > corner
> > > > > > cases. But they forget that the corner cases are usually not as
> > > > important
> > > > > > as the core cases. So, let the QA folks log bugs (or you can a
> log
> > > > > > yourself, when you submit an imperfect implementation). Just
> > release
> > > > early
> > > > > > and often.
> > > > > > >>
> > > > > > >> Also, note that the implementation of a function in Java, so
> > that it
> > > > > > can be executed by Calcite, does not have to be the *only*
> > > > implementation.
> > > > > > It is often better to have the JDBC adapter push the function
> down.
> > > > That is
> > > > > > exactly what Bertil is doing for geospatial functions in
> > > > > > https://issues.apache.org/jira/browse/CALCITE-6239, and Tanner
> is
> > > > looking
> > > > > > at making a map so that we know which SQL dialects can implement
> > which
> > > > > > functions.
> > > > > > >>
> > > > > > >> Julian
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > > > >>> On Feb 22, 2024, at 6:11 AM, Cancai Cai <can...@apache.org>
> > wrote:
> > > > > > >>>
> > > > > > >>> When I was working CALCITE-6224
> > > > > > >>> <https://issues.apache.org/jira/browse/CALCITE-6224>, I
> > > > encountered
> > > > > > some
> > > > > > >>> problems and I always had some doubts in my heart.
> > > > > > >>> I thought about it for a long time, maybe I think I already
> > > > understand
> > > > > > the
> > > > > > >>> doubts in my heart.
> > > > > > >>>
> > > > > > >>> As @mihaibudiu said, Java grammar has its own type rules, and
> > SQL
> > > > has
> > > > > > its
> > > > > > >>> own type rules. What calcite currently does is to use Java
> > syntax
> > > > to
> > > > > > adapt
> > > > > > >>> to the SQL rules of each database to complete execution
> > > > optimization.
> > > > > > In
> > > > > > >>> some extreme scenarios, the SQL rules of various databases
> are
> > > > > > >>> inconsistent. Calcite
> > > > > > >>> needs to be sure to adapt to these extreme situations. But, I
> > > > mean, if
> > > > > > one
> > > > > > >>> day, for example, mysql returns the result of log10(0) as an
> > error
> > > > > > instead
> > > > > > >>> of null, then does calcite need to adapt to the new version
> of
> > > > mysql?
> > > > > > If it
> > > > > > >>> adapts to the new version of mysql, does calcite still need
> to
> > > > adapt
> > > > > > to the
> > > > > > >>> old version of mysql? It seems to me that this may be a
> > paradox.
> > > > > > Because in
> > > > > > >>> my opinion, it is very difficult to 100% adapt to the SQL
> > dialect
> > > > of
> > > > > > all
> > > > > > >>> databases, because different dialects of each database need
> to
> > be
> > > > > > >>> considered, and there may even be differences between
> versions
> > of
> > > > > > different
> > > > > > >>> versions of databases.
> > > > > > >>>
> > > > > > >>> Can anyone explain it to me? I would be very grateful.
> > > > > > >>
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
