[jira] [Created] (CALCITE-4707) Optimize incremental maintenance of materialized views

[Julian Hyde (Jira)]

Julian Hyde created CALCITE-4707:


 Summary: Optimize incremental maintenance of materialized views
 Key: CALCITE-4707
 URL: https://issues.apache.org/jira/browse/CALCITE-4707
 Project: Calcite
  Issue Type: Bug
Reporter: Julian Hyde


Optimize incremental maintenance of materialized views, when we know what DML 
has occurred since the last build.

The goal here is to develop an algebraic approach (i.e. new relational 
operators and transformation rules) that will generate an optimal query for 
maintaining the view.

Consider a materialized view
{code}
CREATE TABLE EmpsByDeptno AS
  SELECT deptno, COUNT(*) AS c, SUM(sal) AS ss
  FROM Emps
  GROUP BY deptno;
{code}

We built it at time T1, when the value of {{Emps}} was {{Emps1}}, and it had 
the value {{EmpsByDeptno1}}.

It is now T2, and since then, new employees have been created in the 
{{NewEmps}} table. Thus {{Emps2 = Emps1 UNION ALL NewEmps}}. We could build a 
new MV,
{code}
CREATE TABLE EmpsByDeptno2 AS
  SELECT deptno, COUNT(*) AS c, SUM(sal) AS ss
  FROM (SELECT * FROM Emps1
  UNION ALL
  SELECT * FROM NewEmps)
  GROUP BY deptno;
{code}but we would prefer to generate a DML command to modify {{EmpsByDeptno}} 
in place:{code}
MERGE INTO EmpsByDeptno AS e
  USING (SELECT deptno, COUNT(*) AS c, SUM(sal) AS ss
  FROM NewEmps) AS de
  ON de.deptno = e.deptno
  WHEN MATCHED THEN
UPDATE SET c = c + de.c, ss = ss + de.ss
  WHEN NOT MATCHED THEN
INSERT (deptno, c, ss)
{code}

We propose two new relational operators:
 * {{Diff(S, R)}} computes the difference between two relations. It has a same 
schema as R and S but with an additional column {{delta}}. Rows in S but not in 
R are called insertions, and have delta=1. Rows that are in R but not in S are 
called deletions, and have delta=-1.
 * {{Patch(R, P)}} applies 

The relational operators are named by analogy with the UNIX utilities {{diff}} 
and {{patch}}. (But {{Diff}}'s arguments are reversed compared to {{diff}}.) 
Consider:
{code}
grep r /usr/share/dict/words  > r.txt
grep s /usr/share/dict/words  > s.txt
diff r.txt s.txt > p.patch
patch -p1 r.txt < p.patch
cmp r.txt s.txt.  # r and s are now identical
{code}

The relation {code}R = Patch(S, Diff(R, S)){code} always holds. {{Diff}} 
computes the difference between R and S, and when {{Patch}} applies that 
difference to {{S}} you get {{R}}.

{{Patch}} and {{Diff}} are intended by be generalizations of set operators. If 
there are only additions, i.e. {{R}} is a superset of {{S}}, then you can 
substitute {{Minus}} for {{Diff}}, and {{Union}} for {{Patch}}:
{code}
R = Union(S, Minus(R, S))
{code}

So, how do these relational operators solve our original problem?

An {{INSERT}} statement is assignment to a relation of itself union some new 
rows. ({{INSERT INTO R SELECT * FROM P}} is {{R  R UNION P}}).

A {{MERGE}} statement is similar to {{INSERT}} but allows updates.

The {{MERGE}} query above is represented as {code}EmpsByDeptno  
Patch(EmpsByDeptno, Diff(NewEmpsByDeptno, EmpsByDeptno)){code}

Lastly we need relational transformation rules to optimize the expressions into 
per-key updates.



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

[jira] [Created] (CALCITE-4706) JDBC adapter generates casts exceeding Redshift's data types bounds

[Stamatis Zampetakis (Jira)]

Stamatis Zampetakis created CALCITE-4706:


 Summary: JDBC adapter generates casts exceeding Redshift's data 
types bounds
 Key: CALCITE-4706
 URL: https://issues.apache.org/jira/browse/CALCITE-4706
 Project: Calcite
  Issue Type: Bug
  Components: core, jdbc-adapter
Affects Versions: 1.27.0
Reporter: Stamatis Zampetakis
Assignee: Stamatis Zampetakis
 Fix For: 1.28.0


Every DBMS imposes different limitation on supported data types and so does 
Redshift. 
The Redshift 
[documentation|https://docs.aws.amazon.com/redshift/latest/dg/c_Supported_data_types.html]
 outlines various limitations around DECIMAL, CHAR, and VARCHAR types. 

In some cases the JDBC adapter may generate type casts exceeding the bounds and 
leading to runtime errors. 

Below some sample queries (executed directly on Redshift) and the respective 
error.

{code:sql}
select cast (col1 as varchar(65536)) from test;
{code}

{noformat}
Error: [Amazon](500310) Invalid operation: length for type varchar cannot 
exceed 65535; (state=22023,code=500310)
{noformat}

{code:sql}
select cast (col1 as char(65536)) from test;
{code}

{noformat}
Error: [Amazon](500310) Invalid operation: length for type bpchar cannot exceed 
4096; (state=22023,code=500310)
{noformat}

{code:sql}
select cast (col2 as decimal(39)) from test;
{code}

{noformat}
Error: [Amazon](500310) Invalid operation: DECIMAL precision 39 must be between 
1 and 38; (state=22023,code=500310)
{noformat}

{code:sql}
select cast (col2 as decimal(38,39)) from test;
{code}

{noformat}
Error: [Amazon](500310) Invalid operation: DECIMAL scale 39 must be between 0 
and 37; (state=22023,code=500310)
{noformat}




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

Re: SQL Dialect Question

[Stamatis Zampetakis]

Hi Charles,

Start by creating a JIRA and then you can do more or less what was done for
EXASOL dialect [1].
Tests for dialects are usually added in RelToSqlConverterTest as you can
see also in [1].
If the new dialect is very similar to an existing one then maybe there is
no reason to create a new one.

Best,
Stamatis

[1]
https://github.com/apache/calcite/commit/f928e073c384010c294370b63ffb748c15caab8a

On Wed, Jul 28, 2021 at 5:25 PM Charles Givre  wrote:

> Hi Calcite Devs!
> I'm interested in writing a SQL dialect for Apache Drill and contributing
> it to Calcite.  What is the process for contributing a dialect?  I'm asking
> because I didn't see any unit tests for dialects.
> Thanks!
> -- C

SQL Dialect Question

[Charles Givre]

Hi Calcite Devs!
I'm interested in writing a SQL dialect for Apache Drill and contributing it to 
Calcite.  What is the process for contributing a dialect?  I'm asking because I 
didn't see any unit tests for dialects. 
Thanks!
-- C

Re: ClassCastException: FamilyOperandTypeChecker cannot be cast to SqlOperandMetadata

[Yanjing Wang]

It may be a bug caused by CALCITE-4427
, I
think lookupSubjectRoutines behavior is a bit strange. when found routines
are less than two, It returns directly whatever the routine SqlKind is.
otherwise filtered by SqlKind. This behavior may cause empty routine
results when all candidates are filtered out. @Julian, could you help the
exception to be fixed?

wrstrs  于2021年7月27日周二 下午3:33写道：

> Hi, all
>
> how to solve this problem?
>
>
>
>
> my code:
> String sql = "select UNIX_TIMESTAMP(concat(substr('2021-07-27
> 12:12:12',0,11),'09:00:00'))";
>
> SqlOperatorTable opTab =
>
> SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(EnumSet.of(
> SqlLibrary.HIVE,
> SqlLibrary.SPARK, SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE,
> SqlLibrary.MYSQL, SqlLibrary.STANDARD));
> SchemaPlus rootSchema = Frameworks.createRootSchema(true);
>
> FrameworkConfig frameworkConfig = Frameworks.newConfigBuilder()
> .defaultSchema(rootSchema)
> .operatorTable(opTab)
> .build();
>
> Planner planner = Frameworks.getPlanner(frameworkConfig);
> SqlNode sqlNode = planner.parse(sql);
> SqlNode validateSqlNode = planner.validate(sqlNode);
>
>
>
> throw exception:
>
> Exception in thread "main" org.apache.calcite.tools.ValidationException:
> java.lang.ClassCastException:
> org.apache.calcite.sql.type.FamilyOperandTypeChecker cannot be cast to
> org.apache.calcite.sql.type.SqlOperandMetadata
>
> at
> org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:228)
>
> Caused by: java.lang.ClassCastException:
> org.apache.calcite.sql.type.FamilyOperandTypeChecker cannot be cast to
> org.apache.calcite.sql.type.SqlOperandMetadata
>
> at
> org.apache.calcite.sql.validate.implicit.TypeCoercionImpl.userDefinedFunctionCoercion(TypeCoercionImpl.java:589)
>
> at
> org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:303)
>
> at
> org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:231)