[
https://issues.apache.org/jira/browse/IMPALA-13483?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17941873#comment-17941873
]
weihua zhang edited comment on IMPALA-13483 at 5/21/25 1:55 PM:
----------------------------------------------------------------
{code:sql}
create table t1(k1 bigint, k2 bigint);
create table t2(k1 int, k2 string, k3 bigint, v1 bigint, v2 bigint);
create table t3(k1 bigint, k2 bigint);
create table if not exists sub_query_correlated_subquery1 (k1 bigint, k2
bigint);
create table if not exists sub_query_correlated_subquery3 (k1 int, k2 string,
k3 bigint, v1 bigint, v2 bigint);
insert into t1 values (1,null),(null,1),(1,2), (null,2),(1,3), (2,4), (2,5),
(3,3), (3,4), (20,2), (22,3), (24,4),(null,null);
insert into t2 values (1,'abc',2,3,4), (1,'abcd',3,3,4), (2,'xyz',2,4,2),
(2,'uvw',3,4,2), (2,'uvw',3,4,2), (3,'abc',4,5,3), (3,'abc',4,5,3),
(null,null,null,null,null);
insert into t3 values (1,null),(null,1),(1,4), (1,2), (null,3), (2,4), (3,7),
(3,9),(null,null),(5,1);
insert into sub_query_correlated_subquery1 values (1,null),(null,1),(1,2),
(null,2),(1,3), (2,4), (2,5), (3,3), (3,4), (20,2), (22,3), (24,4),(null,null);
insert into sub_query_correlated_subquery3 values (1,"abc",2,3,4),
(1,"abcd",3,3,4), (2,"xyz",2,4,2), (2,"uvw",3,4,2), (2,"uvw",3,4,2),
(3,"abc",4,5,3), (3,"abc",4,5,3), (null,null,null,null,null);
{code}
{code:sql}
select t1.* from t1 left join t2 on t1.k2 = t2.k3 and exists ( select t3.k1
from t3 where t1.k2 < t3.k2 ) or t1.k1 < 10 order by t1.k1, t1.k2;
ERROR: RuntimeException: Could not find a matching signature for call <($2, $1)
calcite bug, calcite main branch fixed it now!
{code}
{code:sql}
select t1.* from t1 left join t2 on t1.k2 = t2.k3 and t1.k1 < ( select max(k1)
from t3 where t1.k2 = t3.k2 ) OR t1.k1 < 10 order by t1.k1, t1.k2
ERROR: RuntimeException: Could not find a matching signature for call =($1, $5)
calcite bug, calcite main branch fixed it now!
{code}
was (Author: JIRAUSER307426):
{code:sql}
create table t1(k1 bigint, k2 bigint);
create table t2(k1 int, k2 string, k3 bigint, v1 bigint, v2 bigint);
create table t3(k1 bigint, k2 bigint);
create table if not exists sub_query_correlated_subquery1 (k1 bigint, k2
bigint);
create table if not exists sub_query_correlated_subquery3 (k1 int, k2 string,
k3 bigint, v1 bigint, v2 bigint);
insert into t1 values (1,null),(null,1),(1,2), (null,2),(1,3), (2,4), (2,5),
(3,3), (3,4), (20,2), (22,3), (24,4),(null,null);
insert into t2 values (1,'abc',2,3,4), (1,'abcd',3,3,4), (2,'xyz',2,4,2),
(2,'uvw',3,4,2), (2,'uvw',3,4,2), (3,'abc',4,5,3), (3,'abc',4,5,3),
(null,null,null,null,null);
insert into t3 values (1,null),(null,1),(1,4), (1,2), (null,3), (2,4), (3,7),
(3,9),(null,null),(5,1);
insert into sub_query_correlated_subquery1 values (1,null),(null,1),(1,2),
(null,2),(1,3), (2,4), (2,5), (3,3), (3,4), (20,2), (22,3), (24,4),(null,null);
insert into sub_query_correlated_subquery3 values (1,"abc",2,3,4),
(1,"abcd",3,3,4), (2,"xyz",2,4,2), (2,"uvw",3,4,2), (2,"uvw",3,4,2),
(3,"abc",4,5,3), (3,"abc",4,5,3), (null,null,null,null,null);
{code}
{code:sql}
select t1.* from t1 left join t2 on t1.k2 = t2.k3 and exists ( select t3.k1
from t3 where t1.k2 < t3.k2 ) or t1.k1 < 10 order by t1.k1, t1.k2;
ERROR: RuntimeException: Could not find a matching signature for call <($2, $1)
calcite bug
{code}
{code:sql}
select t1.* from t1 left join t2 on t1.k2 = t2.k3 and t1.k1 < ( select max(k1)
from t3 where t1.k2 = t3.k2 ) OR t1.k1 < 10 order by t1.k1, t1.k2
ERROR: RuntimeException: Could not find a matching signature for call =($1, $5)
{code}
> Calcite Planner: some scalar subquery throws exception when handle
> single_value
> -------------------------------------------------------------------------------
>
> Key: IMPALA-13483
> URL: https://issues.apache.org/jira/browse/IMPALA-13483
> Project: IMPALA
> Issue Type: Sub-task
> Reporter: weihua zhang
> Priority: Major
>
> {code:sql}
> create table correlated_scalar_t1(c1 bigint, c2 bigint);
> create table correlated_scalar_t2(c1 bigint, c2 bigint);
> insert into correlated_scalar_t1 values (1,null),(null,1),(1,2),
> (null,2),(1,3), (2,4), (2,5), (3,3), (3,4), (20,2), (22,3),
> (24,4),(null,null);
> insert into correlated_scalar_t2 values (1,null),(null,1),(1,4), (1,2),
> (null,3), (2,4), (3,7), (3,9),(null,null),(5,1);
> select c1 from correlated_scalar_t1 where correlated_scalar_t1.c2 > (select
> c1 from correlated_scalar_t2 where correlated_scalar_t1.c1 =
> correlated_scalar_t2.c1 and correlated_scalar_t2.c2 < 4) order by c1;{code}
> {code:java}
> LogicalSort(sort0=[$0], dir0=[ASC]), id = 717
> LogicalProject(C1=[$0]), id = 716
> LogicalJoin(condition=[AND(=($0, $2), >($1, $3))], joinType=[inner]), id
> = 715
> LogicalTableScan(table=[[default, correlated_scalar_t1]]), id = 547
> LogicalAggregate(group=[{0}], agg#0=[SINGLE_VALUE($1)]), id = 714
> LogicalProject(c11=[$0], C1=[$0]), id = 713
> LogicalFilter(condition=[AND(<($1, 4), IS NOT NULL($0))]), id = 712
> LogicalTableScan(table=[[default, correlated_scalar_t2]]), id =
> 549
> {code}
> Exception: java.lang.IndexOutOfBoundsException: Index: 3, Size: 3
> may be related to single_value
> hive plan:
> {code:java}
> explain cbo select c1 from correlated_scalar_t1 where correlated_scalar_t1.c2
> > (select c1 from correlated_scalar_t2 where correlated_scalar_t1.c1 =
> correlated_scalar_t2.c1 and correlated_scalar_t2.c2 < 4) order by c1;
> +----------------------------------------------------+
> | Explain |
> +----------------------------------------------------+
> | CBO PLAN: |
> | HiveSortLimit(sort0=[$0], dir0=[ASC]) |
> | HiveProject(c1=[$0]) |
> | HiveJoin(condition=[AND(=($0, $4), >($1, $3))], joinType=[inner],
> algorithm=[none], cost=[not available]) |
> | HiveJoin(condition=[=($0, $2)], joinType=[left], algorithm=[none],
> cost=[not available]) |
> | HiveProject(c1=[$0], c2=[$1]) |
> | HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1))]) |
> | HiveTableScan(table=[[default, correlated_scalar_t1]],
> table:alias=[correlated_scalar_t1]) |
> | HiveProject(c10=[$0]) |
> | HiveFilter(condition=[sq_count_check($1)]) |
> | HiveAggregate(group=[{0}], cnt=[COUNT()]) |
> | HiveFilter(condition=[AND(<($1, 4), IS NOT NULL($0))]) |
> | HiveTableScan(table=[[default, correlated_scalar_t2]],
> table:alias=[correlated_scalar_t2]) |
> | HiveProject(c1=[$0], c10=[$0]) |
> | HiveFilter(condition=[AND(<($1, 4), IS NOT NULL($0))]) |
> | HiveTableScan(table=[[default, correlated_scalar_t2]],
> table:alias=[correlated_scalar_t2]) |
> | |
> +----------------------------------------------------+
> 17 rows selected (0.935 seconds)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
