[jira] [Created] (DRILL-4211) Inconsistent results from a joined sql statement to postgres tables

["Robert Hamilton-Smith (JIRA)" <ji...@apache.org>]

Robert Hamilton-Smith created DRILL-4211:
--------------------------------------------

             Summary: Inconsistent results from a joined sql statement to postgres tables
                 Key: DRILL-4211
                 URL: https://issues.apache.org/jira/browse/DRILL-4211
             Project: Apache Drill
          Issue Type: Bug
          Components: Execution - Relational Operators
    Affects Versions: 1.3.0
         Environment: Postgres db stroage
            Reporter: Robert Hamilton-Smith


When making an sql statement that incorporates a join to a table and then a self join to that table to get a parent value , Drill brings back inconsistent results. 

Here is the sql in postgres with correct output:
{code:sql}
select trx.categoryguid,
cat.categoryname, w1.categoryname as parentcat
from transactions trx
join categories cat on (cat.CATEGORYGUID = trx.CATEGORYGUID)
join categories w1 on (cat.categoryparentguid = w1.categoryguid)
where cat.categoryparentguid IS NOT NULL;
{code}

Output:
||categoryid||categoryname||parentcategory||
|id1|restaurants|food&Dining|
|id1|restaurants|food&Dining|
|id2|Coffee Shops|food&Dining|
|id2|Coffee Shops|food&Dining|

When run in Drill with correct storage prefix:
{code:sql}
select trx.categoryguid,
cat.categoryname, w1.categoryname as parentcat
from db.schema.transactions trx
join db.schema.categories cat on (cat.CATEGORYGUID = trx.CATEGORYGUID)
join db.schema.wpfm_categories w1 on (cat.categoryparentguid = w1.categoryguid)
where cat.categoryparentguid IS NOT NULL
{code}

Results are:
||categoryid||categoryname||parentcategory||
|id1|restaurants|null|
|id1|restaurants|null|
|id2|Coffee Shops|null|
|id2|Coffee Shops|null|

Physical plan is:
{code:sql}
00-00    Screen : rowType = RecordType(VARCHAR(50) categoryguid, VARCHAR(50) categoryname, VARCHAR(50) parentcat): rowcount = 100.0, cumulative cost = {110.0 rows, 110.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 64293
00-01      Project(categoryguid=[$0], categoryname=[$1], parentcat=[$2]) : rowType = RecordType(VARCHAR(50) categoryguid, VARCHAR(50) categoryname, VARCHAR(50) parentcat): rowcount = 100.0, cumulative cost = {100.0 rows, 100.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 64292
00-02        Project(categoryguid=[$9], categoryname=[$41], parentcat=[$47]) : rowType = RecordType(VARCHAR(50) categoryguid, VARCHAR(50) categoryname, VARCHAR(50) parentcat): rowcount = 100.0, cumulative cost = {100.0 rows, 100.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 64291
00-03          Jdbc(sql=[SELECT *
FROM "public"."transactions"
INNER JOIN (SELECT *
FROM "public"."categories"
WHERE "categoryparentguid" IS NOT NULL) AS "t" ON "transactions"."categoryguid" = "t"."categoryguid"
INNER JOIN "public"."categories" AS "categories0" ON "t"."categoryparentguid" = "categories0"."categoryguid"]) : rowType = RecordType(VARCHAR(255) transactionguid, VARCHAR(255) relatedtransactionguid, VARCHAR(255) transactioncode, DECIMAL(1, 0) transactionpending, VARCHAR(50) transactionrefobjecttype, VARCHAR(255) transactionrefobjectguid, VARCHAR(1024) transactionrefobjectvalue, TIMESTAMP(6) transactiondate, VARCHAR(256) transactiondescription, VARCHAR(50) categoryguid, VARCHAR(3) transactioncurrency, DECIMAL(15, 3) transactionoldbalance, DECIMAL(13, 3) transactionamount, DECIMAL(15, 3) transactionnewbalance, VARCHAR(512) transactionnotes, DECIMAL(2, 0) transactioninstrumenttype, VARCHAR(20) transactioninstrumentsubtype, VARCHAR(20) transactioninstrumentcode, VARCHAR(50) transactionorigpartyguid, VARCHAR(255) transactionorigaccountguid, VARCHAR(50) transactionrecpartyguid, VARCHAR(255) transactionrecaccountguid, VARCHAR(256) transactionstatementdesc, DECIMAL(1, 0) transactionsplit, DECIMAL(1, 0) transactionduplicated, DECIMAL(1, 0) transactionrecategorized, TIMESTAMP(6) transactioncreatedat, TIMESTAMP(6) transactionupdatedat, VARCHAR(50) transactionmatrulerefobjtype, VARCHAR(50) transactionmatrulerefobjguid, VARCHAR(50) transactionmatrulerefobjvalue, VARCHAR(50) transactionuserruleguid, DECIMAL(2, 0) transactionsplitorder, TIMESTAMP(6) transactionprocessedat, TIMESTAMP(6) transactioncategoryassignat, VARCHAR(50) transactionsystemcategoryguid, VARCHAR(50) transactionorigmandateid, VARCHAR(100) fingerprint, VARCHAR(50) categoryguid0, VARCHAR(50) categoryparentguid, DECIMAL(3, 0) categorytype, VARCHAR(50) categoryname, VARCHAR(50) categorydescription, VARCHAR(50) partyguid, VARCHAR(50) categoryguid1, VARCHAR(50) categoryparentguid0, DECIMAL(3, 0) categorytype0, VARCHAR(50) categoryname0, VARCHAR(50) categorydescription0, VARCHAR(50) partyguid0): rowcount = 100.0, cumulative cost = {100.0 rows, 100.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 64259
{code}

I worked around it by creating a view on postgres but not ideal. Thanks in advance.
First Drill Jira Bug.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)