[jira] [Commented] (CALCITE-5917) Comments in TimeString class are incorrect

[Benchao Li (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5917?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17753048#comment-17753048
 ] 

Benchao Li commented on CALCITE-5917:
-

We would accept such minor changes, e.g., improving javadoc/method parameter 
names, and usually there is no need to link to a Jira. Take a example, 
[https://github.com/apache/calcite/pull/3308]

 

> Comments in TimeString class are incorrect
> --
>
> Key: CALCITE-5917
> URL: https://issues.apache.org/jira/browse/CALCITE-5917
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Mihai Budiu
>Priority: Trivial
>
> Here is an example of an incorrect comment:
> {code:java}
>   /** Sets the fraction field of a {@code TimeString} to a given number
>* of milliseconds. Nukes the value set via {@link #withNanos}.
>*
>* For example,
>* {@code new TimeString(1970, 1, 1, 2, 3, 4).withMillis(56)}
>* yields {@code TIME '1970-01-01 02:03:04.056'}. */
>   public TimeString withMillis(int millis) {
> {code}
> There are several such comments.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5564) Add parsing and validation for PERCENTILE_CONT/PERCENTILE_DISC functions (enabled in BigQuery)

[Ran Tao (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5564?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17753029#comment-17753029
 ] 

Ran Tao commented on CALCITE-5564:
--

[~tanclary] hi, tanner, what do you think about PERCENTILE_CONT/PERCENTILE_DISC 
to be NonReservedKeywords in CALCITE-5564 in your patch or you did it on 
purpose? (I have checked it, PERCENTILE_CONT/PERCENTILE_DISC are reserved 
keywords from sql-2008) 

If it's wrong, I will open a ticket to solve it, and then can fix CALCITE-5909.

> Add parsing and validation for PERCENTILE_CONT/PERCENTILE_DISC functions 
> (enabled in BigQuery)
> --
>
> Key: CALCITE-5564
> URL: https://issues.apache.org/jira/browse/CALCITE-5564
> Project: Calcite
>  Issue Type: Improvement
>Reporter: Tanner Clary
>Assignee: Tanner Clary
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.35.0
>
>
> Calcite currently has implementations for the {{PERCENTILE_CONT}} and 
> {{PERCENTILE_DISC}} functions. Their syntax may be found 
> [here|https://learn.microsoft.com/en-us/sql/t-sql/functions/percentile-cont-transact-sql?view=sql-server-ver16].
>  
> BigQuery offers these functions as well, but the syntax is slightly 
> different, and may be found 
> [here|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#percentile_cont].
>  The main difference is that instead of using a {{WITHIN GROUP}} clause, the 
> array is passed in directly as the first argument to the function.
> BigQuery Syntax Example: {{SELECT PERCENTILE_CONT(x, .5) OVER() FROM 
> UNNEST([1,2,3,4]) as x;}} would return the median, 2.5.
> Standard Syntax Example: {{SELECT PERCENTILE_CONT(.5) WITHIN GROUP (ORDER BY 
> [some column])}}
> Parsing and validation for the standard functions was added in CALCITE-4644.
> The actual implementation for both the standard and BigQuery forms is covered 
> under CALCITE-4666.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5909) Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE but passes when run from the command line

[Ran Tao (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17753027#comment-17753027
 ] 

Ran Tao commented on CALCITE-5909:
--

[~shenlang] yes. i have noticed this commit either. however i thinks it's a 
workaround. 

> Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE 
> but passes when run from the command line
> --
>
> Key: CALCITE-5909
> URL: https://issues.apache.org/jira/browse/CALCITE-5909
> Project: Calcite
>  Issue Type: Bug
>  Components: tests
>Reporter: LakeShen
>Priority: Minor
>  Labels: pull-request-available
> Attachments: image-2023-08-08-23-32-55-466.png
>
>
> When I run the SqlParserTest,the testNoUnintendedNewReservedKeywords method 
> failed,the exception like this:
> {code:java}
> java.lang.AssertionError: The parser has at least one new reserved keyword. 
> Are you sure it should be reserved? Difference: {code}
> The picture like this:
> !image-2023-08-08-23-32-55-466.png|width=1543,height=496!
> I could fix this problem.More importantly, why is this method failing, but 
> the Calcite pipeline is passing? I think we should look at something we missed



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5907) Unexpected boolean expression simplification for And expression

[Yunhong Zheng (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5907?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17753026#comment-17753026
 ] 

Yunhong Zheng commented on CALCITE-5907:


Hi, [~kgyrtkirk] and [~jhyde]. Sorry for the late reply. Thanks for the 
explanation. Now, I agree with your answering. As I enable implicit type 
coercion in calcite, this example query will get a right plan.

The current issue in Flink is that Flink doesn't support implicit type coercion 
and doesn't prevent type mismatches occurs during the validate phase, which 
will result in an error. I think I need to solve this problem from the 
perspective of Flink. This issue can be closed, Thanks a lot.

> Unexpected boolean expression simplification for And expression
> ---
>
> Key: CALCITE-5907
> URL: https://issues.apache.org/jira/browse/CALCITE-5907
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Yunhong Zheng
>Priority: Major
> Fix For: 1.36.0
>
>
> As FLINK-27402  shown. If we have a table  MyTable(a Int, b Boolean, c 
> String).  Calcite will not simplify this case ( c is Varchar type while 
> SqlLiteral is boolean):
> {code:java}
> SELECT * FROM MyTable WHERE c = true;{code}
> As the logical plan is :
> {code:java}
> LogicalSink(table=[*anonymous_collect$1*], fields=[a, b, c])
> +- LogicalProject(inputs=[0..2])
>    +- LogicalFilter(condition=[=($2, true)])
>       +- LogicalTableScan(table=[[default_catalog, default_database, 
> MyTable]]){code}
> However, Calcite will simplify this case while simplifyAnd :
> {code:java}
> SELECT * FROM MyTable WHERE b = true and c = true;{code}
> As the logical plan is shown below: 'b = true' and 'c = true' both were 
> simplified to 'b' and 'c':
> {code:java}
> LogicalSink(table=[*anonymous_collect$1*], fields=[a, b, c])
> +- LogicalProject(inputs=[0..2])
>    +- LogicalFilter(condition=[AND($1, $2)])
>       +- LogicalTableScan(table=[[default_catalog, default_database, 
> MyTable]]){code}
> This may cause error because of filter condition is a Varchar type literal.
>  
> After reading Calcite's code, I found that. The logic of 
> RexSimplify.implify() and RexSimplify.implifyAnd() is different, where the 
> logic of RexSimplify.implifyAnd() is problematic:
> {code:java}
> // Simplify BOOLEAN expressions if possible
> while (term.getKind() == SqlKind.EQUALS) {
>   RexCall call = (RexCall) term;
>   if (call.getOperands().get(0).isAlwaysTrue()) {
> term = call.getOperands().get(1);
> terms.set(i, term);
> continue;
>   } else if (call.getOperands().get(1).isAlwaysTrue()) {
> term = call.getOperands().get(0);
> terms.set(i, term);
> continue;
>   }
>   break;
> } {code}
> The above code cannot make such a simple judgment, as there may not be an 
> implicit conversion to ensure that the types on both sides of the condition 
> are consistent.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5880) When the association condition of the association subquery is 'is not distinct from', the join condition becomes '=' after decorrelation.

[LakeShen (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17753024#comment-17753024
 ] 

LakeShen commented on CALCITE-5880:
---

I thought about this earlier when I debug, the null case is a little bit 
special, because the top is the left join, even if there is no match to the 
right table, it will fill in the null, so I'm not sure is not null filtering 
out the data, Or should the Join add macth to null.

I need to debug further to get more information.This issue is interesting,If 
you have any idea, communicate it together.

> When the association condition of the association subquery is 'is not 
> distinct from', the join condition becomes '=' after decorrelation.
> -
>
> Key: CALCITE-5880
> URL: https://issues.apache.org/jira/browse/CALCITE-5880
> Project: Calcite
>  Issue Type: Bug
>Reporter: libopeng
>Priority: Major
> Attachments: image-2023-08-01-15-20-22-105.png, 
> image-2023-08-02-10-15-00-455.png
>
>
> {code:java}
> select EMPNO 
> from emp
> where EXISTS (select DEPTNO from dept where emp.EMPNO is not DISTINCT from 
> dept.DEPTNO){code}
> before decorrelation
> {code:java}
> LogicalProject(EMPNO=[$0])
>   LogicalFilter(condition=[IS NOT NULL($9)])
>     LogicalCorrelate(correlation=[$cor0], joinType=[left], 
> requiredColumns=[{0}])
>       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>       LogicalAggregate(group=[{}], agg#0=[MIN($0)])
>         LogicalProject($f0=[true])
>           LogicalFilter(condition=[OR(AND(IS NULL($cor0.EMPNO), IS NULL($0)), 
> IS TRUE(=($cor0.EMPNO, $0)))])<---   here is 't1.x is not distinct from 
> t2.x'
>             LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> after decorrelation
> {code:java}
> LogicalProject(EMPNO=[$0])
>   LogicalFilter(condition=[IS NOT NULL($9)])
>     LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], 
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$13])
>       LogicalJoin(condition=[AND(=($0, $11), =($9, $12))], joinType=[left])   
>  <---here is 't1.x=t2.x and (t1.x is null = t2.x is null)'
>         LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], 
> HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[false], 
> EMPNO0=[$0])
>           LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>         LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
>           LogicalProject(EMPNO=[$2], $f9=[$3], $f0=[true])
>             LogicalJoin(condition=[=($2, $0)], joinType=[inner])
>               LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
>               LogicalProject(EMPNO=[$0], $f9=[false])
>                 LogicalTableScan(table=[[CATALOG, SALES, EMP]])  {code}
>  
> {code:java}
>     EMP               | DEPT
> +-+   |  +-+
> | EMPNO  ||  | DEPTNO |
> +-+   |  +-+
> | null |  |  | null   |
>{code}
> expect result
> {code:java}
>   EMPNO
> +---+
> |  null |
> +---+{code}
> actual result
> {code:java}
>   EMPNO
> +---+
> +---+ {code}
>  
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5743) Query gives incorrect result when COUNT appears in the correlated subquery select list

[libopeng (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17753023#comment-17753023
 ] 

libopeng commented on CALCITE-5743:
---

[~shenlang] 
{code:java}
select EMPNO 
from emp where EMPNO in (select count(*) from dept where emp.DEPTNO = 
dept.DEPTNO){code}
Sample data

 
{code:java}
   emp                 | dept
++ |  ++
| DEPTNO | EMPNO       |  | DEPTNO |
++ |  ++
| 3 |   6| |  | 3 |
| 10 |  1| |  | 3 |
| 8 |   0| |  | 10 | 
   | {code}
 

 

 

> Query gives incorrect result when COUNT appears in the correlated subquery 
> select list
> --
>
> Key: CALCITE-5743
> URL: https://issues.apache.org/jira/browse/CALCITE-5743
> Project: Calcite
>  Issue Type: Bug
>Reporter: libopeng
>Priority: Major
>
> {code:java}
> SELECT a 
> FROM t1 t1 
> WHERE b IN (SELECT COUNT (*) FROM t2 WHERE t1.a=t2.a); {code}
> {code:java}
>   t1   | t2
> +--+   |  +-+
> | a | b |  |  | a |
> +--+   |  +-+
> | 3 | 6 |  |  | 3 |
> | 10 | 1 | |  | 3 |
> | 8 | 0 |  |  | 10 | 
>|
> {code}
> correct result
> {code:java}
> +--+
> | a |
> +--+
> | 10 |
> | 8 |{code}
> after decorrelate
> {code:java}
> LogicalProject(A=[$0])
>   LogicalJoin(condition=[AND(=($0, $3), =($1, $2))], joinType=[inner])
>     LogicalTableScan(table=[[t1]])
>     LogicalFilter(condition=[=($0, $0)])
>       LogicalProject(EXPR$0=[$1], a=[$0])
>         LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
>           LogicalProject(a=[$0])
>             LogicalFilter(condition=[=($0, $0)])
>               LogicalTableScan(table=[[t2]]) {code}
> error result
> {code:java}
> +--+
> | a |
> +--+
> | 10 | {code}
> Data with count=0 will be lost
> This issue was discovered in [this 
> issue|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5568]
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-5880) When the association condition of the association subquery is 'is not distinct from', the join condition becomes '=' after decorrelation.

[libopeng (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-5880?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

libopeng updated CALCITE-5880:
--
Description: 
{code:java}
select EMPNO 
from emp
where EXISTS (select DEPTNO from dept where emp.EMPNO is not DISTINCT from 
dept.DEPTNO){code}
before decorrelation
{code:java}
LogicalProject(EMPNO=[$0])
  LogicalFilter(condition=[IS NOT NULL($9)])
    LogicalCorrelate(correlation=[$cor0], joinType=[left], 
requiredColumns=[{0}])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      LogicalAggregate(group=[{}], agg#0=[MIN($0)])
        LogicalProject($f0=[true])
          LogicalFilter(condition=[OR(AND(IS NULL($cor0.EMPNO), IS NULL($0)), 
IS TRUE(=($cor0.EMPNO, $0)))])<---   here is 't1.x is not distinct from 
t2.x'
            LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{code}
after decorrelation
{code:java}
LogicalProject(EMPNO=[$0])
  LogicalFilter(condition=[IS NOT NULL($9)])
    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], 
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$13])
      LogicalJoin(condition=[AND(=($0, $11), =($9, $12))], joinType=[left]) 
   <---here is 't1.x=t2.x and (t1.x is null = t2.x is null)'
        LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], 
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[false], 
EMPNO0=[$0])
          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
        LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
          LogicalProject(EMPNO=[$2], $f9=[$3], $f0=[true])
            LogicalJoin(condition=[=($2, $0)], joinType=[inner])
              LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
              LogicalProject(EMPNO=[$0], $f9=[false])
                LogicalTableScan(table=[[CATALOG, SALES, EMP]])  {code}
 
{code:java}
    EMP               | DEPT
+-+   |  +-+
| EMPNO  ||  | DEPTNO |
+-+   |  +-+
| null |  |  | null   |
   {code}
expect result
{code:java}
  EMPNO
+---+
|  null |
+---+{code}
actual result
{code:java}
  EMPNO
+---+
+---+ {code}
 
 

  was:
{code:java}
select EMPNO 
from emp
where EXISTS (select DEPTNO from dept where emp.EMPNO is not DISTINCT from 
dept.DEPTNO){code}
before decorrelation
{code:java}
LogicalProject(EMPNO=[$0])
  LogicalFilter(condition=[IS NOT NULL($9)])
    LogicalCorrelate(correlation=[$cor0], joinType=[left], 
requiredColumns=[{0}])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      LogicalAggregate(group=[{}], agg#0=[MIN($0)])
        LogicalProject($f0=[true])
          LogicalFilter(condition=[OR(AND(IS NULL($cor0.EMPNO), IS NULL($0)), 
IS TRUE(=($cor0.EMPNO, $0)))])<---   here is 't1.x is not distinct from 
t2.x'
            LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{code}
after decorrelation
{code:java}
LogicalProject(EMPNO=[$0])
  LogicalFilter(condition=[IS NOT NULL($9)])
    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], 
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$13])
      LogicalJoin(condition=[AND(=($0, $11), =($9, $12))], joinType=[left]) 
   <---here is 't1.x=t2.x and (t1.x is null = t2.x is null)'
        LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], 
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[false], 
EMPNO0=[$0])
          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
        LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
          LogicalProject(EMPNO=[$2], $f9=[$3], $f0=[true])
            LogicalJoin(condition=[=($2, $0)], joinType=[inner])
              LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
              LogicalProject(EMPNO=[$0], $f9=[false])
                LogicalTableScan(table=[[CATALOG, SALES, EMP]])  {code}
 
{code:java}
    EMP               | DEPT
+-+   |  +-+
| EMPNO  ||  | DEPTNO |
+-+   |  +-+
| null |  |  | null   |
   {code}
expect result
{code:java}
  EMPNO
+---+
|  null |
+---+{code}
actual result
{code:java}
  EMPNO
+---+
+---+ {code}
 


> When the association condition of the association subquery is 'is not 
> distinct from', the join condition becomes '=' after decorrelation.
> -
>
> Key: CALCITE-5880
> URL: https://issues.apache.org/jira/browse/CALCITE-5880
> Project: Calcite
>  Issue Type: Bug
>Reporter: libopeng
>Priority: Major
> Attachments: image-2023-08-01-15-20-22-105.png, 
> image-2023-08-02-10-15-00-455.png
>
>
> {code:java}
> select EMPNO 
> from emp
> where EXISTS (select DEPTNO from dept where emp.EMPNO is not DISTINCT 

[jira] [Commented] (CALCITE-5909) Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE but passes when run from the command line

[LakeShen (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17753019#comment-17753019
 ] 

LakeShen commented on CALCITE-5909:
---

Hi [~taoran] ,thank you so much for going so deep into this issue,I will 
reproduce this problem locally today, and I will communicate with you when I 
have an idea.

I think we should understand why testNoUnintendedNewReservedKeywords this 
method has a limitation for the child test class not run.I took a look for code 
git commits:
{code:java}
[CALCITE-2457] JUnit 4 -> 5: migrate Assume -> Assumptions, drop suites, remove 
junit4 from core classpath {code}
Let's see if we could get some information from JIRA above.

Thanks again for your professionalism.

> Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE 
> but passes when run from the command line
> --
>
> Key: CALCITE-5909
> URL: https://issues.apache.org/jira/browse/CALCITE-5909
> Project: Calcite
>  Issue Type: Bug
>  Components: tests
>Reporter: LakeShen
>Priority: Minor
>  Labels: pull-request-available
> Attachments: image-2023-08-08-23-32-55-466.png
>
>
> When I run the SqlParserTest,the testNoUnintendedNewReservedKeywords method 
> failed,the exception like this:
> {code:java}
> java.lang.AssertionError: The parser has at least one new reserved keyword. 
> Are you sure it should be reserved? Difference: {code}
> The picture like this:
> !image-2023-08-08-23-32-55-466.png|width=1543,height=496!
> I could fix this problem.More importantly, why is this method failing, but 
> the Calcite pipeline is passing? I think we should look at something we missed



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5880) When the association condition of the association subquery is 'is not distinct from', the join condition becomes '=' after decorrelation.

[libopeng (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17753014#comment-17753014
 ] 

libopeng commented on CALCITE-5880:
---

I think the problem is caused by decorrelation

> When the association condition of the association subquery is 'is not 
> distinct from', the join condition becomes '=' after decorrelation.
> -
>
> Key: CALCITE-5880
> URL: https://issues.apache.org/jira/browse/CALCITE-5880
> Project: Calcite
>  Issue Type: Bug
>Reporter: libopeng
>Priority: Major
> Attachments: image-2023-08-01-15-20-22-105.png, 
> image-2023-08-02-10-15-00-455.png
>
>
> {code:java}
> select EMPNO 
> from emp
> where EXISTS (select DEPTNO from dept where emp.EMPNO is not DISTINCT from 
> dept.DEPTNO){code}
> before decorrelation
> {code:java}
> LogicalProject(EMPNO=[$0])
>   LogicalFilter(condition=[IS NOT NULL($9)])
>     LogicalCorrelate(correlation=[$cor0], joinType=[left], 
> requiredColumns=[{0}])
>       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>       LogicalAggregate(group=[{}], agg#0=[MIN($0)])
>         LogicalProject($f0=[true])
>           LogicalFilter(condition=[OR(AND(IS NULL($cor0.EMPNO), IS NULL($0)), 
> IS TRUE(=($cor0.EMPNO, $0)))])<---   here is 't1.x is not distinct from 
> t2.x'
>             LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> after decorrelation
> {code:java}
> LogicalProject(EMPNO=[$0])
>   LogicalFilter(condition=[IS NOT NULL($9)])
>     LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], 
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$13])
>       LogicalJoin(condition=[AND(=($0, $11), =($9, $12))], joinType=[left])   
>  <---here is 't1.x=t2.x and (t1.x is null = t2.x is null)'
>         LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], 
> HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[false], 
> EMPNO0=[$0])
>           LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>         LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
>           LogicalProject(EMPNO=[$2], $f9=[$3], $f0=[true])
>             LogicalJoin(condition=[=($2, $0)], joinType=[inner])
>               LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
>               LogicalProject(EMPNO=[$0], $f9=[false])
>                 LogicalTableScan(table=[[CATALOG, SALES, EMP]])  {code}
>  
> {code:java}
>     EMP               | DEPT
> +-+   |  +-+
> | EMPNO  ||  | DEPTNO |
> +-+   |  +-+
> | null |  |  | null   |
>{code}
> expect result
> {code:java}
>   EMPNO
> +---+
> |  null |
> +---+{code}
> actual result
> {code:java}
>   EMPNO
> +---+
> +---+ {code}
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5880) When the association condition of the association subquery is 'is not distinct from', the join condition becomes '=' after decorrelation.

[libopeng (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17753013#comment-17753013
 ] 

libopeng commented on CALCITE-5880:
---

[~shenlang] I doubt this fault caused by this filter condition. I think this 
filter is generated by 'exist'. Of course you can try to fix this issue.
 

> When the association condition of the association subquery is 'is not 
> distinct from', the join condition becomes '=' after decorrelation.
> -
>
> Key: CALCITE-5880
> URL: https://issues.apache.org/jira/browse/CALCITE-5880
> Project: Calcite
>  Issue Type: Bug
>Reporter: libopeng
>Priority: Major
> Attachments: image-2023-08-01-15-20-22-105.png, 
> image-2023-08-02-10-15-00-455.png
>
>
> {code:java}
> select EMPNO 
> from emp
> where EXISTS (select DEPTNO from dept where emp.EMPNO is not DISTINCT from 
> dept.DEPTNO){code}
> before decorrelation
> {code:java}
> LogicalProject(EMPNO=[$0])
>   LogicalFilter(condition=[IS NOT NULL($9)])
>     LogicalCorrelate(correlation=[$cor0], joinType=[left], 
> requiredColumns=[{0}])
>       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>       LogicalAggregate(group=[{}], agg#0=[MIN($0)])
>         LogicalProject($f0=[true])
>           LogicalFilter(condition=[OR(AND(IS NULL($cor0.EMPNO), IS NULL($0)), 
> IS TRUE(=($cor0.EMPNO, $0)))])<---   here is 't1.x is not distinct from 
> t2.x'
>             LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> after decorrelation
> {code:java}
> LogicalProject(EMPNO=[$0])
>   LogicalFilter(condition=[IS NOT NULL($9)])
>     LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], 
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$13])
>       LogicalJoin(condition=[AND(=($0, $11), =($9, $12))], joinType=[left])   
>  <---here is 't1.x=t2.x and (t1.x is null = t2.x is null)'
>         LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], 
> HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[false], 
> EMPNO0=[$0])
>           LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>         LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
>           LogicalProject(EMPNO=[$2], $f9=[$3], $f0=[true])
>             LogicalJoin(condition=[=($2, $0)], joinType=[inner])
>               LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
>               LogicalProject(EMPNO=[$0], $f9=[false])
>                 LogicalTableScan(table=[[CATALOG, SALES, EMP]])  {code}
>  
> {code:java}
>     EMP               | DEPT
> +-+   |  +-+
> | EMPNO  ||  | DEPTNO |
> +-+   |  +-+
> | null |  |  | null   |
>{code}
> expect result
> {code:java}
>   EMPNO
> +---+
> |  null |
> +---+{code}
> actual result
> {code:java}
>   EMPNO
> +---+
> +---+ {code}
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Resolved] (CALCITE-5885) SqlNode#toSqlString() does not honor dialect's supportsCharSet() flag on nested types

[Jiajun Xie (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-5885?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Jiajun Xie resolved CALCITE-5885.
-
Fix Version/s: 1.36.0
   Resolution: Fixed

> SqlNode#toSqlString() does not honor dialect's supportsCharSet() flag on 
> nested types
> -
>
> Key: CALCITE-5885
> URL: https://issues.apache.org/jira/browse/CALCITE-5885
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: Chris Rice
>Assignee: Jiajun Xie
>Priority: Minor
>  Labels: pull-request-available
> Fix For: 1.36.0
>
> Attachments: CharsetTest.java
>
>
> Say we generate a RelNode corresponding to
> {code:java}
> SELECT CAST(c AS VARCHAR) FROM t
> {code}
> and we were to "unparse" that rel node back into the Postgres SQL dialect 
> using
> {code:java}
> var converter = new RelToSqlConverter(PostgresqlSqlDialect.DEFAULT);
> SqlImplementor.Result result = converter.visitRoot(rel);
> SqlNode sqlNode = result.asStatement();
> var sql = sqlNode.toSqlString(PostgresqlSqlDialect.DEFAULT).getSql();
> {code}
> it would generate something like:
> {code:java}
> SELECT CAST("c" AS VARCHAR) AS "c"
> FROM "t"
> {code}
> Note that there is no charset information included in the cast, since the 
> {{PostgresSqlDialect}} specifies that it does not support char sets:
> {code:java}
>   @Override public boolean supportsCharSet() {
> return false;
>   }
> {code}
> Given that, we would expect that unparsing
> {code:java}
> SELECT CAST(c AS VARCHAR ARRAY) FROM t
> {code}
> back into SQL would also generate a type with no charset specified, but it in 
> fact generates
> {code:java}
> SELECT CAST("c" AS VARCHAR CHARACTER SET "ISO-8859-1" ARRAY) AS "c"
> FROM "t"
> {code}
> This seems to come from the way `SqlTypeUtil.convertTypeToSpec()` handles 
> collection types.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5885) SqlNode#toSqlString() does not honor dialect's supportsCharSet() flag on nested types

[Jiajun Xie (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5885?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17753012#comment-17753012
 ] 

Jiajun Xie commented on CALCITE-5885:
-

Fixed in 
[6974126|https://github.com/apache/calcite/commit/697412624ef6baf0bf94554a51a5f7ed54d09ce7],
 thank [~chrisrice]  for your review.

> SqlNode#toSqlString() does not honor dialect's supportsCharSet() flag on 
> nested types
> -
>
> Key: CALCITE-5885
> URL: https://issues.apache.org/jira/browse/CALCITE-5885
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: Chris Rice
>Assignee: Jiajun Xie
>Priority: Minor
>  Labels: pull-request-available
> Attachments: CharsetTest.java
>
>
> Say we generate a RelNode corresponding to
> {code:java}
> SELECT CAST(c AS VARCHAR) FROM t
> {code}
> and we were to "unparse" that rel node back into the Postgres SQL dialect 
> using
> {code:java}
> var converter = new RelToSqlConverter(PostgresqlSqlDialect.DEFAULT);
> SqlImplementor.Result result = converter.visitRoot(rel);
> SqlNode sqlNode = result.asStatement();
> var sql = sqlNode.toSqlString(PostgresqlSqlDialect.DEFAULT).getSql();
> {code}
> it would generate something like:
> {code:java}
> SELECT CAST("c" AS VARCHAR) AS "c"
> FROM "t"
> {code}
> Note that there is no charset information included in the cast, since the 
> {{PostgresSqlDialect}} specifies that it does not support char sets:
> {code:java}
>   @Override public boolean supportsCharSet() {
> return false;
>   }
> {code}
> Given that, we would expect that unparsing
> {code:java}
> SELECT CAST(c AS VARCHAR ARRAY) FROM t
> {code}
> back into SQL would also generate a type with no charset specified, but it in 
> fact generates
> {code:java}
> SELECT CAST("c" AS VARCHAR CHARACTER SET "ISO-8859-1" ARRAY) AS "c"
> FROM "t"
> {code}
> This seems to come from the way `SqlTypeUtil.convertTypeToSpec()` handles 
> collection types.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5906) RelToSqlConverter throw AssertionError when RelNode tree has the Sample RelNode

[LakeShen (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5906?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17753009#comment-17753009
 ] 

LakeShen commented on CALCITE-5906:
---

 In CALCITE-5895, we will change the samplePercentage(float) to 
samplePercentage(bigdecimal),after the CALCITE-5895 merged,this PR could review 
and merged

> RelToSqlConverter throw AssertionError when RelNode tree has the Sample 
> RelNode
> ---
>
> Key: CALCITE-5906
> URL: https://issues.apache.org/jira/browse/CALCITE-5906
> Project: Calcite
>  Issue Type: Bug
>Reporter: LakeShen
>Assignee: LakeShen
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.36.0
>
> Attachments: image-2023-08-08-20-16-27-188.png, 
> image-2023-08-08-20-19-01-269.png
>
>
> Currently in the RelToSqlConverter class, there is no visit method to handle 
> the Sample RelNode.If a SQL plan has Sample RelNode, RelToSqlConverter will 
> throw a AssertionError like this:
> {code:java}
>  java.lang.AssertionError: Need to implement 
> org.apache.calcite.rel.core.Sample
> {code}
> This will cause the Sample RelNode plan to fail to translate to the 
> corresponding dialect.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Created] (CALCITE-5917) Comments in TimeString class are incorrect

[Mihai Budiu (Jira)]

Mihai Budiu created CALCITE-5917:


 Summary: Comments in TimeString class are incorrect
 Key: CALCITE-5917
 URL: https://issues.apache.org/jira/browse/CALCITE-5917
 Project: Calcite
  Issue Type: Bug
  Components: core
Affects Versions: 1.35.0
Reporter: Mihai Budiu


Here is an example of an incorrect comment:

{code:java}
  /** Sets the fraction field of a {@code TimeString} to a given number
   * of milliseconds. Nukes the value set via {@link #withNanos}.
   *
   * For example,
   * {@code new TimeString(1970, 1, 1, 2, 3, 4).withMillis(56)}
   * yields {@code TIME '1970-01-01 02:03:04.056'}. */
  public TimeString withMillis(int millis) {
{code}

There are several such comments.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5895) TABLESAMPLE(0) should return empty result

[Julian Hyde (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5895?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752906#comment-17752906
 ] 

Julian Hyde commented on CALCITE-5895:
--

I have reviewed again. As I said in the PR, can you change the 
{{samplePercentage}} field to {{{}BigDecimal{}}}; it doesn't make sense to go 
to {{float}} and back. When that's done we can merge.

I have logged CALCITE-5916 to make {{RelBuilder}} to more of the work, but that 
can be a follow-up commit.

> TABLESAMPLE(0) should return empty result
> -
>
> Key: CALCITE-5895
> URL: https://issues.apache.org/jira/browse/CALCITE-5895
> Project: Calcite
>  Issue Type: Bug
>Reporter: LakeShen
>Assignee: LakeShen
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.36.0
>
> Attachments: image-2023-08-05-22-45-02-513.png
>
>
> For The sql:
> {code:java}
> select * from emp tablesample bernoulli(0) where empno > 5
> {code}
> The plan is :
> {code:java}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], 
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
>   LogicalFilter(condition=[>($0, 5)])
>     LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> I think the correct result of above SQL is empty.
> But its result is the following SQL result:
> {code:java}
> select * from emp where empno > 5
> {code}
> It's an incorrect result



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-5912) Add an implementation of TABLESAMPLE in Enumerable convention

[Julian Hyde (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-5912?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Julian Hyde updated CALCITE-5912:
-
Summary: Add an implementation of TABLESAMPLE in Enumerable convention  
(was: Add an implementation of TABLESAMPLE in enumerable mode.)

> Add an implementation of TABLESAMPLE in Enumerable convention
> -
>
> Key: CALCITE-5912
> URL: https://issues.apache.org/jira/browse/CALCITE-5912
> Project: Calcite
>  Issue Type: New Feature
>Reporter: LakeShen
>Priority: Major
>
> In Calcite, the TableSample SQL statement will be converted to Sample RelNode 
> plan tree,for example:
> {code:java}
> select * from emp tablesample bernoulli(10){code}
> The plan is :
> {code:java}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], 
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
>   Sample(mode=[bernoulli], rate=[10.0], repeatableSeed=[-])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code}
> Currently in enumerable mode, we don't have EnumerableSample, So we can't add 
> TableSample quidem tests (such as in misc.iq).
> After this jira,we could add the quidem test for TableSample(such as 
> tablesample.iq),although TableSample's results are inconclusive when the 0 < 
> sample rate < 100, but TampeSample(0) is certain result.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-5895) TABLESAMPLE(0) should return empty result

[Julian Hyde (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-5895?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Julian Hyde updated CALCITE-5895:
-
Summary: TABLESAMPLE(0) should return empty result  (was: The result of 
table sample rate 0 should return the empty result)

> TABLESAMPLE(0) should return empty result
> -
>
> Key: CALCITE-5895
> URL: https://issues.apache.org/jira/browse/CALCITE-5895
> Project: Calcite
>  Issue Type: Bug
>Reporter: LakeShen
>Assignee: LakeShen
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.36.0
>
> Attachments: image-2023-08-05-22-45-02-513.png
>
>
> For The sql:
> {code:java}
> select * from emp tablesample bernoulli(0) where empno > 5
> {code}
> The plan is :
> {code:java}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], 
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
>   LogicalFilter(condition=[>($0, 5)])
>     LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> I think the correct result of above SQL is empty.
> But its result is the following SQL result:
> {code:java}
> select * from emp where empno > 5
> {code}
> It's an incorrect result



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Created] (CALCITE-5916) In RelBuilder, add sample() method (equivalent to SQL TABLESAMPLE clause)

[Julian Hyde (Jira)]

Julian Hyde created CALCITE-5916:


 Summary: In RelBuilder, add sample() method (equivalent to SQL 
TABLESAMPLE clause)
 Key: CALCITE-5916
 URL: https://issues.apache.org/jira/browse/CALCITE-5916
 Project: Calcite
  Issue Type: Bug
Reporter: Julian Hyde


In RelBuilder, add sample() method (equivalent to SQL TABLESAMPLE clause).

In {{SqlToRelConverter}}, the code
{code}
if (tableSampleSpec.getSamplePercentage() == 0f) {
  bb.setRoot(relBuilder.push(bb.root()).empty().build(), true);
} else {
  RelOptSamplingParameters params =
  new RelOptSamplingParameters(
  tableSampleSpec.isBernoulli(),
  tableSampleSpec.getSamplePercentage(),
  tableSampleSpec.isRepeatable(),
  tableSampleSpec.getRepeatableSeed());
  bb.setRoot(new Sample(cluster, bb.root(), params), false);
}
{code}
would become
{code}
relBuilder.push(bb.root());
relBuilder.sample(tableSampleSpec.isBernoulli(),
tableSampleSpec.getSamplePercentage(),
tableSampleSpec.isRepeatable(),
tableSampleSpec.getRepeatableSeed());
bb.setRoot(relBuilder.build(), false);
{code}
Note that {{RelBuilder.sample}} calls {{empty()}} if the rate = 0. It should 
also skip sampling if rate = 1.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-5910) Add REGEXP_EXTRACT and REGEXP_SUBSTR functions (enabled in BigQuery library)

[Julian Hyde (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5910?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752889#comment-17752889
 ] 

Julian Hyde edited comment on CALCITE-5910 at 8/10/23 5:39 PM:
---

We need to make a decision whether or not to use re2, and whether to use it 
only for BigQuery-specific functions or for all functions.

In my opinion, we should continue to use java regexp for all functions, for 
now. The differences in semantics (for correct regular expressions) are small 
or zero. This is the best path because it is the quickest and simplest.

If there is a compelling reason to move to re2 (either for semantics, or for 
re2's better defense against denial-of-service expressions) then we can 
revisit, by logging a Jira case. The change might cover all functions, or just 
BigQuery functions, or be enabled using some kind of configuration property. 
This might mean changing the semantics of existing functions but, as I 
mentioned above, we believe that those differences would be small.


was (Author: julianhyde):
We need to make a decision whether or not to use re2, and whether to use it 
only for BigQuery-specific functions or for all functions.

In my opinion, we should continue to use java regexp for all functions, for 
now. The differences in semantics (for correct regular expressions) are small 
or zero. This is the best path because it is the quickest and simplest.

If there is a compelling reason to move to re2 (either for semantics, or for 
re2's better defense against denial-of-service expressions) then we can 
revisit. The change might cover all functions, or just BigQuery functions, or 
be enabled using some kind of configuration property. This might mean changing 
the semantics of existing functions but, as I mentioned above, we believe that 
those differences would be small.

> Add REGEXP_EXTRACT and REGEXP_SUBSTR functions (enabled in BigQuery library)
> 
>
> Key: CALCITE-5910
> URL: https://issues.apache.org/jira/browse/CALCITE-5910
> Project: Calcite
>  Issue Type: Task
>Reporter: Jerin John
>Assignee: Jerin John
>Priority: Major
>  Labels: pull-request-available
>
> Add support for 
> [REGEXP_EXTRACT|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_extract]
>  and 
> [REGEXP_SUBSTR|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_substr]
>  functions from BigQuery.
> *{{REGEXP_EXTRACT(value, regexp[, position[, occurrence]])}}*
> Returns the substring in {{value}} that matches the regular expression 
> {{{}regexp{}}}. Returns {{NULL}} if there is no match.
>  * If the regular expression contains a capturing group ({{{}(...){}}}), and 
> there is a match for that capturing group, that match is returned. If there 
> are multiple matches for a capturing group, the last match is returned.
>  * If {{position}} is specified, the search starts at this position in 
> {{{}value{}}}, otherwise it starts at the beginning of {{{}value{}}}.
>  * If {{occurrence}} is specified, the search returns a specific occurrence 
> of the {{regexp}} in {{{}value{}}}, otherwise returns the first match.
>  
> *{{REGEXP_SUBSTR(value, regexp[, position[, occurrence]])}}*
> Synonym for REGEXP_EXTRACT



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5910) Add REGEXP_EXTRACT and REGEXP_SUBSTR functions (enabled in BigQuery library)

[Julian Hyde (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5910?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752889#comment-17752889
 ] 

Julian Hyde commented on CALCITE-5910:
--

We need to make a decision whether or not to use re2, and whether to use it 
only for BigQuery-specific functions or for all functions.

In my opinion, we should continue to use java regexp for all functions, for 
now. The differences in semantics (for correct regular expressions) are small 
or zero. This is the best path because it is the quickest and simplest.

If there is a compelling reason to move to re2 (either for semantics, or for 
re2's better defense against denial-of-service expressions) then we can 
revisit. The change might cover all functions, or just BigQuery functions, or 
be enabled using some kind of configuration property. This might mean changing 
the semantics of existing functions but, as I mentioned above, we believe that 
those differences would be small.

> Add REGEXP_EXTRACT and REGEXP_SUBSTR functions (enabled in BigQuery library)
> 
>
> Key: CALCITE-5910
> URL: https://issues.apache.org/jira/browse/CALCITE-5910
> Project: Calcite
>  Issue Type: Task
>Reporter: Jerin John
>Assignee: Jerin John
>Priority: Major
>  Labels: pull-request-available
>
> Add support for 
> [REGEXP_EXTRACT|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_extract]
>  and 
> [REGEXP_SUBSTR|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_substr]
>  functions from BigQuery.
> *{{REGEXP_EXTRACT(value, regexp[, position[, occurrence]])}}*
> Returns the substring in {{value}} that matches the regular expression 
> {{{}regexp{}}}. Returns {{NULL}} if there is no match.
>  * If the regular expression contains a capturing group ({{{}(...){}}}), and 
> there is a match for that capturing group, that match is returned. If there 
> are multiple matches for a capturing group, the last match is returned.
>  * If {{position}} is specified, the search starts at this position in 
> {{{}value{}}}, otherwise it starts at the beginning of {{{}value{}}}.
>  * If {{occurrence}} is specified, the search returns a specific occurrence 
> of the {{regexp}} in {{{}value{}}}, otherwise returns the first match.
>  
> *{{REGEXP_SUBSTR(value, regexp[, position[, occurrence]])}}*
> Synonym for REGEXP_EXTRACT



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5858) Add REGEXP_CONTAINS, REGEXP_EXTRACT, REGEXP_EXTRACT_ALL, REGEXP_INSTR, REGEXP_REPLACE and REGEXP_SUBSTR functions (enabled in BigQuery library)

[Julian Hyde (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5858?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752888#comment-17752888
 ] 

Julian Hyde commented on CALCITE-5858:
--

Depending on how the discussion of re2 vs java regexp turns out, 
{{REGEXP_REPLACE}} will either be a new function (with BigQuery-specific 
semantics) or the addition of the existing function to the BigQuery library. If 
it's a new function the javadoc in {{SqlLibraryOperators}} should make clear 
what are the differences in semantics.

> Add REGEXP_CONTAINS, REGEXP_EXTRACT, REGEXP_EXTRACT_ALL, REGEXP_INSTR, 
> REGEXP_REPLACE and REGEXP_SUBSTR functions (enabled in BigQuery library)
> ---
>
> Key: CALCITE-5858
> URL: https://issues.apache.org/jira/browse/CALCITE-5858
> Project: Calcite
>  Issue Type: New Feature
>Reporter: Jerin John
>Assignee: Jerin John
>Priority: Major
>
> Add support for REGEX_* string functions in BigQuery.
> Function descriptions:
>  * REGEXP_CONTAINS: Returns TRUE if input value is a partial match for the 
> regular expression.
>  * REGEXP_EXTRACT: Returns the substring in input value that matches the 
> regular expression. Returns NULL if there is no match.
>  * REGEXP_EXTRACT_ALL: Returns an array of all substrings of input value that 
> match the regular expression. Returns an empty array if there is no match.
>  * REGEXP_INSTR: Returns the lowest 1-based position of a regular expression 
> in an input value.
>  * REGEXP_REPLACE: Returns a STRING where all substrings of input value that 
> match regular expression are replaced with the input replacement.
>  * REGEXP_SUBSTR: Synonym for REGEXP_EXTRACT, returns the substring in input 
> value that matches the regular expression. Returns NULL if there is no match.
> More on these functions and their syntax may be found here: [BigQuery 
> Doc|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_contains]



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-5914) Cache compiled regular expressions in SQL function runtime

[Julian Hyde (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-5914?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Julian Hyde updated CALCITE-5914:
-
Description: 
Cache compiled regular expressions (and other amortized work) in SQL function 
runtime. Compiling a regular expression to a pattern is expensive (compared to 
the cost of matching, given an existing pattern) and therefore caching the 
compiled form will yield performance benefits if the regular expression is 
constant or has a small number of values.

Consider the following query:
{code:java}
SELECT ename, job, RLIKE(ename, 'A.*'), RLIKE(ename, job || '.*')
FROM emp
{code}
The first regular expression, '{{A.\*}}', is constant and can be compiled at 
prepare time or at the start of execution; the second regular expression, {{job 
|| '.\*'}}, might vary from one row to the next. However if the {{job}} column 
has a small number of values it still might be beneficial to cache the compiled 
regular expression.

If {{SqlFunctions.rlike}} could use a cache (mapping from {{String}} to 
{{{}java.util.regex.Pattern{}}}) then it would achieve benefits in both the 
constant and non-constant cases.

The cache needs to:
 * be thread-safe (in case queries are executing using multiple threads),
 * return thread-safe objects (as is {{{}Pattern{}}}),
 * have bounded space (so that a query doesn't blow memory with 1 million 
distinct regular expressions),
 * disposed after the query has terminated,
 * (ideally) share with regexes of the same language in the same query,
 * not conflict with regexes of different languages in the same query.

One possible implementation is to add an {{interface FunctionState}}, with 
subclasses including {{class RegexpCache}}, and if argument 1 of a function is 
a subclass of {{FunctionState}} the compiler would initialize the state in the 
generated code. The function can rely on the state argument being initialized, 
and being the same object from one call to the next. Example:
{code:java}
interface FunctionState {
}

class RegexpCache implements FunctionState {
  final Cache cache = ...;
}
{code}
This change should install the cache for all applicable functions, including 
LIKE, ILIKE, RLIKE, SIMILAR, posix regex, REGEXP_REPLACE (MySQL, Oracle), 
REGEXP_CONTAINS (BigQuery), other BigQuery REGEXP_ functions, PARSE_URL, 
JSON_REPLACE, PARSE_TIMESTAMP.

  was:
Cache compiled regular expressions (and other amortized work) in SQL function 
runtime. Compiling a regular expression to a pattern is expensive (compared to 
the cost of matching, given an existing pattern) and therefore caching the 
compiled form will yield performance benefits if the regular expression is 
constant or has a small number of values.

Consider the following query:
{code:java}
SELECT ename, job, RLIKE(ename, 'A.*'), RLIKE(ename, job || '.*')
FROM emp
{code}
The first regular expression, '{{{}A.{*}{*}{}}}{*}', is constant and can be 
compiled at prepare time or at the start of execution; the second regular 
expression '{{{}job || '.{}}}{*}{{{}'{}}}' might vary from one row to the next. 
However if the {{job}} column has a small number of values it still might be 
beneficial to cache the compiled regular expression.

If {{SqlFunctions.rlike}} could use a cache (mapping from {{String}} to 
{{{}java.util.regex.Pattern{}}}) then it would achieve benefits in both the 
constant and non-constant cases.

The cache needs to:
 * be thread-safe (in case queries are executing using multiple threads),
 * return thread-safe objects (as is {{{}Pattern{}}}),
 * have bounded space (so that a query doesn't blow memory with 1 million 
distinct regular expressions),
 * disposed after the query has terminated,
 * (ideally) share with regexes of the same language in the same query,
 * not conflict with regexes of different languages in the same query.

One possibility is to add an {{{}interface FunctionState{}}}, with subclasses 
including {{{}class RegexpCache{}}}, and if argument 1 of a function is a 
subclass of {{FunctionState}} the compiler would initialize the state in the 
generated code. The function can rely on the state argument being initialized, 
and being the same object from one call to the next. Example:
{code:java}
interface FunctionState {
}

class RegexpCache implements FunctionState {
  final Cache cache = ...;
}
{code}
This change should install the cache for all applicable functions, including 
LIKE, ILIKE, RLIKE, SIMILAR, posix regex, REGEXP_REPLACE (MySQL, Oracle), 
REGEXP_CONTAINS (BigQuery), other BigQuery REGEXP_ functions, PARSE_URL, 
JSON_REPLACE, PARSE_TIMESTAMP.


> Cache compiled regular expressions in SQL function runtime
> --
>
> Key: CALCITE-5914
> URL: https://issues.apache.org/jira/browse/CALCITE-5914
> Project: Calcite
>  Issue Type: Bug
>Reporter: Julian Hyde
>

[jira] [Updated] (CALCITE-5914) Cache compiled regular expressions in SQL function runtime

[Julian Hyde (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-5914?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Julian Hyde updated CALCITE-5914:
-
Description: 
Cache compiled regular expressions (and other amortized work) in SQL function 
runtime. Compiling a regular expression to a pattern is expensive (compared to 
the cost of matching, given an existing pattern) and therefore caching the 
compiled form will yield performance benefits if the regular expression is 
constant or has a small number of values.

Consider the following query:
{code:java}
SELECT ename, job, RLIKE(ename, 'A.*'), RLIKE(ename, job || '.*')
FROM emp
{code}
The first regular expression, '{{{}A.{*}{*}{}}}{*}', is constant and can be 
compiled at prepare time or at the start of execution; the second regular 
expression '{{{}job || '.{}}}{*}{{{}'{}}}' might vary from one row to the next. 
However if the {{job}} column has a small number of values it still might be 
beneficial to cache the compiled regular expression.

If {{SqlFunctions.rlike}} could use a cache (mapping from {{String}} to 
{{{}java.util.regex.Pattern{}}}) then it would achieve benefits in both the 
constant and non-constant cases.

The cache needs to:
 * be thread-safe (in case queries are executing using multiple threads),
 * return thread-safe objects (as is {{{}Pattern{}}}),
 * have bounded space (so that a query doesn't blow memory with 1 million 
distinct regular expressions),
 * disposed after the query has terminated,
 * (ideally) share with regexes of the same language in the same query,
 * not conflict with regexes of different languages in the same query.

One possibility is to add an {{{}interface FunctionState{}}}, with subclasses 
including {{{}class RegexpCache{}}}, and if argument 1 of a function is a 
subclass of {{FunctionState}} the compiler would initialize the state in the 
generated code. The function can rely on the state argument being initialized, 
and being the same object from one call to the next. Example:
{code:java}
interface FunctionState {
}

class RegexpCache implements FunctionState {
  final Cache cache = ...;
}
{code}
This change should install the cache for all applicable functions, including 
LIKE, ILIKE, RLIKE, SIMILAR, posix regex, REGEXP_REPLACE (MySQL, Oracle), 
REGEXP_CONTAINS (BigQuery), other BigQuery REGEXP_ functions, PARSE_URL, 
JSON_REPLACE, PARSE_TIMESTAMP.

  was:
Cache compiled regular expressions (and other amortized work) in SQL function 
runtime.

Consider the following query:
{code:java}
SELECT ename, job, RLIKE(ename, 'A.*'), RLIKE(ename, job || '.*')
FROM emp
{code}
The first regular expression, '{{{}A.*{}}}', is constant and can be compiled at 
prepare time or at the start of execution; the second regular expression 
'{{{}job || '.*'{}}}' might vary from one row to the next. However if the 
{{job}} column has a small number of values it still might be beneficial to 
cache the compiled regular expression.

If {{SqlFunctions.rlike}} could use a cache (mapping from {{String}} to 
{{{}java.util.regex.Pattern{}}}) then it would achieve benefits in both the 
constant and non-constant cases.

The cache needs to:
 * be thread-safe (in case queries are executing using multiple threads),
 * return thread-safe objects (as is {{{}Pattern{}}}),
 * have bounded space (so that a query doesn't blow memory with 1 million 
distinct regular expressions),
 * disposed after the query has terminated,
 * (ideally) share with regexes of the same language in the same query,
 * not conflict with regexes of different languages in the same query.

One possibility is to add an {{{}interface FunctionState{}}}, with subclasses 
including {{{}class RegexpCache{}}}, and if argument 1 of a function is a 
subclass of {{FunctionState}} the compiler would initialize the state in the 
generated code. The function can rely on the state argument being initialized, 
and being the same object from one call to the next. Example:
{code:java}
interface FunctionState {
}

class RegexpCache implements FunctionState {
  final Cache cache = ...;
}
{code}
This change should install the cache for all applicable functions, including 
LIKE, ILIKE, RLIKE, SIMILAR, posix regex, REGEXP_REPLACE (MySQL, Oracle), 
REGEXP_CONTAINS (BigQuery), other BigQuery REGEXP_ functions, PARSE_URL, 
JSON_REPLACE, PARSE_TIMESTAMP.


> Cache compiled regular expressions in SQL function runtime
> --
>
> Key: CALCITE-5914
> URL: https://issues.apache.org/jira/browse/CALCITE-5914
> Project: Calcite
>  Issue Type: Bug
>Reporter: Julian Hyde
>Priority: Major
>
> Cache compiled regular expressions (and other amortized work) in SQL function 
> runtime. Compiling a regular expression to a pattern is expensive (compared 
> to the cost of matching, given an existing pattern) and therefore caching the 
> 

[jira] [Updated] (CALCITE-5915) Missing SQL hints in not-expanded subqueries.

[Vladimir Steshin (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-5915?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Vladimir Steshin updated CALCITE-5915:
--
Description: 
Not sure if it is a bug becuase the query plans in this case might not be final 
and require further expanding, but the SQL hints might not be propagated to 
subqueries when SqlToRelConverter#withExpand==false. This happens because hints 
are pushed down with _RelOptUtil#RelHintPropagateShuttle_ which travese through 
_RelNode#getInputs()_. But what if node is not accessible as other node’s input 
like keeping in _LogicalFilter#condition_? Then the shuttle skips such nodes.

Test attached.


  was:
Not sure if it is a bug becuase the query plans in this case might not be final 
and require further expanding, but the SQL hints might not be propagated to 
subqueries when SqlToRelConverter#withExpand==false. This happens because hints 
are pushed down with _RelOptUtil#RelHintPropagateShuttle_ which travese through 
_RelNode#getInputs()_. But what if node is not accessible as other node’s input 
like keeping in _LogicalFilter#condition_? Then the shuttle skips such nodes. I 
found tah may happen in qubqueries. 

Test attached.



> Missing SQL hints in not-expanded subqueries. 
> --
>
> Key: CALCITE-5915
> URL: https://issues.apache.org/jira/browse/CALCITE-5915
> Project: Calcite
>  Issue Type: Bug
>Reporter: Vladimir Steshin
>Priority: Minor
> Attachments: SqlHintsInSubqueriesWithDisabledExpanding.java
>
>
> Not sure if it is a bug becuase the query plans in this case might not be 
> final and require further expanding, but the SQL hints might not be 
> propagated to subqueries when SqlToRelConverter#withExpand==false. This 
> happens because hints are pushed down with 
> _RelOptUtil#RelHintPropagateShuttle_ which travese through 
> _RelNode#getInputs()_. But what if node is not accessible as other node’s 
> input like keeping in _LogicalFilter#condition_? Then the shuttle skips such 
> nodes.
> Test attached.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-5915) Missing SQL hints in not-expanded subqueries.

[Vladimir Steshin (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-5915?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Vladimir Steshin updated CALCITE-5915:
--
Description: 
Not sure if it is a bug becuase the query plans in this case might not be final 
and require further expanding, but the SQL hints might not be propagated to 
subqueries when SqlToRelConverter#withExpand==false. This happens because hints 
are pushed down with _RelOptUtil#RelHintPropagateShuttle_ which travese through 
_RelNode#getInputs()_. But what if node is not accessible as other node’s input 
like keeping in _LogicalFilter#condition_? Then the shuttle skips such nodes. I 
found tah may happen in qubqueries. 

Test attached.


  was:
Not sure if it is a bug becuase the query plans in this case might not be final 
and require further expanding, but the SQL hints might not be propagated to 
subqueries when SqlToRelConverter#withExpand==false. This happens because hints 
are pushed down with _RelOptUtil#RelHintPropagateShuttle_ which travese through 
_RelNode#getInputs()_. But what if node is not accessible as other node’s input 
like _LogicalFilter#condition_? Then the shuttle skips such nodes. I found tah 
may happen in qubqueries. 

Test attached.



> Missing SQL hints in not-expanded subqueries. 
> --
>
> Key: CALCITE-5915
> URL: https://issues.apache.org/jira/browse/CALCITE-5915
> Project: Calcite
>  Issue Type: Bug
>Reporter: Vladimir Steshin
>Priority: Minor
> Attachments: SqlHintsInSubqueriesWithDisabledExpanding.java
>
>
> Not sure if it is a bug becuase the query plans in this case might not be 
> final and require further expanding, but the SQL hints might not be 
> propagated to subqueries when SqlToRelConverter#withExpand==false. This 
> happens because hints are pushed down with 
> _RelOptUtil#RelHintPropagateShuttle_ which travese through 
> _RelNode#getInputs()_. But what if node is not accessible as other node’s 
> input like keeping in _LogicalFilter#condition_? Then the shuttle skips such 
> nodes. I found tah may happen in qubqueries. 
> Test attached.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-5915) Missing SQL hints in not-expanded subqueries.

[Vladimir Steshin (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-5915?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Vladimir Steshin updated CALCITE-5915:
--
Description: 
Not sure if it is a bug becuase the query plans in this case might not be final 
and require further expanding, but the SQL hints might not be propagated to 
subqueries when SqlToRelConverter#withExpand==false. This happens because hints 
are pushed down with _RelOptUtil#RelHintPropagateShuttle_ which travese through 
_RelNode#getInputs()_. But what if node is not accessible as other node’s input 
like _LogicalFilter#condition_? Then the shuttle skips such nodes. I found tah 
may happen in qubqueries. 

Test attached.


  was:
The SQL hints might not be propagated in decorrelated subqueries when 
SqlToRelConverter#withExpand==false. This happens because hints are pushed down 
with _RelOptUtil#RelHintPropagateShuttle_ which travese through 
_RelNode#getInputs()_. But what if node is not accessible as other node’s input 
like _LogicalFilter#condition_? Then the shuttle skips such nodes. I found tah 
may happen in qubqueries. 

Test attached.



> Missing SQL hints in not-expanded subqueries. 
> --
>
> Key: CALCITE-5915
> URL: https://issues.apache.org/jira/browse/CALCITE-5915
> Project: Calcite
>  Issue Type: Bug
>Reporter: Vladimir Steshin
>Priority: Minor
> Attachments: SqlHintsInSubqueriesWithDisabledExpanding.java
>
>
> Not sure if it is a bug becuase the query plans in this case might not be 
> final and require further expanding, but the SQL hints might not be 
> propagated to subqueries when SqlToRelConverter#withExpand==false. This 
> happens because hints are pushed down with 
> _RelOptUtil#RelHintPropagateShuttle_ which travese through 
> _RelNode#getInputs()_. But what if node is not accessible as other node’s 
> input like _LogicalFilter#condition_? Then the shuttle skips such nodes. I 
> found tah may happen in qubqueries. 
> Test attached.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-5909) Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE but passes when run from the command line

[Ran Tao (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752849#comment-17752849
 ] 

Ran Tao edited comment on CALCITE-5909 at 8/10/23 5:05 PM:
---

[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but calcite-5564 
introduce PERCENTILE_CONT/PERCENTILE_DISC，to let it be NonReservedKeywords, 
however PERCENTILE_CONT/PERCENTILE_DISC is reserved keywords from sql-2008. it 
will cause PERCENTILE_CONT/PERCENTILE_DISC case failed in this PR.I will fix it 
ASAP.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I hope get some feedbacks or contexts). 
Or maybe we can customize the test directory, it's a simple way.

In terms of priority, I think PERCENTILE_CONT, PERCENTILE_DISC keywords need to 
be solved first (to reset it to ReservedKeyWords, if i'm wrong, pls correct me, 
thanks).

[~julianhyde] [~shenlang] hi, If you have time, pls help to check it. 
 


was (Author: lemonjing):
[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but 
[calcite-5564|https://issues.apache.org/jira/browse/CALCITE-5564] introduce 
PERCENTILE_CONT/PERCENTILE_DISC，to let it be NonReservedKeywords, however 
PERCENTILE_CONT/PERCENTILE_DISC is reserved keywords from sql-2008. it blocked 
my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference 

[jira] [Comment Edited] (CALCITE-5909) Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE but passes when run from the command line

[Ran Tao (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752849#comment-17752849
 ] 

Ran Tao edited comment on CALCITE-5909 at 8/10/23 4:47 PM:
---

[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but 
[calcite-5564|https://issues.apache.org/jira/browse/CALCITE-5564] introduce 
PERCENTILE_CONT/PERCENTILE_DISC，to let it be NonReservedKeywords, however 
PERCENTILE_CONT/PERCENTILE_DISC is reserved keywords from sql-2008. it blocked 
my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I hope get some feedbacks or contexts). 
Or maybe we can customize the test directory, it's a simple way.

In terms of priority, I think PERCENTILE_CONT, PERCENTILE_DISC keywords need to 
be solved first (or let it be NonReservedKeywords?).

[~julianhyde] If you have time, pls help to check it.
 


was (Author: lemonjing):
[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but calcite-5564 
introduce PERCENTILE_CONT/PERCENTILE_DISC，to let it be NonReservedKeywords, 
however PERCENTILE_CONT/PERCENTILE_DISC is reserved keywords from sql-2008. it 
blocked my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like 

[jira] [Comment Edited] (CALCITE-5909) Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE but passes when run from the command line

[Ran Tao (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752849#comment-17752849
 ] 

Ran Tao edited comment on CALCITE-5909 at 8/10/23 4:45 PM:
---

[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but calcite-5564 
introduce PERCENTILE_CONT/PERCENTILE_DISC，to let it be NonReservedKeywords, 
however PERCENTILE_CONT/PERCENTILE_DISC is reserved keywords from sql-2008. it 
blocked my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I hope get some feedbacks or contexts). 
Or maybe we can customize the test directory, it's a simple way.

In terms of priority, I think PERCENTILE_CONT, PERCENTILE_DISC keywords need to 
be solved first (or let it be NonReservedKeywords?).

[~julianhyde] If you have time, pls help to check it.
 


was (Author: lemonjing):
[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but calcite-5564 
introduce PERCENTILE_CONT/PERCENTILE_DISC，to let it be NonReservedKeywords, 
however PERCENTILE_CONT/PERCENTILE_DISC is reserved keywords from sql-2008. it 
blocked my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I hope get some feedbacks or 

[jira] [Assigned] (CALCITE-5909) Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE but passes when run from the command line

[Ran Tao (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-5909?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Ran Tao reassigned CALCITE-5909:


Assignee: (was: Ran Tao)

> Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE 
> but passes when run from the command line
> --
>
> Key: CALCITE-5909
> URL: https://issues.apache.org/jira/browse/CALCITE-5909
> Project: Calcite
>  Issue Type: Bug
>  Components: tests
>Reporter: LakeShen
>Priority: Minor
>  Labels: pull-request-available
> Attachments: image-2023-08-08-23-32-55-466.png
>
>
> When I run the SqlParserTest,the testNoUnintendedNewReservedKeywords method 
> failed,the exception like this:
> {code:java}
> java.lang.AssertionError: The parser has at least one new reserved keyword. 
> Are you sure it should be reserved? Difference: {code}
> The picture like this:
> !image-2023-08-08-23-32-55-466.png|width=1543,height=496!
> I could fix this problem.More importantly, why is this method failing, but 
> the Calcite pipeline is passing? I think we should look at something we missed



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-5909) Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE but passes when run from the command line

[Ran Tao (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752849#comment-17752849
 ] 

Ran Tao edited comment on CALCITE-5909 at 8/10/23 4:12 PM:
---

[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but calcite-5564 
introduce PERCENTILE_CONT/PERCENTILE_DISC，to let it be NonReservedKeywords, 
however PERCENTILE_CONT/PERCENTILE_DISC is reserved keywords from sql-2008. it 
blocked my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I hope get some feedbacks or contexts).

In terms of priority, I think PERCENTILE_CONT, PERCENTILE_DISC keywords need to 
be solved first (or let it be NonReservedKeywords?).

[~julianhyde] If you have time, pls help to check it. [~shenlang] thanks for 
reporting this issue.
 


was (Author: lemonjing):
[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but 
[calcite-5564|https://issues.apache.org/jira/browse/CALCITE-5564] introduce 
PERCENTILE_CONT/PERCENTILE_DISC，to let it be NonReservedKeywords, however 
PERCENTILE_CONT/PERCENTILE_DISC is reserved keywords from sql-2008. it blocked 
my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I 

[jira] [Comment Edited] (CALCITE-5909) Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE but passes when run from the command line

[Ran Tao (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752849#comment-17752849
 ] 

Ran Tao edited comment on CALCITE-5909 at 8/10/23 3:59 PM:
---

[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but 
[calcite-5564|https://issues.apache.org/jira/browse/CALCITE-5564] introduce 
PERCENTILE_CONT/PERCENTILE_DISC，to let it be NonReservedKeywords, however 
PERCENTILE_CONT/PERCENTILE_DISC is reserved keywords from sql-2008. it blocked 
my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I hope get some feedbacks or contexts).

In terms of priority, I think PERCENTILE_CONT, PERCENTILE_DISC keywords need to 
be solved first.

[~julianhyde] If you have time, pls help to check it. [~shenlang] thanks for 
reporting this issue.
 


was (Author: lemonjing):
[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but calcite-5564 
introduce PERCENTILE_CONT/PERCENTILE_DISC，to let it be NonReservedKeywords, 
however PERCENTILE_CONT/PERCENTILE_DISC is reserved keywords from sql-2008. it 
blocked my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I hope get some feedbacks or contexts).

[jira] [Assigned] (CALCITE-5909) Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE but passes when run from the command line

[Ran Tao (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-5909?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Ran Tao reassigned CALCITE-5909:


Assignee: Ran Tao

> Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE 
> but passes when run from the command line
> --
>
> Key: CALCITE-5909
> URL: https://issues.apache.org/jira/browse/CALCITE-5909
> Project: Calcite
>  Issue Type: Bug
>  Components: tests
>Reporter: LakeShen
>Assignee: Ran Tao
>Priority: Minor
>  Labels: pull-request-available
> Attachments: image-2023-08-08-23-32-55-466.png
>
>
> When I run the SqlParserTest,the testNoUnintendedNewReservedKeywords method 
> failed,the exception like this:
> {code:java}
> java.lang.AssertionError: The parser has at least one new reserved keyword. 
> Are you sure it should be reserved? Difference: {code}
> The picture like this:
> !image-2023-08-08-23-32-55-466.png|width=1543,height=496!
> I could fix this problem.More importantly, why is this method failing, but 
> the Calcite pipeline is passing? I think we should look at something we missed



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-5909) Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE but passes when run from the command line

[Ran Tao (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752849#comment-17752849
 ] 

Ran Tao edited comment on CALCITE-5909 at 8/10/23 3:57 PM:
---

[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but calcite-5564 
introduce PERCENTILE_CONT/PERCENTILE_DISC，to let it be NonReservedKeywords, 
however PERCENTILE_CONT/PERCENTILE_DISC is reserved keywords from sql-2008. it 
blocked my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I hope get some feedbacks or contexts).

In terms of priority, I think PERCENTILE_CONT, PERCENTILE_DISC keywords need to 
be solved first.

[~julianhyde] If you have time, pls help to check it. [~shenlang] thanks for 
reporting this issue.
 


was (Author: lemonjing):
[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but 
[calcite-5564|https://issues.apache.org/jira/browse/CALCITE-5564] introduce 
PERCENTILE_CONT/PERCENTILE_DISC，to let it be NonReservedKeywords, however 
PERCENTILE_CONT/PERCENTILE_DISC is reserved keywords from sql-2008. it blocked 
my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I hope get some feedbacks or contexts).

[jira] [Comment Edited] (CALCITE-5909) Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE but passes when run from the command line

[Ran Tao (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752849#comment-17752849
 ] 

Ran Tao edited comment on CALCITE-5909 at 8/10/23 3:55 PM:
---

[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but 
[calcite-5564|https://issues.apache.org/jira/browse/CALCITE-5564] introduce 
PERCENTILE_CONT/PERCENTILE_DISC，to let it be NonReservedKeywords, however 
PERCENTILE_CONT/PERCENTILE_DISC is reserved keywords from sql-2008. it blocked 
my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I hope get some feedbacks or contexts).

In terms of priority, I think PERCENTILE_CONT, PERCENTILE_DISC keywords need to 
be solved first.

[~julianhyde] If you have time, pls help to check it. [~shenlang] thanks for 
report this issue.
 


was (Author: lemonjing):
[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but  
calcite-5564introduce PERCENTILE_CONT/PERCENTILE_DISC，to let it be 
NonReservedKeywords, however PERCENTILE_CONT/PERCENTILE_DISC is reserved 
keywords from sql-2008. it blocked my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I hope get some feedbacks or contexts).

In 

[jira] [Comment Edited] (CALCITE-5909) Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE but passes when run from the command line

[Ran Tao (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752849#comment-17752849
 ] 

Ran Tao edited comment on CALCITE-5909 at 8/10/23 3:54 PM:
---

[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but  
[calcite-5564|https://issues.apache.org/jira/browse/CALCITE-5564](I have added 
to this issue link) introduce PERCENTILE_CONT/PERCENTILE_DISC，to let it be 
NonReservedKeywords, however PERCENTILE_CONT/PERCENTILE_DISC is reserved 
keywords from sql-2008. it blocked my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I hope get some feedbacks or contexts).

In terms of priority, I think PERCENTILE_CONT, PERCENTILE_DISC keywords need to 
be solved first.

[~julianhyde] If you have time, pls help to check it. [~shenlang] thanks for 
report this issue.
 


was (Author: lemonjing):
[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:



*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)


*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a PR to fix it. but  calcite-5564(I have added 
the link) intrduce PERCENTILE_CONT/PERCENTILE_DISC，to let it be 
NonReservedKeywords, however PERCENTILE_CONT/PERCENTILE_DISC is reserved 
keywords from sql-2008. it blocked my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I hope get some feedbacks or 

[jira] [Comment Edited] (CALCITE-5909) Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE but passes when run from the command line

[Ran Tao (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752849#comment-17752849
 ] 

Ran Tao edited comment on CALCITE-5909 at 8/10/23 3:54 PM:
---

[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but  
calcite-5564introduce PERCENTILE_CONT/PERCENTILE_DISC，to let it be 
NonReservedKeywords, however PERCENTILE_CONT/PERCENTILE_DISC is reserved 
keywords from sql-2008. it blocked my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I hope get some feedbacks or contexts).

In terms of priority, I think PERCENTILE_CONT, PERCENTILE_DISC keywords need to 
be solved first.

[~julianhyde] If you have time, pls help to check it. [~shenlang] thanks for 
report this issue.
 


was (Author: lemonjing):
[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:

*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)

*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a 
[PR|https://github.com/apache/calcite/pull/3361] to fix it. but  
[calcite-5564|https://issues.apache.org/jira/browse/CALCITE-5564](I have added 
to this issue link) introduce PERCENTILE_CONT/PERCENTILE_DISC，to let it be 
NonReservedKeywords, however PERCENTILE_CONT/PERCENTILE_DISC is reserved 
keywords from sql-2008. it blocked my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I hope 

[jira] [Updated] (CALCITE-5909) Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE but passes when run from the command line

[ASF GitHub Bot (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-5909?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ASF GitHub Bot updated CALCITE-5909:

Labels: pull-request-available  (was: )

> Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE 
> but passes when run from the command line
> --
>
> Key: CALCITE-5909
> URL: https://issues.apache.org/jira/browse/CALCITE-5909
> Project: Calcite
>  Issue Type: Bug
>  Components: tests
>Reporter: LakeShen
>Priority: Minor
>  Labels: pull-request-available
> Attachments: image-2023-08-08-23-32-55-466.png
>
>
> When I run the SqlParserTest,the testNoUnintendedNewReservedKeywords method 
> failed,the exception like this:
> {code:java}
> java.lang.AssertionError: The parser has at least one new reserved keyword. 
> Are you sure it should be reserved? Difference: {code}
> The picture like this:
> !image-2023-08-08-23-32-55-466.png|width=1543,height=496!
> I could fix this problem.More importantly, why is this method failing, but 
> the Calcite pipeline is passing? I think we should look at something we missed



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5909) Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE but passes when run from the command line

[Ran Tao (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752849#comment-17752849
 ] 

Ran Tao commented on CALCITE-5909:
--

[~thomas.rebele] thanks for suggestions. however, this problem is not the 
reason of ide, caching, multi-thread or data.

I checked this problem carefully and in-depth. In fact, this case is wrong 
indeed. The reason why gradle build is normal is because this SqlParserTest was 
moved into the src/main directory in calcite-1.28, that is, it will not be 
executed as a unit test. But inheriting its subclasses such as BabelParserTest, 
ServerParserTest can execute it indirectly. But in calcite-1.28, for this case, 
it is restricted and cannot be executed in subclasses, that is, it cannot be 
included in the build or test phase in the end. Here are some important changes:



*<= calcite-1.27.0:* SqlParserTest is in `core/test` dir (this case is right 
and can be covered by gradle build process)

*calcite-1.28.0 ~ calcite-1.33.0:* move SqlParserTest to `testkit/main` dir 
(SqlParserTest not in test dir but it can be coverd by BabelParserTest, 
ServerParserTest indirectly. all cases in SqlParserTest run success except 
testNoUnintendedNewReservedKeywords, because this case has 
`not-run-for-subclasses` limitation, so it can't be covered in gradle build 
process, but this case is right.)


*>= calcite-1.34.0:* this case is wrong, because the keywords are changed in 
these released version, but not update the static field of 
SqlParserTest.KEYWORDS. because of the reason above we can't found this failure 
in the build or ci process.

The solution:
1. fix this case. I have opened a PR to fix it. but  calcite-5564(I have added 
the link) intrduce PERCENTILE_CONT/PERCENTILE_DISC，to let it be 
NonReservedKeywords, however PERCENTILE_CONT/PERCENTILE_DISC is reserved 
keywords from sql-2008. it blocked my PR.

2.Only fix this case is not enough, it will be failed if we not cover it in 
calcite build process. I have try 2 ways. first way is re-move SqlParserTest to 
test directory, it's very difficult, because many src/main classes use it. If 
move it to test dir, these classes can not reference it. second way is remove 
the `not-run-for-subclasses` this limitation, it meets some other problems, 
however, it looks like can be solved (I hope get some feedbacks or contexts).

In terms of priority, I think PERCENTILE_CONT, PERCENTILE_DISC keywords need to 
be solved first.

[~julianhyde] If you have time, pls help to check it. [~shenlang] thanks for 
report this issue.
 

> Sometimes SqlParserTest.testNoUnintendedNewReservedKeywords fails in the IDE 
> but passes when run from the command line
> --
>
> Key: CALCITE-5909
> URL: https://issues.apache.org/jira/browse/CALCITE-5909
> Project: Calcite
>  Issue Type: Bug
>  Components: tests
>Reporter: LakeShen
>Priority: Minor
> Attachments: image-2023-08-08-23-32-55-466.png
>
>
> When I run the SqlParserTest,the testNoUnintendedNewReservedKeywords method 
> failed,the exception like this:
> {code:java}
> java.lang.AssertionError: The parser has at least one new reserved keyword. 
> Are you sure it should be reserved? Difference: {code}
> The picture like this:
> !image-2023-08-08-23-32-55-466.png|width=1543,height=496!
> I could fix this problem.More importantly, why is this method failing, but 
> the Calcite pipeline is passing? I think we should look at something we missed



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-5915) Missing SQL hints in not-expanded subqueries.

[Vladimir Steshin (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-5915?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Vladimir Steshin updated CALCITE-5915:
--
Attachment: SqlHintsInSubqueriesWithDisabledExpanding.java

> Missing SQL hints in not-expanded subqueries. 
> --
>
> Key: CALCITE-5915
> URL: https://issues.apache.org/jira/browse/CALCITE-5915
> Project: Calcite
>  Issue Type: Bug
>Reporter: Vladimir Steshin
>Priority: Minor
> Attachments: SqlHintsInSubqueriesWithDisabledExpanding.java
>
>
> The SQL hints might not be propagated in decorrelated subqueries when 
> SqlToRelConverter#withExpand==false. This happens because hints are pushed 
> down with _RelOptUtil#RelHintPropagateShuttle_ which travese through 
> _RelNode#getInputs()_. But what if node is not accessible as other node’s 
> input like _LogicalFilter#condition_? Then the shuttle skips such nodes. I 
> found tah may happen in qubqueries. 
> Test attached.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-5915) Missing SQL hints in not-expanded subqueries.

[Vladimir Steshin (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-5915?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Vladimir Steshin updated CALCITE-5915:
--
Attachment: (was: SqlHintsInSubqueriesWithDisabledExpanding.java)

> Missing SQL hints in not-expanded subqueries. 
> --
>
> Key: CALCITE-5915
> URL: https://issues.apache.org/jira/browse/CALCITE-5915
> Project: Calcite
>  Issue Type: Bug
>Reporter: Vladimir Steshin
>Priority: Minor
> Attachments: SqlHintsInSubqueriesWithDisabledExpanding.java
>
>
> The SQL hints might not be propagated in decorrelated subqueries when 
> SqlToRelConverter#withExpand==false. This happens because hints are pushed 
> down with _RelOptUtil#RelHintPropagateShuttle_ which travese through 
> _RelNode#getInputs()_. But what if node is not accessible as other node’s 
> input like _LogicalFilter#condition_? Then the shuttle skips such nodes. I 
> found tah may happen in qubqueries. 
> Test attached.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Created] (CALCITE-5915) Missing SQL hints in not-expanded subqueries.

[Vladimir Steshin (Jira)]

Vladimir Steshin created CALCITE-5915:
-

 Summary: Missing SQL hints in not-expanded subqueries. 
 Key: CALCITE-5915
 URL: https://issues.apache.org/jira/browse/CALCITE-5915
 Project: Calcite
  Issue Type: Bug
Reporter: Vladimir Steshin
 Attachments: SqlHintsInSubqueriesWithDisabledExpanding.java

The SQL hints might not be propagated in decorrelated subqueries when 
SqlToRelConverter#withExpand==false. This happens because hints are pushed down 
with _RelOptUtil#RelHintPropagateShuttle_ which travese through 
_RelNode#getInputs()_. But what if node is not accessible as other node’s input 
like _LogicalFilter#condition_? Then the shuttle skips such nodes. I found tah 
may happen in qubqueries. 

Test attached.




--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-5915) Missing SQL hints in not-expanded subqueries.

[Vladimir Steshin (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-5915?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Vladimir Steshin updated CALCITE-5915:
--
Attachment: SqlHintsInSubqueriesWithDisabledExpanding.java

> Missing SQL hints in not-expanded subqueries. 
> --
>
> Key: CALCITE-5915
> URL: https://issues.apache.org/jira/browse/CALCITE-5915
> Project: Calcite
>  Issue Type: Bug
>Reporter: Vladimir Steshin
>Priority: Minor
> Attachments: SqlHintsInSubqueriesWithDisabledExpanding.java
>
>
> The SQL hints might not be propagated in decorrelated subqueries when 
> SqlToRelConverter#withExpand==false. This happens because hints are pushed 
> down with _RelOptUtil#RelHintPropagateShuttle_ which travese through 
> _RelNode#getInputs()_. But what if node is not accessible as other node’s 
> input like _LogicalFilter#condition_? Then the shuttle skips such nodes. I 
> found tah may happen in qubqueries. 
> Test attached.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5880) When the association condition of the association subquery is 'is not distinct from', the join condition becomes '=' after decorrelation.

[LakeShen (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752797#comment-17752797
 ] 

LakeShen commented on CALCITE-5880:
---

I use this sql to debug :
{code:java}
select EMPNO
from EMPNULLABLES
where EXISTS (select DEPTNO from  EMPNULLABLES dept where EMPNULLABLES.EMPNO is 
not DISTINCT from dept.DEPTNO) {code}
I find the condition:
{code:java}
 LogicalFilter(condition=[IS NOT NULL($9)]) {code}
The above condition filters out null data.

Hi [~leepb] ,are you going fix this problem?

If not ,maybe I will try to fix this problem.

 

> When the association condition of the association subquery is 'is not 
> distinct from', the join condition becomes '=' after decorrelation.
> -
>
> Key: CALCITE-5880
> URL: https://issues.apache.org/jira/browse/CALCITE-5880
> Project: Calcite
>  Issue Type: Bug
>Reporter: libopeng
>Priority: Major
> Attachments: image-2023-08-01-15-20-22-105.png, 
> image-2023-08-02-10-15-00-455.png
>
>
> {code:java}
> select EMPNO 
> from emp
> where EXISTS (select DEPTNO from dept where emp.EMPNO is not DISTINCT from 
> dept.DEPTNO){code}
> before decorrelation
> {code:java}
> LogicalProject(EMPNO=[$0])
>   LogicalFilter(condition=[IS NOT NULL($9)])
>     LogicalCorrelate(correlation=[$cor0], joinType=[left], 
> requiredColumns=[{0}])
>       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>       LogicalAggregate(group=[{}], agg#0=[MIN($0)])
>         LogicalProject($f0=[true])
>           LogicalFilter(condition=[OR(AND(IS NULL($cor0.EMPNO), IS NULL($0)), 
> IS TRUE(=($cor0.EMPNO, $0)))])<---   here is 't1.x is not distinct from 
> t2.x'
>             LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> after decorrelation
> {code:java}
> LogicalProject(EMPNO=[$0])
>   LogicalFilter(condition=[IS NOT NULL($9)])
>     LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], 
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$13])
>       LogicalJoin(condition=[AND(=($0, $11), =($9, $12))], joinType=[left])   
>  <---here is 't1.x=t2.x and (t1.x is null = t2.x is null)'
>         LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], 
> HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[false], 
> EMPNO0=[$0])
>           LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>         LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
>           LogicalProject(EMPNO=[$2], $f9=[$3], $f0=[true])
>             LogicalJoin(condition=[=($2, $0)], joinType=[inner])
>               LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
>               LogicalProject(EMPNO=[$0], $f9=[false])
>                 LogicalTableScan(table=[[CATALOG, SALES, EMP]])  {code}
>  
> {code:java}
>     EMP               | DEPT
> +-+   |  +-+
> | EMPNO  ||  | DEPTNO |
> +-+   |  +-+
> | null |  |  | null   |
>{code}
> expect result
> {code:java}
>   EMPNO
> +---+
> |  null |
> +---+{code}
> actual result
> {code:java}
>   EMPNO
> +---+
> +---+ {code}
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5743) Query gives incorrect result when COUNT appears in the correlated subquery select list

[LakeShen (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752779#comment-17752779
 ] 

LakeShen commented on CALCITE-5743:
---

Hi [~leepb] ,could you give me a sql to reproduce this case using EMP and DEPT 
tables,I would reproduce it in my local environment.

> Query gives incorrect result when COUNT appears in the correlated subquery 
> select list
> --
>
> Key: CALCITE-5743
> URL: https://issues.apache.org/jira/browse/CALCITE-5743
> Project: Calcite
>  Issue Type: Bug
>Reporter: libopeng
>Priority: Major
>
> {code:java}
> SELECT a 
> FROM t1 t1 
> WHERE b IN (SELECT COUNT (*) FROM t2 WHERE t1.a=t2.a); {code}
> {code:java}
>   t1   | t2
> +--+   |  +-+
> | a | b |  |  | a |
> +--+   |  +-+
> | 3 | 6 |  |  | 3 |
> | 10 | 1 | |  | 3 |
> | 8 | 0 |  |  | 10 | 
>|
> {code}
> correct result
> {code:java}
> +--+
> | a |
> +--+
> | 10 |
> | 8 |{code}
> after decorrelate
> {code:java}
> LogicalProject(A=[$0])
>   LogicalJoin(condition=[AND(=($0, $3), =($1, $2))], joinType=[inner])
>     LogicalTableScan(table=[[t1]])
>     LogicalFilter(condition=[=($0, $0)])
>       LogicalProject(EXPR$0=[$1], a=[$0])
>         LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
>           LogicalProject(a=[$0])
>             LogicalFilter(condition=[=($0, $0)])
>               LogicalTableScan(table=[[t2]]) {code}
> error result
> {code:java}
> +--+
> | a |
> +--+
> | 10 | {code}
> Data with count=0 will be lost
> This issue was discovered in [this 
> issue|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5568]
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5184) In parser, allow "LIMIT start, ALL"

[hongyu guo (Jira)]

[ 
https://issues.apache.org/jira/browse/CALCITE-5184?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752765#comment-17752765
 ] 

hongyu guo commented on CALCITE-5184:
-

Hi, [~julianhyde]

Can you help me review this code again?  Thank you!

> In parser, allow "LIMIT start, ALL"
> ---
>
> Key: CALCITE-5184
> URL: https://issues.apache.org/jira/browse/CALCITE-5184
> Project: Calcite
>  Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: hongyu guo
>Priority: Major
>  Labels: pull-request-available
>
> In parser, allow "LIMIT start, ALL". For example,
> {code:sql}
> SELECT *
> FROM Emp
> ORDER BY sal DESC
> LIMIT 10, ALL
> {code}
> would be equivalent to
> {code:sql}
> SELECT *
> FROM Emp
> ORDER BY sal DESC
> OFFSET 10
> {code}
> No other database supports this syntax, but Calcite supports Postgres' "LIMIT 
> ALL" and MySQL's "LIMIT start, count" so it makes sense to combine them.
> See the original discussion in CALCITE-5086.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)