Problems with Subqueries and simple aggregations on Calcite 1.20

[Enrico Olivelli <eo...@gmail.com>]

Hello,
We are upgrading HerdDB from Calcite 1.19 to 1.20 but we are falling into
serious issues regarding subqueries and aggregations on very simple queries.

I am really sorry I did not have time to test 1.20 before the release this
time.

I will create JIRAs but maybe someone has already an idea of which change
could have broken these cases.

HerdDB is only using Calcite planner, just by configuring it with only
standard rules (no custom rules, no custom traits/convensions, only
EnumerableConvention), basic configuration and an implementation of the
Schema of the tables.


Problem 1: SELECT MIN(n1) as mi, MAX(n1) as ma FROM tblspace1.tsql

Result:

java.lang.IllegalArgumentException: source #1 is already mapped to target #1
	at org.apache.calcite.util.mapping.Mappings$SurjectionWithInverse.set(Mappings.java:1326)
	at org.apache.calcite.rel.core.Project.getMapping(Project.java:279)
	at org.apache.calcite.rel.core.Project.getMapping(Project.java:250)
	at org.apache.calcite.rel.rules.ProjectTableScanRule.apply(ProjectTableScanRule.java:107)
	at org.apache.calcite.rel.rules.ProjectTableScanRule$2.onMatch(ProjectTableScanRule.java:83)
	at org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:208)
	... 30 more



Problem 2 (subquery in DML) : UPDATE tblspace1.table1 set n1=1000 WHERE k1
in (SELECT fk FROM tblspace1.table2 WHERE k2=?)


class org.apache.calcite.sql.SqlBasicCall: `K1` IN (SELECT `table2`.`fk` AS `FK`
FROM `tblspace1`.`table2` AS `TABLE2`
WHERE `table2`.`k2` = ?)
	at org.apache.calcite.util.Util.needToImplement(Util.java:967)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.getValidatedNodeType(SqlValidatorImpl.java:1579)
	at org.apache.calcite.sql2rel.SqlToRelConverter.findSubQueries(SqlToRelConverter.java:1802)
	at org.apache.calcite.sql2rel.SqlToRelConverter.findSubQueries(SqlToRelConverter.java:1776)
	at org.apache.calcite.sql2rel.SqlToRelConverter.replaceSubQueries(SqlToRelConverter.java:1011)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertUpdate(SqlToRelConverter.java:3570)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3172)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:563)
	at org.apache.calcite.prepare.PlannerImpl.rel(PlannerImpl.java:254)
	at herddb.sql.CalcitePlanner.runPlanner(CalcitePlanner.java:506)
	at herddb.sql.CalcitePlanner.translate(CalcitePlanner.java:293)
	at herddb.core.TestUtils.executeUpdate(TestUtils.java:43)


You can find the details in this PR
https://github.com/diennea/herddb/pull/404

Thank you in advance
Enrico