You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@asterixdb.apache.org by "Wail Y. Alkowaileet (Jira)" <ji...@apache.org> on 2019/10/19 17:42:00 UTC
[jira] [Updated] (ASTERIXDB-2661) SQL++ and AQL produce different
plans
[ https://issues.apache.org/jira/browse/ASTERIXDB-2661?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Wail Y. Alkowaileet updated ASTERIXDB-2661:
-------------------------------------------
Description:
This one is found by Miles and [~imaxon]
DDL SQL++:
{code:java}
DROP DATAVERSE test IF EXISTS;
CREATE DATAVERSE test;
USE test;
CREATE TYPE ifType AS OPEN
{
id: int32
};CREATE DATASET ifds(ifType)
PRIMARY KEY id;
{code}
SQL++ Query:
{code:java}
USE test;
SELECT VALUE x
FROM ifds as x
WHERE (
CASE WHEN (x.names.count = "1") THEN
x.names.name.firstName = "Tom"
ELSE
(some v in x.names.name satisfies v.firstName = "Tom")
END
);{code}
SQL++ Plan:
{code:java}
distribute result [$$x]
-- DISTRIBUTE_RESULT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$x])
-- STREAM_PROJECT |PARTITIONED|
select (switch-case(TRUE, eq($$37, "1"), eq($$38, "Tom"), $$32))
-- STREAM_SELECT |PARTITIONED|
project ([$$x, $$37, $$38, $$32])
-- STREAM_PROJECT |PARTITIONED|
subplan {
aggregate [$$32] <- [non-empty-stream()]
-- AGGREGATE |LOCAL|
select (eq($$36, "Tom"))
-- STREAM_SELECT |LOCAL|
assign [$$36] <- [$$v.getField("firstName")]
-- ASSIGN |LOCAL|
unnest $$v <- scan-collection($$39)
-- UNNEST |LOCAL|
nested tuple source
-- NESTED_TUPLE_SOURCE |LOCAL|
}
-- SUBPLAN |PARTITIONED|
assign [$$38] <- [$$39.getField("firstName")]
-- ASSIGN |PARTITIONED|
project ([$$x, $$37, $$39])
-- STREAM_PROJECT |PARTITIONED|
assign [$$37, $$39] <- [$$33.getField("count"), $$33.getField("name")]
-- ASSIGN |PARTITIONED|
assign [$$33] <- [$$x.getField("names")]
-- ASSIGN |PARTITIONED|
project ([$$x])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
data-scan []<-[$$34, $$x] <- test.ifds
-- DATASOURCE_SCAN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
{code}
AQL Query:
{code:java}
use dataverse test;
for $x in dataset ifds
where (
if ($x.names.count = "1") then
$x.names.name.firstName = "Tom"
else
(some $v in $x.names.name satisfies $v.firstName = "Tom")
)
return $x;
{code}
AQL Plan:
{code:java}
distribute result [$$x]
-- DISTRIBUTE_RESULT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$x])
-- STREAM_PROJECT |PARTITIONED|
select ($$18)
-- STREAM_SELECT |PARTITIONED|
project ([$$x, $$18])
-- STREAM_PROJECT |PARTITIONED|
unnest $$18 <- scan-collection($$17)
-- UNNEST |PARTITIONED|
project ([$$x, $$17])
-- STREAM_PROJECT |PARTITIONED|
assign [$$17] <- [switch-case(eq($$21, "1"), TRUE, $$10, cast($$16))]
-- ASSIGN |PARTITIONED|
project ([$$x, $$21, $$10, $$16])
-- STREAM_PROJECT |PARTITIONED|
subplan {
aggregate [$$16] <- [listify($$15)]
-- AGGREGATE |LOCAL|
aggregate [$$15] <- [non-empty-stream()]
-- AGGREGATE |LOCAL|
select (eq($$26, "Tom"))
-- STREAM_SELECT |LOCAL|
assign [$$26] <- [$$v.getField("firstName")]
-- ASSIGN |LOCAL|
unnest $$v <- scan-collection($$25)
-- UNNEST |LOCAL|
select (not(and($$33, not(is-unknown($$33)))))
-- STREAM_SELECT |LOCAL|
assign [$$33] <- [eq($$21, "1")]
-- ASSIGN |LOCAL|
nested tuple source
-- NESTED_TUPLE_SOURCE |LOCAL|
}
-- SUBPLAN |PARTITIONED|
subplan {
aggregate [$$10] <- [listify($$9)]
-- AGGREGATE |LOCAL|
assign [$$9] <- [eq($$22, "Tom")]
-- ASSIGN |LOCAL|
assign [$$22] <- [$$30.getField("firstName")]
-- ASSIGN |LOCAL|
assign [$$30] <- [$$29.getField("name")]
-- ASSIGN |LOCAL|
assign [$$29] <- [$$x.getField("names")]
-- ASSIGN |LOCAL|
select (eq($$21, "1"))
-- STREAM_SELECT |LOCAL|
nested tuple source
-- NESTED_TUPLE_SOURCE |LOCAL|
}
-- SUBPLAN |PARTITIONED|
project ([$$x, $$21, $$25])
-- STREAM_PROJECT |PARTITIONED|
assign [$$21, $$25] <- [$$28.getField("count"), $$28.getField("name")]
-- ASSIGN |PARTITIONED|
assign [$$28] <- [$$x.getField("names")]
-- ASSIGN |PARTITIONED|
project ([$$x])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
data-scan []<-[$$20, $$x] <- test.ifds
-- DATASOURCE_SCAN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
{code}
was:
This one is found by Miles and [~imaxon]
DDL SQL++:
{code:java}
DROP DATAVERSE test IF EXISTS;
CREATE DATAVERSE test;
USE test;
CREATE TYPE ifType AS OPEN
{
id: int32
};CREATE DATASET ifds(ifType)
PRIMARY KEY id;
{code}
SQL++ Query:
{code:java}
USE test;
SELECT VALUE x
FROM ifds as x
WHERE (
CASE WHEN (x.names.count = "1") THEN
x.names.name.firstName = "Tom"
ELSE
(some v in x.names.name satisfies v.firstName = "Tom")
END
);{code}
SQL++ Plan:
{code:java}
distribute result [$$x]
-- DISTRIBUTE_RESULT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$x])
-- STREAM_PROJECT |PARTITIONED|
select (switch-case(TRUE, eq($$37, \"1\"), eq($$38, \"Tom\"), $$32))
-- STREAM_SELECT |PARTITIONED|
project ([$$x, $$37, $$38, $$32])
-- STREAM_PROJECT |PARTITIONED|
subplan {
aggregate [$$32] <- [non-empty-stream()]
-- AGGREGATE |LOCAL|
select (eq($$36, \"Tom\"))
-- STREAM_SELECT |LOCAL|
assign [$$36] <- [$$v.getField(\"firstName\")]
-- ASSIGN |LOCAL|
unnest $$v <- scan-collection($$39)
-- UNNEST |LOCAL|
nested tuple source
-- NESTED_TUPLE_SOURCE |LOCAL|
}
-- SUBPLAN |PARTITIONED|
assign [$$38] <- [$$39.getField(\"firstName\")]
-- ASSIGN |PARTITIONED|
project ([$$x, $$37, $$39])
-- STREAM_PROJECT |PARTITIONED|
assign [$$37, $$39] <- [$$33.getField(\"count\"), $$33.getField(\"name\")]
-- ASSIGN |PARTITIONED|
assign [$$33] <- [$$x.getField(\"names\")]
-- ASSIGN |PARTITIONED|
project ([$$x])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
data-scan []<-[$$34, $$x] <- test.ifds
-- DATASOURCE_SCAN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
{code}
AQL Query:
{code:java}
use dataverse test;
for $x in dataset ifds
where (
if ($x.names.count = "1") then
$x.names.name.firstName = "Tom"
else
(some $v in $x.names.name satisfies $v.firstName = "Tom")
)
return $x;
{code}
AQL Plan:
{code:java}
distribute result [$$x]
-- DISTRIBUTE_RESULT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$x])
-- STREAM_PROJECT |PARTITIONED|
select ($$18)
-- STREAM_SELECT |PARTITIONED|
project ([$$x, $$18])
-- STREAM_PROJECT |PARTITIONED|
unnest $$18 <- scan-collection($$17)
-- UNNEST |PARTITIONED|
project ([$$x, $$17])
-- STREAM_PROJECT |PARTITIONED|
assign [$$17] <- [switch-case(eq($$21, "1"), TRUE, $$10, cast($$16))]
-- ASSIGN |PARTITIONED|
project ([$$x, $$21, $$10, $$16])
-- STREAM_PROJECT |PARTITIONED|
subplan {
aggregate [$$16] <- [listify($$15)]
-- AGGREGATE |LOCAL|
aggregate [$$15] <- [non-empty-stream()]
-- AGGREGATE |LOCAL|
select (eq($$26, "Tom"))
-- STREAM_SELECT |LOCAL|
assign [$$26] <- [$$v.getField("firstName")]
-- ASSIGN |LOCAL|
unnest $$v <- scan-collection($$25)
-- UNNEST |LOCAL|
select (not(and($$33, not(is-unknown($$33)))))
-- STREAM_SELECT |LOCAL|
assign [$$33] <- [eq($$21, "1")]
-- ASSIGN |LOCAL|
nested tuple source
-- NESTED_TUPLE_SOURCE |LOCAL|
}
-- SUBPLAN |PARTITIONED|
subplan {
aggregate [$$10] <- [listify($$9)]
-- AGGREGATE |LOCAL|
assign [$$9] <- [eq($$22, "Tom")]
-- ASSIGN |LOCAL|
assign [$$22] <- [$$30.getField("firstName")]
-- ASSIGN |LOCAL|
assign [$$30] <- [$$29.getField("name")]
-- ASSIGN |LOCAL|
assign [$$29] <- [$$x.getField("names")]
-- ASSIGN |LOCAL|
select (eq($$21, "1"))
-- STREAM_SELECT |LOCAL|
nested tuple source
-- NESTED_TUPLE_SOURCE |LOCAL|
}
-- SUBPLAN |PARTITIONED|
project ([$$x, $$21, $$25])
-- STREAM_PROJECT |PARTITIONED|
assign [$$21, $$25] <- [$$28.getField("count"), $$28.getField("name")]
-- ASSIGN |PARTITIONED|
assign [$$28] <- [$$x.getField("names")]
-- ASSIGN |PARTITIONED|
project ([$$x])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
data-scan []<-[$$20, $$x] <- test.ifds
-- DATASOURCE_SCAN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
{code}
> SQL++ and AQL produce different plans
> -------------------------------------
>
> Key: ASTERIXDB-2661
> URL: https://issues.apache.org/jira/browse/ASTERIXDB-2661
> Project: Apache AsterixDB
> Issue Type: Bug
> Components: COMP - Compiler
> Affects Versions: 0.9.4.2
> Reporter: Wail Y. Alkowaileet
> Priority: Major
>
> This one is found by Miles and [~imaxon]
> DDL SQL++:
> {code:java}
> DROP DATAVERSE test IF EXISTS;
> CREATE DATAVERSE test;
> USE test;
> CREATE TYPE ifType AS OPEN
> {
> id: int32
> };CREATE DATASET ifds(ifType)
> PRIMARY KEY id;
> {code}
> SQL++ Query:
> {code:java}
> USE test;
> SELECT VALUE x
> FROM ifds as x
> WHERE (
> CASE WHEN (x.names.count = "1") THEN
> x.names.name.firstName = "Tom"
> ELSE
> (some v in x.names.name satisfies v.firstName = "Tom")
> END
> );{code}
> SQL++ Plan:
> {code:java}
> distribute result [$$x]
> -- DISTRIBUTE_RESULT |PARTITIONED|
> exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> project ([$$x])
> -- STREAM_PROJECT |PARTITIONED|
> select (switch-case(TRUE, eq($$37, "1"), eq($$38, "Tom"), $$32))
> -- STREAM_SELECT |PARTITIONED|
> project ([$$x, $$37, $$38, $$32])
> -- STREAM_PROJECT |PARTITIONED|
> subplan {
> aggregate [$$32] <- [non-empty-stream()]
> -- AGGREGATE |LOCAL|
> select (eq($$36, "Tom"))
> -- STREAM_SELECT |LOCAL|
> assign [$$36] <- [$$v.getField("firstName")]
> -- ASSIGN |LOCAL|
> unnest $$v <- scan-collection($$39)
> -- UNNEST |LOCAL|
> nested tuple source
> -- NESTED_TUPLE_SOURCE |LOCAL|
> }
> -- SUBPLAN |PARTITIONED|
> assign [$$38] <- [$$39.getField("firstName")]
> -- ASSIGN |PARTITIONED|
> project ([$$x, $$37, $$39])
> -- STREAM_PROJECT |PARTITIONED|
> assign [$$37, $$39] <- [$$33.getField("count"), $$33.getField("name")]
> -- ASSIGN |PARTITIONED|
> assign [$$33] <- [$$x.getField("names")]
> -- ASSIGN |PARTITIONED|
> project ([$$x])
> -- STREAM_PROJECT |PARTITIONED|
> exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> data-scan []<-[$$34, $$x] <- test.ifds
> -- DATASOURCE_SCAN |PARTITIONED|
> exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> empty-tuple-source
> -- EMPTY_TUPLE_SOURCE |PARTITIONED|
> {code}
>
>
> AQL Query:
> {code:java}
> use dataverse test;
> for $x in dataset ifds
> where (
> if ($x.names.count = "1") then
> $x.names.name.firstName = "Tom"
> else
> (some $v in $x.names.name satisfies $v.firstName = "Tom")
> )
> return $x;
> {code}
> AQL Plan:
> {code:java}
> distribute result [$$x]
> -- DISTRIBUTE_RESULT |PARTITIONED|
> exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> project ([$$x])
> -- STREAM_PROJECT |PARTITIONED|
> select ($$18)
> -- STREAM_SELECT |PARTITIONED|
> project ([$$x, $$18])
> -- STREAM_PROJECT |PARTITIONED|
> unnest $$18 <- scan-collection($$17)
> -- UNNEST |PARTITIONED|
> project ([$$x, $$17])
> -- STREAM_PROJECT |PARTITIONED|
> assign [$$17] <- [switch-case(eq($$21, "1"), TRUE, $$10, cast($$16))]
> -- ASSIGN |PARTITIONED|
> project ([$$x, $$21, $$10, $$16])
> -- STREAM_PROJECT |PARTITIONED|
> subplan {
> aggregate [$$16] <- [listify($$15)]
> -- AGGREGATE |LOCAL|
> aggregate [$$15] <- [non-empty-stream()]
> -- AGGREGATE |LOCAL|
> select (eq($$26, "Tom"))
> -- STREAM_SELECT |LOCAL|
> assign [$$26] <- [$$v.getField("firstName")]
> -- ASSIGN |LOCAL|
> unnest $$v <- scan-collection($$25)
> -- UNNEST |LOCAL|
> select (not(and($$33, not(is-unknown($$33)))))
> -- STREAM_SELECT |LOCAL|
> assign [$$33] <- [eq($$21, "1")]
> -- ASSIGN |LOCAL|
> nested tuple source
> -- NESTED_TUPLE_SOURCE |LOCAL|
> }
> -- SUBPLAN |PARTITIONED|
> subplan {
> aggregate [$$10] <- [listify($$9)]
> -- AGGREGATE |LOCAL|
> assign [$$9] <- [eq($$22, "Tom")]
> -- ASSIGN |LOCAL|
> assign [$$22] <- [$$30.getField("firstName")]
> -- ASSIGN |LOCAL|
> assign [$$30] <- [$$29.getField("name")]
> -- ASSIGN |LOCAL|
> assign [$$29] <- [$$x.getField("names")]
> -- ASSIGN |LOCAL|
> select (eq($$21, "1"))
> -- STREAM_SELECT |LOCAL|
> nested tuple source
> -- NESTED_TUPLE_SOURCE |LOCAL|
> }
> -- SUBPLAN |PARTITIONED|
> project ([$$x, $$21, $$25])
> -- STREAM_PROJECT |PARTITIONED|
> assign [$$21, $$25] <- [$$28.getField("count"), $$28.getField("name")]
> -- ASSIGN |PARTITIONED|
> assign [$$28] <- [$$x.getField("names")]
> -- ASSIGN |PARTITIONED|
> project ([$$x])
> -- STREAM_PROJECT |PARTITIONED|
> exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> data-scan []<-[$$20, $$x] <- test.ifds
> -- DATASOURCE_SCAN |PARTITIONED|
> exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> empty-tuple-source
> -- EMPTY_TUPLE_SOURCE |PARTITIONED|
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)