A lot of us, myself included, are inclined to include nullability as part of the type. I realized that this is in part because we work with modern programming languages, where a nullable integer is literally a different type to an integer. (E.g. Option<Integer> vs Integer.)
But in the relational world, nullability has traditionally been just a constraint. Given a table ‘emp' we can add the constraint ‘check (empno is not null)’ just as we can add the constraint ‘check (sal > 0)’. During query planning, nullability behaves more like a constraint than a type. We discover additional constraints as we go along. If a rule applied to r1 generates r2, and r2 has additional constraints, then that doesn’t make the rule invalid. Quite the reverse; those additional constraints also apply to r1, because we know r1 and r2 are equivalent. It would be a big change to remove nullability from the type system. I’m not proposing we do it. But we should bear in mind that there is another way. Julian > On Oct 23, 2024, at 12:12 PM, Mihai Budiu <[email protected]> wrote: > > 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 <[email protected]> > Sent: Wednesday, October 23, 2024 11:43 AM > To: dev <[email protected]> > Subject: Discussion: Distinguish between casts for aligning null types and > casts for other type conversion > > Hi, All. > > > 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: > > > @Test void testUnparseSqlIntervalQualifierMySql1() { > String sql = "" > + "select p.\"product_class_id\" from\n" > + "\"sales_fact_1997\" s left join \"product\" > p\n" > + "on s.\"product_id\" = p.\"product_id\" " > + "where p.\"product_class_id\" > 10"; > final String expect0 = "SELECT CAST(`t`.`product_class_id` AS > SIGNED) AS `product_class_id`\n" > + "FROM `foodmart`.`sales_fact_1997`\n" > + "INNER JOIN (SELECT *\n" > + "FROM `foodmart`.`product`\n" > + "WHERE `product_class_id` > 10) " > + "AS `t` ON `sales_fact_1997`.`product_id` = > `t`.`product_id`"; > > > HepProgramBuilder builder = new HepProgramBuilder(); > builder.addRuleClass(FilterJoinRule.class); > HepPlanner hepPlanner = new HepPlanner(builder.build()); > RuleSet rules = > RuleSets.ofList(CoreRules.FILTER_INTO_JOIN); > > > sql(sql).withMysql().optimize(rules, hepPlanner).ok(expect0); > } > > > > 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: > > > @Test void testJoinOnCalcToJoin01() { > String mv = "" > + "select \"emps\".\"empid\", > \"emps\".\"deptno\", \"depts\".\"deptno\" from\n" > + "\"emps\" right join \"depts\"\n" > + "on \"emps\".\"deptno\" = \"depts\".\"deptno\" " > + "where \"emps\".\"empid\" > 10"; > String query = "" > + "select \"emps\".\"empid\", > \"emps\".\"deptno\", \"depts\".\"deptno\" from\n" > + "\"emps\" right join \"depts\"\n" > + "on \"emps\".\"deptno\" = \"depts\".\"deptno\"" > + "where \"emps\".\"empid\" > 50"; > sql(mv, query).noMat(); > } > > > > 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
