Tanner Clary, Thank you very much for your help. I didn't expect it to work like this. I learned interesting knowledge.
It seems that the infinity conversion is indeed not converted to double, but converted to BigDecimal type. I have left a message on jira and I will try to solve it. Thank you again for your help. I am very sorry that I have caused a lot of trouble to you. Best wishes, Cancai Cai Tanner Clary <tannercl...@google.com.invalid> 于2024年2月24日周六 00:10写道： > 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. > > > > > > > >> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
