[ASTERIXDB-2661] SQL++ and AQL produce different plans

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 0.9.4.2
• Component/s: COMP - Compiler
• Assignee: Unassigned
• Reporter: Wail Y. Alkowaileet
• Created: 19/Oct/19 17:39
• Updated: 05/Jan/24 21:29
• Resolved: 05/Jan/24 21:29

Description

This one is found by Miles and imaxon

DDL SQL++:

DROP DATAVERSE test IF EXISTS;
CREATE DATAVERSE test;

USE test;
CREATE TYPE ifType AS OPEN
{
    id: int32
};CREATE DATASET ifds(ifType)
PRIMARY KEY id;

SQL++ Query:

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
);

SQL++ Plan:

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|

 

 

AQL Query:

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;

AQL Plan:

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|

Issue Links

• Blocked: ASTERIXDB-1203 QuantifiedExpressions in IfThenElse throws NPE