Hi, Tanner Clary I also found that some sql functions return java's double.NaN when it is empty, instead of sql's null. Maybe I will raise a jira to discuss this.
Best wishes, Cancai Cai Cancai Cai <caic68...@gmail.com> 于2024年2月24日周六 00:32写道： > 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. >> > > > > > > >> >> > > > > > > > >> > > > > > > >> > > > > > > >> > > > > > >> > > > > >> > > > >> > > >> > >> >
