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. > > > > > > >> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
