Jenkins build is back to normal : Calcite » Calcite-snapshots #72

[Apache Jenkins Server]

See 

Re: Pushing filters with unsafe operations (CAST/DIVISION) below inner joins

[Viliam Durina]

I have observed this issue years ago in well-known databases. My case was
much simpler:

data
recordType:int  value:text
-   --
0   1
1   a

SELECT *
FROM data
WHERE recordType='1' AND CAST(value AS INT)<10


SQL is declarative, and unlike procedural languages, it doesn't prescribe
short-circuit evaluation of the WHERE clause, or any specific evaluation
order. If it was prescribed, the query would be perfectly safe. But
prescribing the evaluation order would rule out many optimizations, or make
them much harder, such as this half-null-half-error value.

For example, reordering additions might or might not lead to overflow:
TINYINTs `100 + 100 - 90`, evaluated in this order, overflow, but `100 - 90
+ 100` don't - imagine each value comes from a different table and we
reorder the joins. Perhaps result of TINYINT addition can be SMALLINT, but
what if they are BIGINTs?

My understanding was that any expression must not fail in any plausible
execution plan. Therefore if I parse the column `value`, it must succeed
for every possible scanned row. In my specific case I ended up implementing
a custom TO_NUMBER function that returns null on parsing error, and that
null never appeared in the result because of the other condition.

Viliam

On Thu, 17 Feb 2022 at 20:08, Julian Hyde  wrote:

> You should definitely log a bug, even if there are no plans to fix it. It
> is a violation of the standard, and therefore it is a bug, and therefore we
> should document that it exists.
>
> Can you do some research on the right terminology. You use the term
> ‘unsafe’. I think the PL community uses the term ’strict’ for expressions
> that must be evaluated (and therefore if they throw, the query is sure to
> abort). We have other related concepts floating around in Calcite - dynamic
> functions, deterministic operators - and standardizing terminology is key
> if we are to keep the menagerie in order.
>
> There might be a way to have our cake and eat it too. We could push down
> the division, and if there is a division-by-zero we return a special value
> such as NULL. If the special value is not eliminated by the join then it is
> promoted to a throw. The “cake” here is the performance benefit of pushing
> down a filter (that may never or rarely throw); the “eating it” is safety
> on the occasion that the filter does throw.
>
> Even if that strategy doesn’t work, maybe we could have a flag that says
> whether to push down conditions that might throw.
>
> Julian
>
>
> > On Feb 17, 2022, at 8:07 AM, Scott Reynolds 
> wrote:
> >
> > Is it feasible to prevent the filter push down in cases like this (
> detect
> > potential division by zero) ? What other exceptions that could happen?
> >
> > Should it only push down some filters and preserve the complex filter
> above
> > the join?
> >
> > Regarding a Jira ticket, generating an exception when it should produce
> > valid results is gotta be a Jira case IMHO.
> >
> >
> >
> >
> > On Thu, Feb 17, 2022, 7:17 AM Stamatis Zampetakis 
> wrote:
> >
> >> Hi all,
> >>
> >> Till today, I believed that pushing a filter below an inner join is not
> >> strictly related to the operators (equals, plus, minus, cast, division)
> >> present in the filter.
> >>
> >> However, the query below will return some results if the filter is
> executed
> >> after the join or it will fail with an exception if the filter is pushed
> >> below the join.
> >>
> >> EMP [EMPNO, NAME, DEPTNO]
> >> 0   | Alex | 0
> >> 10 | Bob | 1
> >>
> >> DEP [DEPTNO]
> >> 1
> >>
> >> SELECT e.name
> >> FROM emp e
> >> INNER JOIN dep d ON e.deptno = d.deptno
> >> WHERE (10 / e.empno) = 1
> >>
> >> It seems that SQL standard (Section 7.4 general rule 1) mandates that
> WHERE
> >> should be applied to the result of FROM so in the case above pushing
> >> filters below the join seems to violate the standard.
> >>
> >> Currently, in Calcite we are going to push the division, cast, and
> >> basically any kind of operator below the join. Many well-known databases
> >> e.g., Postgres, do that as well.
> >>
> >> Should we log it as a bug and try to do something about it or let it be
> as
> >> it is?
> >>
> >> Best,
> >> Stamatis
> >>
>
>

-- 
This message contains confidential information and is intended only for the 
individuals named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail. Please notify the sender 
immediately by e-mail if you have received this e-mail by mistake and 
delete this e-mail from your system. E-mail transmission cannot be 
guaranteed to be secure or error-free as information could be intercepted, 
corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. 
The sender therefore does not accept liability for any errors or omissions 
in the contents of this message, which arise as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. -Hazelcast

Re: can we set a default schema for calcite connection to avoid writing schema names in sql

[xiaobo]

Hi Gavin,

  Can you share a complete example of using a FrameworkConfig 
object to open a calcite connection, thanks.

   SchemaPlus rootSchema = Frameworks.createRootSchema(true);
Schema schema = new ReflectiveSchema(new HrSchema2(emps1));
rootSchema.add("hr", schema);

FrameworkConfig builder = Frameworks.newConfigBuilder()
.defaultSchema(rootSchema.getSubSchema("hr"))
.parserConfig(SqlParser.config().withCaseSensitive(false))
.build();




-- Original --
From:  "Gavin Ray";;
Send time: Sunday, Feb 13, 2022 10:25 AM
To: "dev"; 

Subject:  Re: can we set a default schema for calcite connection to avoid 
writing schema names in sql



You can create an empty root schema, add a ReflectiveSchema, and then set
this as the default schema:

val rootSchema: SchemaPlus = Frameworks.createRootSchema(true)
val hrReflectiveSchema = ReflectiveSchema(HrSchema())
rootSchema.add("hr", hrReflectiveSchema)

Frameworks.newConfigBuilder()
.defaultSchema(rootSchema.getSubSchema("hr"))

On Sat, Feb 12, 2022 at 9:14 PM xiaobo  wrote:

> Hi Gavin,
> Thanks for your help,
>
> the defaultSchema(SchemaPlus defaultSchema) method need a SchemaPlus which
> only can be get after a connection is opened, but we want to set a target
> subschema such as RelfectiveShcema to be the default one, and we guess the
> default schema setting operation should be done before the connection is
> opened.
>
>
>
> -- Original --
> From:  "Gavin Ray";;
> Send time: Sunday, Feb 13, 2022 1:43 AM
> To: "dev";
>
> Subject:  Re: can we set a default schema for calcite connection to avoid
> writing schema names in sql
>
>
>
> Hey Xiabo,
>
> You can do this, however it is easiest to do from the "FrameworkConfig"
> object, like this:
>
> import org.apache.calcite.tools.FrameworkConfig
> // Need to set case-sensitive to false, or else it tries to
> // look up capitalized table names and fails
> //
> // IE: "EMPS" instead of "emps"
> val frameworkConfig: FrameworkConfig = Frameworks.newConfigBuilder()
>  .defaultSchema(connection.rootSchema)
>  .parserConfig(SqlParser.config().withCaseSensitive(false))
>  .build()
>
> Hope this helps =)
>
> On Fri, Feb 11, 2022 at 9:09 PM xiaobo 
> wrote:
>
> > sorry for the html escape characters,
> > we tried the following and it does not work
> >
> > Class.forName("org.apache.calcite.jdbc.Driver");
> > Properties info = new Properties();
> > info.setProperty("lex", "JAVA");
> > info.setProperty(InternalProperty.CASE_SENSITIVE.name(),
> > "false");
> > info.setProperty("defaultSchema", "hr");
> > Connection connection =
> > DriverManager.getConnection("jdbc:calcite:", info);
> > CalciteConnection conn =
> > connection.unwrap(CalciteConnection.class);
> > SchemaPlus rootSchema = conn.getRootSchema();
> > Schema schema = new ReflectiveSchema(target);
> > rootSchema.add(schemaName, schema);
> > return conn;
> >
> >
> >
> >
> > -- Original --
> > From:  "xiaobo ";;
> > Send time: Friday, Feb 11, 2022 11:20 PM
> > To: "dev";
> >
> > Subject:  can we set a default schema for calcite connection to avoid
> > writing schema names in sql
> >
> >
> >
> > we have tried the following and it does not work
> >
> >
> > Class.forName("org.apache.calcite.jdbc.Driver");
> >         Properties info
> =
> > new Properties();
> >        
> > info.setProperty("lex", "JAVA");
> >        
> > info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> > "false");
> >        
> > info.setProperty("defaultSchema", "hr");
> >         try {
> >        
> >     Connection connection =
> >        
> >        
> > DriverManager.getConnection("jdbc:calcite:", info);
> >        
> >     CalciteConnection conn =
> >        
> >        
> > connection.unwrap(CalciteConnection.class);
> >        
> >     SchemaPlus rootSchema = conn.getRootSchema();
> >     

[HELP][DISCUSS] ReduceExpressionsRule configurability / extensibility

[Ruben Q L]

Hello community,

I would need some advice for a very specific problem.
I find myself in the following situation: I have a plan with a bottom
filter (an equality) and a top filter (a user defined function MY_FUNC):
...
  LogicalFilter(condition: MY_FUNC($0, 42))
LogicalFilter(condition: $0=1)
  ...

When ReduceExpressionsRule.FilterReduceExpressionsRule gets applied on the
top filter, it pulls up the predicates from the bottom, detects that $0 is
equal to 1, so it replaces it leaving:
  LogicalFilter(condition: MY_FUNC(1, 42))
LogicalFilter(condition: $0=1)

The relevant code seems to be
ReduceExpressionsRule.ReducibleExprLocator#visitCall which considers that
all calls are a priori reducible:
@Override public Void visitCall(RexCall call) {
  // assume REDUCIBLE_CONSTANT until proven otherwise
  analyzeCall(call, Constancy.REDUCIBLE_CONSTANT);
  return null;
}

However, due to some unrelated circumstances, this reduction is incorrect
for my particular UDF, so I do not want it to be converted into MY_FUNC(1,
42), I'd need it to remain as MY_FUNC($0, 42) (i.e. neither the call
itself, nor its parameters can be reduced); the rest of the logic inside
FilterReduceExpressionsRule is perfectly fine for me. So it seems I'm
looking for something like:
@Override public Void visitCall(RexCall call) {
 if (call.op.equals(MY_FUNC) {
   return pushVariable();
  }
  analyzeCall(call, Constancy.REDUCIBLE_CONSTANT);
  return null;
}

Question 1: Is there a way to achieve this result (i.e. let it know to
ReduceExpressionsRule that a certain operator must not be reduced) via rule
configuration or in the UDF operator's definition?

So far, I have not found a positive answer to this question, so my next
thought was "ok, I'll define my own MyFilterReduceExpressionsRule which
extends FilterReduceExpressionsRule and will adjust the few parts of the
code where I need some special treatment, i.e. ReducibleExprLocator''.
Except that, in practice I cannot simply do that, I am forced to copy-paste
most of the original rule code (and then modify a few lines) because of the
following reasons:
- ReduceExpressionsRule subclasses (e.g. FilterReduceExpressionsRule) even
if they are protected, they use some auxiliary methods that are static, so
they cannot be overridden (e.g. FilterReduceExpressionsRule#onMatch calls
the static reduceExpressions that calls the static
reduceExpressionsInternal that calls the static findReducibleExps that
creates the ReducibleExprLocator).
- ReduceExpressionsRule uses some auxiliary static classes (e.g.
ReducibleExprLocator) which are protected (good) but have a package-private
constructor (bad) so in practice they cannot be extended (I cannot create
"MyReducibleExprLocator extends ReducibleExprLocator" to deal with my
special UDF).

Question 2: if the answer to the first question is "no", should we improve
ReduceExpressionsRule to make it more easily adaptable (for cases like my
example)? Maybe converting the static methods into non-static; and
declaring the static classes' constructors protected (so that anything can
be overridden by downstream rule subclasses if required)? Or maybe we could
provide more (optional) configuration capabilities in
ReduceExpressionsRule.Config to achieve this?

Best regards,
Ruben

Re: can we set a default schema for calcite connection to avoid writing schema names in sql

[Gavin Ray]

Sure

Here is a singleton object that uses FrameworkConfig in Kotlin to manage a
single master RootSchema + CalciteConnection:
https://github.com/GavinRay97/GraphQLCalcite/blob/e4ba2426edb546bda9bd5bd87a61764850138348/src/main/kotlin/CalciteSchemaManager.kt#L23-L117

And here is the same in Java:
https://github.com/GavinRay97/GraphQLCalcite/blob/1070d179b67d803f05975cf416c392b010823069/src/main/java/com/example/calcitewrappers/DatabaseManager.java#L22-L60

Hope this is helpful =)

On Fri, Feb 18, 2022 at 10:06 AM xiaobo  wrote:

> Hi Gavin,
>
>   Can you share a complete example of using a FrameworkConfig
> object to open a calcite connection, thanks.
>
>SchemaPlus rootSchema = Frameworks.createRootSchema(true);
> Schema schema = new ReflectiveSchema(new HrSchema2(emps1));
> rootSchema.add("hr", schema);
>
> FrameworkConfig builder = Frameworks.newConfigBuilder()
> .defaultSchema(rootSchema.getSubSchema("hr"))
> .parserConfig(SqlParser.config().withCaseSensitive(false))
> .build();
>
>
>
>
> -- Original --
> From:  "Gavin Ray";;
> Send time: Sunday, Feb 13, 2022 10:25 AM
> To: "dev";
>
> Subject:  Re: can we set a default schema for calcite connection to avoid
> writing schema names in sql
>
>
>
> You can create an empty root schema, add a ReflectiveSchema, and then set
> this as the default schema:
>
> val rootSchema: SchemaPlus = Frameworks.createRootSchema(true)
> val hrReflectiveSchema = ReflectiveSchema(HrSchema())
> rootSchema.add("hr", hrReflectiveSchema)
>
> Frameworks.newConfigBuilder()
> .defaultSchema(rootSchema.getSubSchema("hr"))
>
> On Sat, Feb 12, 2022 at 9:14 PM xiaobo 
> wrote:
>
> > Hi Gavin,
> > Thanks for your help,
> >
> > the defaultSchema(SchemaPlus defaultSchema) method need a SchemaPlus
> which
> > only can be get after a connection is opened, but we want to set a target
> > subschema such as RelfectiveShcema to be the default one, and we guess
> the
> > default schema setting operation should be done before the connection is
> > opened.
> >
> >
> >
> > -- Original --
> > From:  "Gavin Ray";;
> > Send time: Sunday, Feb 13, 2022 1:43 AM
> > To: "dev";
> >
> > Subject:  Re: can we set a default schema for calcite connection to avoid
> > writing schema names in sql
> >
> >
> >
> > Hey Xiabo,
> >
> > You can do this, however it is easiest to do from the "FrameworkConfig"
> > object, like this:
> >
> > import org.apache.calcite.tools.FrameworkConfig
> > // Need to set case-sensitive to false, or else it tries to
> > // look up capitalized table names and fails
> > //
> > // IE: "EMPS" instead of "emps"
> > val frameworkConfig: FrameworkConfig = Frameworks.newConfigBuilder()
> >  .defaultSchema(connection.rootSchema)
> >  .parserConfig(SqlParser.config().withCaseSensitive(false))
> >  .build()
> >
> > Hope this helps =)
> >
> > On Fri, Feb 11, 2022 at 9:09 PM xiaobo 
> > wrote:
> >
> > > sorry for the html escape characters,
> > > we tried the following and it does not work
> > >
> > > Class.forName("org.apache.calcite.jdbc.Driver");
> > > Properties info = new Properties();
> > > info.setProperty("lex", "JAVA");
> > > info.setProperty(InternalProperty.CASE_SENSITIVE.name
> (),
> > > "false");
> > > info.setProperty("defaultSchema", "hr");
> > > Connection connection =
> > > DriverManager.getConnection("jdbc:calcite:", info);
> > > CalciteConnection conn =
> > > connection.unwrap(CalciteConnection.class);
> > > SchemaPlus rootSchema = conn.getRootSchema();
> > > Schema schema = new ReflectiveSchema(target);
> > > rootSchema.add(schemaName, schema);
> > > return conn;
> > >
> > >
> > >
> > >
> > > -- Original --
> > > From:  "xiaobo ";;
> > > Send time: Friday, Feb 11, 2022 11:20 PM
> > > To: "dev";
> > >
> > > Subject:  can we set a default schema for calcite connection to avoid
> > > writing schema names in sql
> > >
> > >
> > >
> > > we have tried the following and it does not work
> > >
> > >
> > > Class.forName("org.apache.calcite.jdbc.Driver");
> > >         Properties
> info
> > =
> > > new Properties();
> > >        
> > > info.setProperty("lex", "JAVA");
> > >        
> > > info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> > > "false");
> > >        
> > > info.setProperty("defaultSchema", "hr");
> > >         try {
> > >        
> > >     Connection connection =
> > >        
> > >        
> > > DriverManager.getConnection("jdbc:calcite:", info);
> > >        
> > >     CalciteConnection conn =
> > >        
> > >        
> > > connection.unwrap(CalciteConnection.class);
> > >        
> > >     SchemaPlus rootSchema = conn.getRootSc

Query Validation Failure

[Yogendra Sharma]

Hi Community,

I am trying to understand the code written in SqlValidatorImpl class. I wrote a 
simple piece of code to test/debug this, and to my surprise, even that simple 
code is failing with error-

"No match found for function signature ="

I simply calling Validator.validate(node)  on a SqlNode received after parsing 
a SELECT query with WHERE clause.

public void validate() throws Exception {
Map tableMap = new HashMap<>();
tableMap.put("TAB1", this.createTable("TAB1", 1));
tableMap.put("TAB2", this.createTable("TAB2", 2));

SimpleSchema schema = new SimpleSchema("Simple", tableMap);
RelDataTypeFactory typeFactory = new JavaTypeFactoryImpl();

CalciteSchema rootSchema = CalciteSchema.createRootSchema(false, false);
rootSchema.add(schema.getSchemaName(), schema);

Prepare.CatalogReader catalogReader = new CalciteCatalogReader(rootSchema,
Collections.singletonList(schema.getSchemaName()), typeFactory, this.config());

SqlValidator.Config validatorConfig = SqlValidator.Config.DEFAULT
.withLenientOperatorLookup(this.config().lenientOperatorLookup())
.withSqlConformance(this.config().conformance())
.withDefaultNullCollation(this.config().defaultNullCollation())
.withIdentifierExpansion(true);

SqlOperatorTable opTable = 
SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(SqlLibrary.ORACLE);

SqlValidator validator = SqlValidatorUtil.newValidator(opTable, catalogReader, 
typeFactory, validatorConfig);

SqlNode node = this.parse("SELECT * FROM TAB1 t1 WHERE t1.COL11 = 100");
validator.validate(node);
System.out.println("Done");
}


Thank you!!

Re: [HELP][DISCUSS] ReduceExpressionsRule configurability / extensibility

[Stamatis Zampetakis]

Hi Ruben,

There was a recent request about preventing simplifications of certain
operators [1] that does make sense in certain use cases. Apart from changes
in RexSimplify this request would most likely need some kind of changes in
various reduce expressions rules like the ones you seem to need as well.

Adding appropriate configuration to the respective rule to avoid reducing
certain expressions seems reasonable to me. Note that some other reduction
rules, such as AggregateReduceFunctionsRule [2], already expose similar
configurations.

Best,
Stamatis

[1] https://lists.apache.org/thread/cyj792yqfc8byfkxcw2jv07c9tfs0np9
[2]
https://github.com/apache/calcite/blob/9c4f3bb540dd67a0ffefc09f4ebd98d2be65bb14/core/src/main/java/org/apache/calcite/rel/rules/AggregateReduceFunctionsRule.java#L871

On Fri, Feb 18, 2022 at 4:23 PM Ruben Q L  wrote:

> Hello community,
>
> I would need some advice for a very specific problem.
> I find myself in the following situation: I have a plan with a bottom
> filter (an equality) and a top filter (a user defined function MY_FUNC):
> ...
>   LogicalFilter(condition: MY_FUNC($0, 42))
> LogicalFilter(condition: $0=1)
>   ...
>
> When ReduceExpressionsRule.FilterReduceExpressionsRule gets applied on the
> top filter, it pulls up the predicates from the bottom, detects that $0 is
> equal to 1, so it replaces it leaving:
>   LogicalFilter(condition: MY_FUNC(1, 42))
> LogicalFilter(condition: $0=1)
>
> The relevant code seems to be
> ReduceExpressionsRule.ReducibleExprLocator#visitCall which considers that
> all calls are a priori reducible:
> @Override public Void visitCall(RexCall call) {
>   // assume REDUCIBLE_CONSTANT until proven otherwise
>   analyzeCall(call, Constancy.REDUCIBLE_CONSTANT);
>   return null;
> }
>
> However, due to some unrelated circumstances, this reduction is incorrect
> for my particular UDF, so I do not want it to be converted into MY_FUNC(1,
> 42), I'd need it to remain as MY_FUNC($0, 42) (i.e. neither the call
> itself, nor its parameters can be reduced); the rest of the logic inside
> FilterReduceExpressionsRule is perfectly fine for me. So it seems I'm
> looking for something like:
> @Override public Void visitCall(RexCall call) {
>  if (call.op.equals(MY_FUNC) {
>return pushVariable();
>   }
>   analyzeCall(call, Constancy.REDUCIBLE_CONSTANT);
>   return null;
> }
>
> Question 1: Is there a way to achieve this result (i.e. let it know to
> ReduceExpressionsRule that a certain operator must not be reduced) via rule
> configuration or in the UDF operator's definition?
>
> So far, I have not found a positive answer to this question, so my next
> thought was "ok, I'll define my own MyFilterReduceExpressionsRule which
> extends FilterReduceExpressionsRule and will adjust the few parts of the
> code where I need some special treatment, i.e. ReducibleExprLocator''.
> Except that, in practice I cannot simply do that, I am forced to copy-paste
> most of the original rule code (and then modify a few lines) because of the
> following reasons:
> - ReduceExpressionsRule subclasses (e.g. FilterReduceExpressionsRule) even
> if they are protected, they use some auxiliary methods that are static, so
> they cannot be overridden (e.g. FilterReduceExpressionsRule#onMatch calls
> the static reduceExpressions that calls the static
> reduceExpressionsInternal that calls the static findReducibleExps that
> creates the ReducibleExprLocator).
> - ReduceExpressionsRule uses some auxiliary static classes (e.g.
> ReducibleExprLocator) which are protected (good) but have a package-private
> constructor (bad) so in practice they cannot be extended (I cannot create
> "MyReducibleExprLocator extends ReducibleExprLocator" to deal with my
> special UDF).
>
> Question 2: if the answer to the first question is "no", should we improve
> ReduceExpressionsRule to make it more easily adaptable (for cases like my
> example)? Maybe converting the static methods into non-static; and
> declaring the static classes' constructors protected (so that anything can
> be overridden by downstream rule subclasses if required)? Or maybe we could
> provide more (optional) configuration capabilities in
> ReduceExpressionsRule.Config to achieve this?
>
> Best regards,
> Ruben
>

Re: Pushing filters with unsafe operations (CAST/DIVISION) below inner joins

[Stamatis Zampetakis]

Thank you all for the feedback.

I will do a small research about the proper term to use for functions that
might throw / never throw and log a JIRA to move the discussion further.

@Viliam : Note that the problem you mention, although similar, is not
exactly the same as the one I brought up now.

The reordering of predicates in the WHERE clause has been brought up quite
a few times in the dev list and our stance [1] is that since the standard
leaves this decision to the implementor people should not rely on this (or
put the appropriate guards).

The case here is a bit different, at least the way I read the standard,
cause it defines the following:

"If all optional clauses are omitted, then the result of the  is the same as the result of the
. Otherwise, each specified clause is applied to the result of
the previously specified clause
and the result of the  is the result of the application
of the last specified clause."

and one of the optional clauses mentioned in the previous paragraph is the
. There seems to be a clearly defined order between the , which includes inner joins, and the .

Best,
Stamatis

[1] https://lists.apache.org/thread/mq44cnrohz19hh10btms126vbcoxl50w

On Fri, Feb 18, 2022 at 9:58 AM Viliam Durina  wrote:

> I have observed this issue years ago in well-known databases. My case was
> much simpler:
>
> data
> recordType:int  value:text
> -   --
> 0   1
> 1   a
>
> SELECT *
> FROM data
> WHERE recordType='1' AND CAST(value AS INT)<10
>
>
> SQL is declarative, and unlike procedural languages, it doesn't prescribe
> short-circuit evaluation of the WHERE clause, or any specific evaluation
> order. If it was prescribed, the query would be perfectly safe. But
> prescribing the evaluation order would rule out many optimizations, or make
> them much harder, such as this half-null-half-error value.
>
> For example, reordering additions might or might not lead to overflow:
> TINYINTs `100 + 100 - 90`, evaluated in this order, overflow, but `100 - 90
> + 100` don't - imagine each value comes from a different table and we
> reorder the joins. Perhaps result of TINYINT addition can be SMALLINT, but
> what if they are BIGINTs?
>
> My understanding was that any expression must not fail in any plausible
> execution plan. Therefore if I parse the column `value`, it must succeed
> for every possible scanned row. In my specific case I ended up implementing
> a custom TO_NUMBER function that returns null on parsing error, and that
> null never appeared in the result because of the other condition.
>
> Viliam
>
> On Thu, 17 Feb 2022 at 20:08, Julian Hyde  wrote:
>
> > You should definitely log a bug, even if there are no plans to fix it. It
> > is a violation of the standard, and therefore it is a bug, and therefore
> we
> > should document that it exists.
> >
> > Can you do some research on the right terminology. You use the term
> > ‘unsafe’. I think the PL community uses the term ’strict’ for expressions
> > that must be evaluated (and therefore if they throw, the query is sure to
> > abort). We have other related concepts floating around in Calcite -
> dynamic
> > functions, deterministic operators - and standardizing terminology is key
> > if we are to keep the menagerie in order.
> >
> > There might be a way to have our cake and eat it too. We could push down
> > the division, and if there is a division-by-zero we return a special
> value
> > such as NULL. If the special value is not eliminated by the join then it
> is
> > promoted to a throw. The “cake” here is the performance benefit of
> pushing
> > down a filter (that may never or rarely throw); the “eating it” is safety
> > on the occasion that the filter does throw.
> >
> > Even if that strategy doesn’t work, maybe we could have a flag that says
> > whether to push down conditions that might throw.
> >
> > Julian
> >
> >
> > > On Feb 17, 2022, at 8:07 AM, Scott Reynolds 
> > wrote:
> > >
> > > Is it feasible to prevent the filter push down in cases like this (
> > detect
> > > potential division by zero) ? What other exceptions that could happen?
> > >
> > > Should it only push down some filters and preserve the complex filter
> > above
> > > the join?
> > >
> > > Regarding a Jira ticket, generating an exception when it should produce
> > > valid results is gotta be a Jira case IMHO.
> > >
> > >
> > >
> > >
> > > On Thu, Feb 17, 2022, 7:17 AM Stamatis Zampetakis 
> > wrote:
> > >
> > >> Hi all,
> > >>
> > >> Till today, I believed that pushing a filter below an inner join is
> not
> > >> strictly related to the operators (equals, plus, minus, cast,
> division)
> > >> present in the filter.
> > >>
> > >> However, the query below will return some results if the filter is
> > executed
> > >> after the join or it will fail with an exception if the filter is
> pushed
> > >> below the join.
> > >>
> > >> EMP [EMPNO, NAME, DEPTNO]
> > >> 0   | Alex | 0
> > >> 10 | Bob | 1
> > >>
> > >> DEP [DEPTNO]
> > >> 1
> > 

Re: Query Validation Failure

[Yogendra Sharma]

Turned out to be the operator table trick. I was passing the wrong operator 
table.

Changing the operator table to below fixed it.
SqlOperatorTable operatorTable = SqlStdOperatorTable.instance();

Thank you!

From: Yogendra Sharma 
Sent: Saturday, February 19, 2022 3:08 AM
To: dev@calcite.apache.org 
Subject: Query Validation Failure

Hi Community,

I am trying to understand the code written in SqlValidatorImpl class. I wrote a 
simple piece of code to test/debug this, and to my surprise, even that simple 
code is failing with error-

"No match found for function signature ="

I simply calling Validator.validate(node)  on a SqlNode received after parsing 
a SELECT query with WHERE clause.

public void validate() throws Exception {
Map tableMap = new HashMap<>();
tableMap.put("TAB1", this.createTable("TAB1", 1));
tableMap.put("TAB2", this.createTable("TAB2", 2));

SimpleSchema schema = new SimpleSchema("Simple", tableMap);
RelDataTypeFactory typeFactory = new JavaTypeFactoryImpl();

CalciteSchema rootSchema = CalciteSchema.createRootSchema(false, false);
rootSchema.add(schema.getSchemaName(), schema);

Prepare.CatalogReader catalogReader = new CalciteCatalogReader(rootSchema,
Collections.singletonList(schema.getSchemaName()), typeFactory, this.config());

SqlValidator.Config validatorConfig = SqlValidator.Config.DEFAULT
.withLenientOperatorLookup(this.config().lenientOperatorLookup())
.withSqlConformance(this.config().conformance())
.withDefaultNullCollation(this.config().defaultNullCollation())
.withIdentifierExpansion(true);

SqlOperatorTable opTable = 
SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(SqlLibrary.ORACLE);

SqlValidator validator = SqlValidatorUtil.newValidator(opTable, catalogReader, 
typeFactory, validatorConfig);

SqlNode node = this.parse("SELECT * FROM TAB1 t1 WHERE t1.COL11 = 100");
validator.validate(node);
System.out.println("Done");
}


Thank you!!

Re: can we set a default schema for calcite connection to avoid writing schema names in sql

[xiaobo]

Thanks Gavin,

From the Java example it seems  FrameworkConfig can't be attached to 
calciteconnection something like passing a Property object when opening the 
connection, and in order to make FrameworkConfig work, we must use the relation 
related APIs of calcite(like the executeQuery(RelNode relRoot)  method), but 
can't use the JDBC APIs of calcite connection, are we right?

 




-- Original --
From:  "Gavin Ray";;
Send time: Saturday, Feb 19, 2022 0:37 AM
To: "dev"; 

Subject:  Re: can we set a default schema for calcite connection to avoid 
writing schema names in sql



Sure

Here is a singleton object that uses FrameworkConfig in Kotlin to manage a
single master RootSchema + CalciteConnection:
https://github.com/GavinRay97/GraphQLCalcite/blob/e4ba2426edb546bda9bd5bd87a61764850138348/src/main/kotlin/CalciteSchemaManager.kt#L23-L117

And here is the same in Java:
https://github.com/GavinRay97/GraphQLCalcite/blob/1070d179b67d803f05975cf416c392b010823069/src/main/java/com/example/calcitewrappers/DatabaseManager.java#L22-L60

Hope this is helpful =)

On Fri, Feb 18, 2022 at 10:06 AM xiaobo  wrote:

> Hi Gavin,
>
>   Can you share a complete example of using a FrameworkConfig
> object to open a calcite connection, thanks.
>
>SchemaPlus rootSchema = Frameworks.createRootSchema(true);
> Schema schema = new ReflectiveSchema(new HrSchema2(emps1));
> rootSchema.add("hr", schema);
>
> FrameworkConfig builder = Frameworks.newConfigBuilder()
> .defaultSchema(rootSchema.getSubSchema("hr"))
> .parserConfig(SqlParser.config().withCaseSensitive(false))
> .build();
>
>
>
>
> -- Original --
> From:  "Gavin Ray";;
> Send time: Sunday, Feb 13, 2022 10:25 AM
> To: "dev";
>
> Subject:  Re: can we set a default schema for calcite connection to avoid
> writing schema names in sql
>
>
>
> You can create an empty root schema, add a ReflectiveSchema, and then set
> this as the default schema:
>
> val rootSchema: SchemaPlus = Frameworks.createRootSchema(true)
> val hrReflectiveSchema = ReflectiveSchema(HrSchema())
> rootSchema.add("hr", hrReflectiveSchema)
>
> Frameworks.newConfigBuilder()
> .defaultSchema(rootSchema.getSubSchema("hr"))
>
> On Sat, Feb 12, 2022 at 9:14 PM xiaobo 
> wrote:
>
> > Hi Gavin,
> > Thanks for your help,
> >
> > the defaultSchema(SchemaPlus defaultSchema) method need a SchemaPlus
> which
> > only can be get after a connection is opened, but we want to set a target
> > subschema such as RelfectiveShcema to be the default one, and we guess
> the
> > default schema setting operation should be done before the connection is
> > opened.
> >
> >
> >
> > -- Original --
> > From:  "Gavin Ray";;
> > Send time: Sunday, Feb 13, 2022 1:43 AM
> > To: "dev";
> >
> > Subject:  Re: can we set a default schema for calcite connection to avoid
> > writing schema names in sql
> >
> >
> >
> > Hey Xiabo,
> >
> > You can do this, however it is easiest to do from the "FrameworkConfig"
> > object, like this:
> >
> > import org.apache.calcite.tools.FrameworkConfig
> > // Need to set case-sensitive to false, or else it tries to
> > // look up capitalized table names and fails
> > //
> > // IE: "EMPS" instead of "emps"
> > val frameworkConfig: FrameworkConfig = Frameworks.newConfigBuilder()
> >  .defaultSchema(connection.rootSchema)
> >  .parserConfig(SqlParser.config().withCaseSensitive(false))
> >  .build()
> >
> > Hope this helps =)
> >
> > On Fri, Feb 11, 2022 at 9:09 PM xiaobo 
> > wrote:
> >
> > > sorry for the html escape characters,
> > > we tried the following and it does not work
> > >
> > > Class.forName("org.apache.calcite.jdbc.Driver");
> > > Properties info = new Properties();
> > > info.setProperty("lex", "JAVA");
> > > info.setProperty(InternalProperty.CASE_SENSITIVE.name
> (),
> > > "false");
> > > info.setProperty("defaultSchema", "hr");
> > > Connection connection =
> > > DriverManager.getConnection("jdbc:calcite:", info);
> > > CalciteConnection conn =
> > > connection.unwrap(CalciteConnection.class);
> > > SchemaPlus rootSchema = conn.getRootSchema();
> > > Schema schema = new ReflectiveSchema(target);
> > > rootSchema.add(schemaName, schema);
> > > return conn;
> > >
> > >
> > >
> > >
> > > -- Original --
> > > From:  "xiaobo ";;
> > > Send time: Friday, Feb 11, 2022 11:20 PM
> > > To: "dev";
> > >
> > > Subject:  can we set a default schema for calcite connection to avoid
> > > writing schema names in sql
> > >
> > >
> > >
> > > we have tried the following and it does not work
> > >
> > >
> > > Class.forName("org.apache.calcite.jdbc.Driver");
> > >         Properties
> info
> > =
> > > 

Re: can we set a default schema for calcite connection to avoid writing schema names in sql

[xiaobo]

I tried the example with the following testing calcitedatabse class, 

String sql = "select count(*) from emps";

Schema schema = new ReflectiveSchema(new HrSchema(emps1, 
depts1));

CalciteDatabase db = new CalciteDatabase(schema, "hr");

try {
Long l = db.exeGetLong(sql);
System.out.println("sql result " + l);

} catch (SQLException | ValidationException | SqlParseException 
| RelConversionException e) {
e.printStackTrace();
}
and the simple counting sql failed with :

java.sql.SQLException: exception while executing query: null
at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at 
org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:576)
at 
org.apache.calcite.avatica.AvaticaPreparedStatement.executeQuery(AvaticaPreparedStatement.java:137)
at 
com.xsmartware.common.calcite.CalciteDatabase.executeQuery(CalciteDatabase.java:77)
at 
com.xsmartware.common.calcite.CalciteDatabase.executeQuery(CalciteDatabase.java:84)
at 
com.xsmartware.common.calcite.CalciteDatabase.exeGetLong(CalciteDatabase.java:90)
at 
com.xsmartware.javatest.calcite.CalCiteTest.test6(CalCiteTest.java:149)
at com.xsmartware.javatest.calcite.CalCiteTest.run(CalCiteTest.java:107)
at 
org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:758)
at 
org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:748)
at 
org.springframework.boot.SpringApplication.run(SpringApplication.java:309)
at 
org.springframework.boot.SpringApplication.run(SpringApplication.java:1301)
at 
org.springframework.boot.SpringApplication.run(SpringApplication.java:1290)
at 
com.xsmartware.javatest.JavaTestApplication.main(JavaTestApplication.java:9)
Caused by: java.lang.NullPointerException
at Baz.bind(Unknown Source)
at 
org.apache.calcite.jdbc.CalcitePrepare$CalciteSignature.enumerable(CalcitePrepare.java:363)
at 
org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:338)
at 
org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:578)
at 
org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:569)
at 
org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:184)
at 
org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:64)
at 
org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:43)
at 
org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:572)
... 12 more







public class CalciteDatabase {
static {
try {
Class.forName("org.apache.calcite.jdbc.Driver");
}catch(Throwable t ) {
throw new RuntimeException(t);
}
}

private final CalciteConnection conn;
private final FrameworkConfig config;


public CalciteDatabase(Schema schema, String schemaName)
throws SQLException {
conn = openConnection();
SchemaPlus rootSchema = Frameworks.createRootSchema(true);
rootSchema.add(schemaName, schema);

config = Frameworks.newConfigBuilder()
.defaultSchema(rootSchema.getSubSchema(schemaName))
.parserConfig(SqlParser.config().withCaseSensitive(false))
.build();
}

public SchemaPlus getRootSchema() {
return config.getDefaultSchema().getParentSchema();
}

public SchemaPlus getDefaultSchema() {
return config.getDefaultSchema();
}


private CalciteConnection openConnection() throws SQLException{
Properties info = new Properties();
info.setProperty("lex", "JAVA");
Connection connection = DriverManager.getConnection("jdbc:calcite:", 
info);
CalciteConnection conn = connection.unwrap(CalciteConnection.class);

return conn;
}

public ResultSet executeQuery(RelNode relRoot) throws SQLException {
relRoot.getCluster().getPlanner().setRoot(
relRoot.getCluster().getPlanner().changeTraits(
relRoot,

relRoot.getCluster().traitSet().replace(EnumerableConvention.INSTANCE)));

final RelNode bestExp = relRoot.getCluster().getPlanner().findBestExp();
final RelRunner runner = conn.unwrap(RelRunner.class);

try (PreparedStatement statement = runner.prepareStatement(bestExp)) {
return statement.executeQuery();
}
}

public ResultSet executeQuery(String sql)
throws SQLException, ValidationException,