SQL logic test has conditionals. So we could encode each database response (including the one from Calcite)
On Sun, Feb 25, 2024, 3:33 PM Julian Hyde <jh...@apache.org> wrote: > 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 > > > >
