Hi, I've ran into RuleMatchImportanceComparator issue (see https://issues.apache.org/jira/browse/CALCITE-2356 )
As a fun experiment, I've replaced the comparator with Random#nextBoolean(), and it identified a bug: https://github.com/apache/calcite/pull/1747/checks?check_run_id=384628081#step:5:789 The key here is the perfectly valid code produces the wrong SQL, so it is a bug either in a rule or in rel-to-sql. What do you think if we add a randomized CI job to the test suite? Of course, randomized execution might produce slightly different plans, so we need to have a system property that would refrain from failing the test if an execution plan is different. On the other hand, if the test method throws an unexpected exception, then it is a true bug identified by randomization. JdbcFrontJdbcBackLinqMiddleTest > testJoinGroupByOrderBy() FAILED java.sql.SQLException: Error while executing SQL "select count(*), c."state_province", sum(s."unit_sales") as s from "foodmart"."sales_fact_1997" as s join "foodmart"."customer" as c on s."customer_id" = c."customer_id" group by c."state_province" order by c."state_province"": While executing SQL [SELECT COUNT(*), "t2"."state_province", "t2"."S" FROM (SELECT "t0"."state_province", COUNT(*), COALESCE(SUM("t"."unit_sales"), 0) AS "S" FROM (SELECT "customer_id", "unit_sales" FROM "foodmart"."sales_fact_1997") AS "t" INNER JOIN (SELECT "customer_id", "state_province" FROM "foodmart"."customer") AS "t0" ON "t"."customer_id" = "t0"."customer_id" GROUP BY "t0"."state_province" ORDER BY "t0"."state_province" NULLS LAST) AS "t2"] on JDBC sub-schema Caused by: java.lang.RuntimeException: While executing SQL [SELECT COUNT(*), "t2"."state_province", "t2"."S" FROM (SELECT "t0"."state_province", COUNT(*), COALESCE(SUM("t"."unit_sales"), 0) AS "S" FROM (SELECT "customer_id", "unit_sales" FROM "foodmart"."sales_fact_1997") AS "t" INNER JOIN (SELECT "customer_id", "state_province" FROM "foodmart"."customer") AS "t0" ON "t"."customer_id" = "t0"."customer_id" GROUP BY "t0"."state_province" ORDER BY "t0"."state_province" NULLS LAST) AS "t2"] on JDBC sub-schema java.sql.SQLSyntaxErrorException: expression not in aggregate or GROUP BY columns: "t2"."state_province" at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) Vladimir
