Mailing-List: contact dev-help@drill.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@drill.apache.org
MIME-Version: 1.0
In-Reply-To: <CALqceLF_-L-Ve-JonbK9oXbkzzjHiERgkvkrQeM=zuy-mi1HHw@mail.gmail.com>
References: <CALqceLF_-L-Ve-JonbK9oXbkzzjHiERgkvkrQeM=zuy-mi1HHw@mail.gmail.com>
From: Zelaine Fong <zfong@maprtech.com>
Date: Wed, 26 Oct 2016 13:50:28 -0700
Message-ID: <CACX89XeW10MZjqEDiQ3rmMWEk7JMxjYhD7qsnWgL4fSjnov0Ww@mail.gmail.com>
Subject: Re: Filter appears above project in query plan - Null Equality Join
To: dev <dev@drill.apache.org>
Content-Type: multipart/alternative; boundary=089e013d11b0754078053fcac61d
archived-at: Wed, 26 Oct 2016 20:50:35 -0000

--089e013d11b0754078053fcac61d
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

The filter, I assume you' referring to, is a join filter.  So, at a
minimum, it needs to be applied after the hash join.  I'm not sure there's
a lot of benefit in pushing that filter past the project that's on top of
the hash join.

-- Zelaine

On Wed, Oct 26, 2016 at 8:59 AM, Khurram Faraaz <kfaraaz@maprtech.com>
wrote:

> Hi All,
>
> Filter is seen on top of Project in query plan for a null equality join.
> This is over CSV data, shouldn't the filter appear below the project in t=
he
> query plan ?
> I am on Drill 1.9.0 git commit id: a29f1e29
>
> Note : t1 has some nulls in it
>            t2 does not have any nulls in it.
>
> {noformat}
> 0: jdbc:drill:schema=3Ddfs.tmp> explain plan for
> select * from `oneColDupsWnulls.csv` t1 JOIN `oneColWOnulls.csv` t2
> ON t1.columns[0] =3D t2.columns[0]
> WHERE t1.columns[0] IS NOT DISTINCT FROM t2.columns[0]
> OR ( t1.columns[0] IS NULL AND t2.columns[0] IS NULL );
> +------+------+
> | text | json |
> +------+------+
> | 00-00    Screen
> 00-01      ProjectAllowDup(*=3D[$0], *0=3D[$1])
> 00-02        Project(T43=C2=A6=C2=A6*=3D[$0], T44=C2=A6=C2=A6*=3D[$2])
> 00-03          SelectionVectorRemover
> 00-04            Filter(condition=3D[OR(CAST(CASE(IS NULL(ITEM($1, 0)), I=
S
> NULL(ITEM($3, 0)), IS NULL(ITEM($3, 0)), IS NULL(ITEM($1, 0)), =3D(ITEM($=
1,
> 0), ITEM($3, 0)))):BOOLEAN NOT NULL, AND(IS NULL(ITEM($1, 0)), IS
> NULL(ITEM($3, 0))))])
> 00-05              Project(T43=C2=A6=C2=A6*=3D[$0], columns=3D[$1], T44=
=C2=A6=C2=A6*=3D[$3],
> columns0=3D[$4])
> 00-06                HashJoin(condition=3D[=3D($2, $5)], joinType=3D[inne=
r])
> 00-07                  Project(T44=C2=A6=C2=A6*=3D[$0], columns0=3D[$1], =
$f20=3D[$2])
> 00-09                    Project(T44=C2=A6=C2=A6*=3D[$0], columns=3D[$1],=
 $f2=3D[ITEM($1,
> 0)])
> 00-11                      Project(T44=C2=A6=C2=A6*=3D[$0], columns=3D[$1=
])
> 00-13                        Scan(groupscan=3D[EasyGroupScan
> [selectionRoot=3Dmaprfs:/tmp/oneColWOnulls.csv, numFiles=3D1, columns=3D[=
`*`],
> files=3D[maprfs:///tmp/oneColWOnulls.csv]]])
> 00-08                  Project(T43=C2=A6=C2=A6*=3D[$0], columns=3D[$1], $=
f2=3D[ITEM($1,
> 0)])
> 00-10                    Project(T43=C2=A6=C2=A6*=3D[$0], columns=3D[$1])
> 00-12                      Scan(groupscan=3D[EasyGroupScan
> [selectionRoot=3Dmaprfs:/tmp/oneColDupsWnulls.csv, numFiles=3D1,
> columns=3D[`*`],
> files=3D[maprfs:///tmp/oneColDupsWnulls.csv]]])
> {noformat}
>
> Results returned by query
>
> {noformat}
> 0: jdbc:drill:schema=3Ddfs.tmp> select * from `oneColDupsWnulls.csv` t1 J=
OIN
> `oneColWOnulls.csv` t2 ON t1.columns[0] =3D t2.columns[0] WHERE t1.column=
s[0]
> IS NOT DISTINCT FROM t2.columns[0] OR ( t1.columns[0] IS NULL AND
> t2.columns[0] IS NULL );
> +-------------+-------------+
> |   columns   |  columns0   |
> +-------------+-------------+
> | ["test"]    | ["test"]    |
> | ["foo"]     | ["foo"]     |
> | ["foo"]     | ["foo"]     |
> | ["bar"]     | ["bar"]     |
> | ["yes"]     | ["yes"]     |
> | ["yes"]     | ["yes"]     |
> | ["no"]      | ["no"]      |
> | ["no"]      | ["no"]      |
> | ["foobar"]  | ["foobar"]  |
> | ["foobar"]  | ["foobar"]  |
> | ["never"]   | ["never"]   |
> | ["never"]   | ["never"]   |
> | ["ever"]    | ["ever"]    |
> | ["ever"]    | ["ever"]    |
> | ["here"]    | ["here"]    |
> | ["there"]   | ["there"]   |
> | ["no"]      | ["no"]      |
> | ["no"]      | ["no"]      |
> | ["yes"]     | ["yes"]     |
> | ["yes"]     | ["yes"]     |
> | ["foobar"]  | ["foobar"]  |
> | ["foobar"]  | ["foobar"]  |
> | ["temp"]    | ["temp"]    |
> +-------------+-------------+
> 23 rows selected (0.341 seconds)
> {noformat}
>
> Thanks,
> Khurram
>

--089e013d11b0754078053fcac61d--