string literal in where clause

[Ashutosh Chauhan <ha...@apache.org>]

create table t1(a varchar(10), b varchar(10));

select * from t1 where 'foo';

Above query compiles and returns null result set on Hive, mysql, sqllite
and fails on compile time on postgres, oracle, MS sql server. On optiq, I
get stack trace listed at bottom. Will it be acceptable to modify Optiq to
accept such queries?

java.lang.AssertionError: condition must be boolean

at org.eigenbase.rex.RexProgram.isValid(RexProgram.java:458)

at org.eigenbase.rex.RexProgram.<init>(RexProgram.java:101)

at
org.eigenbase.rex.RexProgramBuilder.getProgram(RexProgramBuilder.java:396)

at
org.eigenbase.rex.RexProgramBuilder.getProgram(RexProgramBuilder.java:385)

at
org.eigenbase.rex.RexProgramBuilder.getProgram(RexProgramBuilder.java:366)

at
org.eigenbase.relopt.RelOptUtil.pushFilterPastProject(RelOptUtil.java:2296)

at
org.eigenbase.rel.rules.PushFilterPastProjectRule.onMatch(PushFilterPastProjectRule.java:80)

at
org.eigenbase.relopt.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:321)

at org.eigenbase.relopt.hep.HepPlanner.applyRule(HepPlanner.java:488)

at org.eigenbase.relopt.hep.HepPlanner.applyRules(HepPlanner.java:365)

at
org.eigenbase.relopt.hep.HepPlanner.executeInstruction(HepPlanner.java:258)

at
org.eigenbase.relopt.hep.HepInstruction$RuleCollection.execute(HepInstruction.java:68)

at org.eigenbase.relopt.hep.HepPlanner.executeProgram(HepPlanner.java:179)
at org.eigenbase.relopt.hep.HepPlanner.findBestExp(HepPlanner.java:166)

Re: string literal in where clause

[Julian Hyde <ju...@hydromatic.net>]

I contend that weakening Calcite’s type system makes the system worse for the majority, and maybe for everyone. To illustrate this, what would you expect 

  select * from t1 where ‘false’

to return? I don’t think is clear whether you’d expect ‘false’ to become true (because it is a non-empty string) or become false (applying the rule in Boolean.parseBoolean(String)).

I know you are using Hive, and that Hive does not use Calcite’s SQL parser, so is this question is really about the RelNode/RexNode API to Calcite rather than its SQL parser? (Different users, different rules.)

If we are talking about SQL dialect, I agree that Calcite can support different dialects. We already have policies for which characters you use to quote identifiers (single-quote, double-quote, back tick, brackets), and case-sensitivity, and type systems (e.g. what is the maximum length of a DECIMAL). We could add rules for implicit type-conversions.

If we are talking about RelNode/RexNode API, I think this language should be strongly typed. It is hard to write and debug planner rules if you can’t be sure about whether arguments match their expected type. It is easier to make sure that everything type-checks on the way in.

In this specific case, you could add an implicit call to CAST(… AS BOOLEAN) around your WHERE clause when you generate the RexNode.

Julian



On Oct 6, 2014, at 12:56 PM, Ashutosh Chauhan <ha...@apache.org> wrote:

> create table t1(a varchar(10), b varchar(10));
> 
> select * from t1 where 'foo';
> 
> Above query compiles and returns null result set on Hive, mysql, sqllite
> and fails on compile time on postgres, oracle, MS sql server. On optiq, I
> get stack trace listed at bottom. Will it be acceptable to modify Optiq to
> accept such queries?
> 
> java.lang.AssertionError: condition must be boolean
> 
> at org.eigenbase.rex.RexProgram.isValid(RexProgram.java:458)
> 
> at org.eigenbase.rex.RexProgram.<init>(RexProgram.java:101)
> 
> at
> org.eigenbase.rex.RexProgramBuilder.getProgram(RexProgramBuilder.java:396)
> 
> at
> org.eigenbase.rex.RexProgramBuilder.getProgram(RexProgramBuilder.java:385)
> 
> at
> org.eigenbase.rex.RexProgramBuilder.getProgram(RexProgramBuilder.java:366)
> 
> at
> org.eigenbase.relopt.RelOptUtil.pushFilterPastProject(RelOptUtil.java:2296)
> 
> at
> org.eigenbase.rel.rules.PushFilterPastProjectRule.onMatch(PushFilterPastProjectRule.java:80)
> 
> at
> org.eigenbase.relopt.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:321)
> 
> at org.eigenbase.relopt.hep.HepPlanner.applyRule(HepPlanner.java:488)
> 
> at org.eigenbase.relopt.hep.HepPlanner.applyRules(HepPlanner.java:365)
> 
> at
> org.eigenbase.relopt.hep.HepPlanner.executeInstruction(HepPlanner.java:258)
> 
> at
> org.eigenbase.relopt.hep.HepInstruction$RuleCollection.execute(HepInstruction.java:68)
> 
> at org.eigenbase.relopt.hep.HepPlanner.executeProgram(HepPlanner.java:179)
> at org.eigenbase.relopt.hep.HepPlanner.findBestExp(HepPlanner.java:166)

Re: string literal in where clause

[Vladimir Sitnikov <si...@gmail.com>]

What is the intention behind such kind of query?​

-
Vladimir