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