[jira] [Created] (CALCITE-4873) Unconditional operand appending on SqlPosixRegexOperator#createCall method call

[Igor Seliverstov (Jira)]

Igor Seliverstov created CALCITE-4873:
-

 Summary: Unconditional operand appending on 
SqlPosixRegexOperator#createCall method call
 Key: CALCITE-4873
 URL: https://issues.apache.org/jira/browse/CALCITE-4873
 Project: Calcite
  Issue Type: Bug
  Components: babel, core
Reporter: Igor Seliverstov


We tried to use POSIX match syntax in our project and noticed unexpected 
behaviour on query validation in case the query contains SqlPosixRegexOperator.

SqlPosixRegexOperator#createCall implementation appends a case sensitivity flag 
even in case when the fag is already in place, it causes NPE on query 
validation.

the flow looks so:

1) SqlPosixRegexOperator is created by parser, sensitivity flag is added

2) At validation time when the validator tries to expand expression, the 
operator is created once again using original operands (which already contain 
sensitivity flag):
{noformat}
createCall:88, SqlPosixRegexOperator (org.apache.calcite.sql.fun)
result:117, SqlShuttle$CallCopyingArgHandler (org.apache.calcite.sql.util)
result:101, SqlShuttle$CallCopyingArgHandler (org.apache.calcite.sql.util)
visitScoped:5918, SqlValidatorImpl$Expander (org.apache.calcite.sql.validate)
visit:50, SqlScopedShuttle (org.apache.calcite.sql.validate)
visit:33, SqlScopedShuttle (org.apache.calcite.sql.validate)
accept:139, SqlCall (org.apache.calcite.sql)
expand:5493, SqlValidatorImpl (org.apache.calcite.sql.validate)
validateWhereClause:4083, SqlValidatorImpl (org.apache.calcite.sql.validate)
{noformat}
at this point of time the call contains two boolean operands in operand set

3) At type derivation time the operator tries to lookup the original operand 
but fails because of the parameter count filter:
{noformat}
lookupSubjectRoutines:499, SqlUtil (org.apache.calcite.sql)
lookupRoutine:443, SqlUtil (org.apache.calcite.sql)
deriveType:525, SqlOperator (org.apache.calcite.sql)
deriveType:148, SqlBinaryOperator (org.apache.calcite.sql)
visit:5766, SqlValidatorImpl$DeriveTypeVisitor (org.apache.calcite.sql.validate)
visit:5753, SqlValidatorImpl$DeriveTypeVisitor (org.apache.calcite.sql.validate)
accept:139, SqlCall (org.apache.calcite.sql)
deriveTypeImpl:1753, SqlValidatorImpl (org.apache.calcite.sql.validate)
deriveType:1738, SqlValidatorImpl (org.apache.calcite.sql.validate)
validateWhereOrOn:4099, SqlValidatorImpl (org.apache.calcite.sql.validate)
validateWhereClause:4085, SqlValidatorImpl (org.apache.calcite.sql.validate)
{noformat}





--
This message was sent by Atlassian Jira
(v8.3.4#803005)

How to get the real column name and data type from a SQL view ?

[Armstrong]

Hi guys,
 Thanks for your time to read this help message. I'm new to Calcite and
reading some codes and examples from calcite Github repo. I'm wandering how
to get the column real name and type from view.
 For example, I have some SQL as below.  A kafka topic table left join
MySQL table, and use a View join_result_view represent the join result. I
want to get the real column name and data type from this view.
 How to get  result like this ? "user_id", bigint ;  "log_time", bigint
 I already try use SqlParse parse these sqls, and get the SqlNode, but
I can't get the real column name and type. Maybe I missed out the correct
method. Hopefully some one can identify the problem, thanks a lot.

CREATE TABLE user_action_source (
`user_id` STRING,
`item_id` BIGINT,
`bhv_type` STRING,
`bhv_time` INT,
`play_duration` INT,
`ts` BIGINT,
proc_time as PROCTIME()
) WITH (
'connector' = 'kafka',
'topic' = 'user_action_log',
'parallelism' = '5',
'properties.cluster' = 'asv_cluster',
'properties.group.id' = 'demo-job',
'scan.startup.mode' = 'latest-offset',
'format' = 'json',
'json.fail-on-missing-field' = 'false',
'json.ignore-parse-errors' = 'true',
'scan.manually-commit-offsets-interval' = '5000'
);

CREATE TABLE item_source (
`item_id` BIGINT,
`video_name` STRING,
`video_count` INT,
`video_duration` INT,
`authors` STRING
) WITH (
'connector' = 'jdbc',
'url' = 'jdbc:mysql://localhost:3306/mydatabase',
'table-name' = 'item_info'
);

create view join_result_view as
select a.user_id, a.item_id, a.bhv_type, a.ts as log_time, b.video_name,
b.video_count, b.video_duration, b.authors
from user_action_source  as a left join item_source  FOR SYSTEM_TIME AS OF
a.proc_time as b
on a.item_id = b.item_id;

Calcite for macro parsing in SQL ?

[Cyril Catheu]

Hey,

I'm working on a product that executes arbitrary SQL on a "data source".
A data source implements a SQL language, for instance, it can be MySQL,
Pinot, BigQuery, etc...
I'd like to introduce a macro language on top of the SQL.
Very similar to what is done in grafana:
https://grafana.com/docs/grafana/latest/datasources/mysql/#macros
These macros would be resolved before the SQL is executed on the data
source.

A macro looks like a function but is a string replacement, for instance:
 __timeFilter(timeColumn, start, end) -->  timeColumn>=start and
timeColumn Is this a good use case for Calcite? What seemed interesting to me is
that Calcite has all those SqlDialect implementations.

I had this flow in mind:
1. get the SqlDialect of my datasource
*2.* add to this dialect my custom macros (add to an SqlFunction list? )
3. parse
4. replace
5. rebuild

I'm wondering if 2. is possible? I'm not sure to understand how I could add
a list of SqlFunction to a dialect, or build a new dialect from an existing
one dynamically.

If you've taken the time to read this message, thanks a lot!
Have a nice day.

-- 
[image: Startree] 

Cyril de Catheu

Software Engineer, StarTree

cy...@startree.ai | +33 684-829-908

[image: Linkedin] [image:
Twitter] 

Re: Calcite for macro parsing in SQL ?

[Julian Hyde]

Calcite already has something called macros.  They work like Lisp macros 
(substituting the AST) rather than C macros (your model of string replacement). 
See JdbcTest.testTableMacro [1].

Does this approach solve your problem?

Julian

[1] 
https://github.com/apache/calcite/blob/82dd78a14f6aef2eeec2f9c94978d04b4acc5359/core/src/test/java/org/apache/calcite/test/JdbcTest.java#L427
 

> On Nov 3, 2021, at 1:53 PM, Cyril Catheu  wrote:
> 
> Hey,
> 
> I'm working on a product that executes arbitrary SQL on a "data source".
> A data source implements a SQL language, for instance, it can be MySQL,
> Pinot, BigQuery, etc...
> I'd like to introduce a macro language on top of the SQL.
> Very similar to what is done in grafana:
> https://grafana.com/docs/grafana/latest/datasources/mysql/#macros
> These macros would be resolved before the SQL is executed on the data
> source.
> 
> A macro looks like a function but is a string replacement, for instance:
> __timeFilter(timeColumn, start, end) -->  timeColumn>=start and
> timeColumn 
> Grafana uses regex replacements, but I'm considering parsing the SQL with
> Calcite to apply the macros safely and manage nested macros/functions.
> 
> --> Is this a good use case for Calcite? What seemed interesting to me is
> that Calcite has all those SqlDialect implementations.
> 
> I had this flow in mind:
> 1. get the SqlDialect of my datasource
> *2.* add to this dialect my custom macros (add to an SqlFunction list? )
> 3. parse
> 4. replace
> 5. rebuild
> 
> I'm wondering if 2. is possible? I'm not sure to understand how I could add
> a list of SqlFunction to a dialect, or build a new dialect from an existing
> one dynamically.
> 
> If you've taken the time to read this message, thanks a lot!
> Have a nice day.
> 
> -- 
> [image: Startree] 
> 
> Cyril de Catheu
> 
> Software Engineer, StarTree
> 
> cy...@startree.ai | +33 684-829-908
> 
> [image: Linkedin] [image:
> Twitter] 

Extending Grammar Mapping

[Jeremy Dyer]

Hello,

I am working on a custom parser grammar that extends
"PostgresqlSqlDialect". I have a simple case where I want to allow my
grammar to accept the token "SUBSTR" and have it behave exactly like
"SUBSTRING" would. Is there a simple way to map this behavior without
having to create a new "SqlCall" extended class and correlating ftl files?

- Jeremy Dyer

[jira] [Created] (CALCITE-4874) Add recursive digest helper method

[Will Noble (Jira)]

Will Noble created CALCITE-4874:
---

 Summary: Add recursive digest helper method
 Key: CALCITE-4874
 URL: https://issues.apache.org/jira/browse/CALCITE-4874
 Project: Calcite
  Issue Type: Improvement
Reporter: Will Noble


I often find myself having to visual compare long rel node trees while 
debugging. Having a helper method ready to generate the string will save time 
going through and manually copy-pasting and formatting each node.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Created] (CALCITE-4875) NVL Function Incorrectly changes nullability field of its operands

[Jay Narale (Jira)]

Jay Narale created CALCITE-4875:
---

 Summary: NVL Function Incorrectly changes nullability field of its 
operands
 Key: CALCITE-4875
 URL: https://issues.apache.org/jira/browse/CALCITE-4875
 Project: Calcite
  Issue Type: Bug
  Components: core
Reporter: Jay Narale


We rewrite the NVL function in 
_org/apache/calcite/sql2rel/StandardConvertletTable.java:303_  , during the 
rewrite we currently override the operands' nullability to be NOT NULL which is 
not needed. This causes issues if that operand is pushdown , since the input of 
that operand need not be not nullable whereas we forced that operand to be Not 
nullable

 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)