Re: Discussion: Distinguish between casts for aligning null types and casts for other type conversion

[Mihai Budiu]

It should be easy to tell whether a cast just changes nullability by comparing 
the type of the argument expression with the specified result type. I don't 
think a special operator is required.

Another question is "what is the semantics of a cast that converts X from 
nullable A to non-nullable A when X turns out to be null at runtime?" This 
inidcates a bug in the type inference, and should probably be a runtime crash.

Mihai

________________________________
From: suibianwanwan33 <suibianwanwa...@foxmail.com>
Sent: Wednesday, October 23, 2024 11:43 AM
To: dev <dev@calcite.apache.org>
Subject: Discussion: Distinguish between casts for aligning null types and 
casts for other type conversion

Hi, All.&nbsp;


Calcite's type nullable validate is very strict. Between the two relNode, we 
must align the type and nullability of each field, which leads to some 
optimizations where we add some casts in order to align the nullables.
For example, in RelToSqlConverterTest, the following case will output cast 
project:


&nbsp; @Test void testUnparseSqlIntervalQualifierMySql1() {
&nbsp; &nbsp; String sql = ""
&nbsp; &nbsp; &nbsp; &nbsp; + "select p.\"product_class_id\" from\n"
&nbsp; &nbsp; &nbsp; &nbsp; + "\"sales_fact_1997\" s left join \"product\" p\n"
&nbsp; &nbsp; &nbsp; &nbsp; + "on s.\"product_id\" = p.\"product_id\" "
&nbsp; &nbsp; &nbsp; &nbsp; + "where p.\"product_class_id\" &gt; 10";
&nbsp; &nbsp; final String expect0 = "SELECT CAST(`t`.`product_class_id` AS 
SIGNED) AS `product_class_id`\n"
&nbsp; &nbsp; &nbsp; &nbsp; + "FROM `foodmart`.`sales_fact_1997`\n"&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; + "INNER JOIN (SELECT *\n"&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; + "FROM `foodmart`.`product`\n"&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; + "WHERE `product_class_id` &gt; 10) "&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; + "AS `t` ON `sales_fact_1997`.`product_id` = 
`t`.`product_id`";


&nbsp; &nbsp; HepProgramBuilder builder = new HepProgramBuilder();
&nbsp; &nbsp; builder.addRuleClass(FilterJoinRule.class);
&nbsp; &nbsp; HepPlanner hepPlanner = new HepPlanner(builder.build());
&nbsp; &nbsp; RuleSet rules =
&nbsp; &nbsp; &nbsp; &nbsp; RuleSets.ofList(CoreRules.FILTER_INTO_JOIN);


&nbsp; &nbsp; sql(sql).withMysql().optimize(rules, hepPlanner).ok(expect0);
&nbsp; }



On the one hand, this can lead to some useless casts in the output SQL, and on 
the other hand, in some scenarios, these casts may affect some optimizations. 
For example, in the materialized rewrite, the following query cannot be 
rewritten because cast generated:


&nbsp; @Test void testJoinOnCalcToJoin01() {
&nbsp; &nbsp; String mv = ""
&nbsp; &nbsp; &nbsp; &nbsp; + "select \"emps\".\"empid\", \"emps\".\"deptno\", 
\"depts\".\"deptno\" from\n"
&nbsp; &nbsp; &nbsp; &nbsp; + "\"emps\" right join \"depts\"\n"
&nbsp; &nbsp; &nbsp; &nbsp; + "on \"emps\".\"deptno\" = \"depts\".\"deptno\" "
&nbsp; &nbsp; &nbsp; &nbsp; + "where \"emps\".\"empid\" &gt; 10";
&nbsp; &nbsp; String query = ""
&nbsp; &nbsp; &nbsp; &nbsp; + "select \"emps\".\"empid\", \"emps\".\"deptno\", 
\"depts\".\"deptno\" from\n"
&nbsp; &nbsp; &nbsp; &nbsp; + "\"emps\" right join \"depts\"\n"
&nbsp; &nbsp; &nbsp; &nbsp; + "on \"emps\".\"deptno\" = \"depts\".\"deptno\""
&nbsp; &nbsp; &nbsp; &nbsp; + "where \"emps\".\"empid\" &gt; 50";
&nbsp; &nbsp; sql(mv, query).noMat();
&nbsp; }



Moreover, sometimes it is not always possible to distinguish whether the cast 
is a nullable relNode type or a user-specified cast.
So, could we provide a NULL_CAST as a conversion like in FILTER_INTO_JOIN just 
to align types?




Thanks,
Suibianwanwan