Does SQL Logic Test have an opinion about what SQRT(-1) should return? If Calcite is pretending to be Oracle, it should return NaN. If it is pretending to be MySQL it should return NULL. AFAICT, SQL Logic Test would regard (at most) one of those responses as correct. If it even covers SQRT.
On Sun, Feb 25, 2024 at 12:25 PM Guillaume Masse <masse.guilla...@narrative.io.invalid> wrote: > > Write test with SQL logic test > > https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki > > Then run those tests against each database. (This technique is called > oracle testing) > > > On Sun, Feb 25, 2024, 3:13 PM Julian Hyde <jh...@apache.org> wrote: > > > TL;DR: We need to add software engineering processes to ensure that > > Calcite is consistent with other SQL systems. What should those be? > > > > There has been a lot of activity recently adding functions such as > > LOG2 [1] and ensuring that existing functions such as LOG and SQRT [2] > > are consistent with other implementations. Another case was about > > whether Calcite should allow casting a VARCHAR to a VARBINARY [3]. > > > > These discussions have been time-consuming. For example, the LOG2 PR > > received almost a hundred comments, and that is a function that should > > be simple to specify and implement. If a DB did not have LOG2, instead > > of LOG2(n) I would just write LOG(n) / LOG(2) and it would work. > > > > In one of the discussions a statement was made that "SQL rules say > > that SQRT should return NULL for negative values". I checked Oracle -- > > the definitive SQL implementation for many years -- and its doc says > > that it returns NaN. > > > > In another incident[4], the TO_CHAR function was removed from the > > MySQL library and then added back when someone pointed out that it is > > present in MariaDB, and our MySQL library covers MariaDB. > > > > We just can't keep doing this. We need to quickly and efficiently > > arrive at the correct specification for when Calcite is attempting to > > emulate other SQL systems, and then we need to stay with that > > specification. I propose that we use software engineering. > > > > One idea is to create a Quidem script that can be run against MySQL, > > and passes, and when run against Calcite-pretending-to-be-MySQL, it > > also passes. Anyone adding to that script must test it against a MySQL > > instance before merging to main. And similarly for other SQL dialects. > > > > As we encounter underspecified areas - e.g. the behavior of SQRT when > > applied to -0 - we can resolve them by adding to that script. > > > > Does anyone have any ideas for how we can solve this using automated > > tools, backed by changes to our process, rather than by endless > > discussions? > > > > (By the way, this discussion relates to libraries (the "fun" > > parameter), conformance and our parser settings (the "lex" parameter, > > quoted and unquoted casing, etc.). We could have a similar discussion > > about dialects - the SQL generated by the JDBC adapter - but please > > let's have that discussion in another thread.) > > > > Julian > > > > [1] https://issues.apache.org/jira/browse/CALCITE-6224 > > [2] https://issues.apache.org/jira/browse/CALCITE-5638 > > [3] https://issues.apache.org/jira/browse/CALCITE-6210 > > [4] https://issues.apache.org/jira/browse/CALCITE-6222 > >
