drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Krystal (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-789) Left outer join returns "null" values for columns from the right table
Date Tue, 20 May 2014 18:48:38 GMT

    [ https://issues.apache.org/jira/browse/DRILL-789?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14003813#comment-14003813
] 

Krystal commented on DRILL-789:
-------------------------------

Here is the full explain plan:
0: jdbc:drill:schema=dfs> explain plan for select voter.name voter_name, voter.registration
registration, student.name student_name, student.gpa gpa from voter inner join student on
(student.name = voter.name) where voter.age < 30;
+------------+------------+
|    text    |    json    |
+------------+------------+
| ScreenPrel
  UnionExchangePrel
    ProjectPrel(voter_name=[$1], registration=[$3], student_name=[$5], gpa=[$6])
      HashJoinPrel(condition=[=($5, $1)], joinType=[inner])
        HashToRandomExchangePrel(dist0=[[$1]])
          FilterPrel(condition=[<($2, 30)])
            ScanPrel(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=maprfs:/drill/testdata/p1tests/voter]],
selectionRoot=/drill/testdata/p1tests/voter, columns=[SchemaPath [`name`], SchemaPath [`age`],
SchemaPath [`registration`]]]])
        HashToRandomExchangePrel(dist0=[[$1]])
          ScanPrel(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=maprfs:/drill/testdata/p1tests/student]],
selectionRoot=/drill/testdata/p1tests/student, columns=[SchemaPath [`name`], SchemaPath [`gpa`]]]])
 | {
  "head" : {
    "version" : 1,
    "generator" : {
      "type" : "ExplainHandler",
      "info" : ""
    },
    "type" : "APACHE_DRILL_PHYSICAL",
    "options" : [ ],
    "resultMode" : "EXEC"
  },
  "graph" : [ {
    "pop" : "parquet-scan",
    "@id" : 1,
    "entries" : [ {
      "path" : "maprfs:/drill/testdata/p1tests/voter"
    } ],
    "storage" : {
      "type" : "file",
      "connection" : "maprfs:///",
      "workspaces" : {
        "root" : {
          "location" : "/",
          "writable" : false,
          "storageformat" : null
        },
        "default" : {
          "location" : "/drill/testdata/p1tests",
          "writable" : true,
          "storageformat" : null
        },
        "tpcds" : {
          "location" : "/drill/testdata/tpc-ds-main",
          "writable" : true,
          "storageformat" : null
        }
      },
      "formats" : null
    },
    "format" : {
      "type" : "parquet"
    },
    "columns" : [ "`name`", "`age`", "`registration`" ],
    "selectionRoot" : "/drill/testdata/p1tests/voter"
  }, {
    "pop" : "filter",
    "@id" : 2,
    "child" : 1,
    "expr" : "less_than(`age`, 30) ",
    "initialAllocation" : 1000000,
    "maxAllocation" : 10000000000
  }, {
    "pop" : "hash-to-random-exchange",
    "@id" : 3,
    "child" : 2,
    "expr" : "hash(`name`) ",
    "initialAllocation" : 1000000,
    "maxAllocation" : 10000000000
  }, {
    "pop" : "parquet-scan",
    "@id" : 4,
    "entries" : [ {
      "path" : "maprfs:/drill/testdata/p1tests/student"
    } ],
    "storage" : {
      "type" : "file",
      "connection" : "maprfs:///",
      "workspaces" : {
        "root" : {
          "location" : "/",
          "writable" : false,
          "storageformat" : null
        },
        "default" : {
          "location" : "/drill/testdata/p1tests",
          "writable" : true,
          "storageformat" : null
        },
        "tpcds" : {
          "location" : "/drill/testdata/tpc-ds-main",
          "writable" : true,
          "storageformat" : null
        }
      },
      "formats" : null
    },
    "format" : {
      "type" : "parquet"
    },
    "columns" : [ "`name`", "`gpa`" ],
    "selectionRoot" : "/drill/testdata/p1tests/student"
  }, {
    "pop" : "hash-to-random-exchange",
    "@id" : 5,
    "child" : 4,
    "expr" : "hash(`name`) ",
    "initialAllocation" : 1000000,
    "maxAllocation" : 10000000000
  }, {
    "pop" : "project",
    "@id" : 6,
    "exprs" : [ {
      "ref" : "`*0`",
      "expr" : "`*`"
    }, {
      "ref" : "`name0`",
      "expr" : "`name`"
    }, {
      "ref" : "`gpa`",
      "expr" : "`gpa`"
    } ],
    "child" : 5,
    "initialAllocation" : 1000000,
    "maxAllocation" : 10000000000
  }, {
    "pop" : "hash-join",
    "@id" : 7,
    "left" : 3,
    "right" : 6,
    "conditions" : [ {
      "relationship" : "==",
      "left" : "`name`",
      "right" : "`name0`"
    } ],
    "joinType" : "INNER",
    "initialAllocation" : 1000000,
    "maxAllocation" : 10000000000
  }, {
    "pop" : "project",
    "@id" : 8,
    "exprs" : [ {
      "ref" : "`voter_name`",
      "expr" : "`name`"
    }, {
      "ref" : "`registration`",
      "expr" : "`registration`"
    }, {
      "ref" : "`student_name`",
      "expr" : "`name0`"
    }, {
      "ref" : "`gpa`",
      "expr" : "`gpa`"
    } ],
    "child" : 7,
    "initialAllocation" : 1000000,
    "maxAllocation" : 10000000000
  }, {
    "pop" : "union-exchange",
    "@id" : 9,
    "child" : 8,
    "initialAllocation" : 1000000,
    "maxAllocation" : 10000000000
  }, {
    "pop" : "screen",
    "@id" : 10,
    "child" : 9,
    "initialAllocation" : 1000000,
    "maxAllocation" : 10000000000
  } ]
} |
+------------+------------+
1 row selected (0.495 seconds)


> Left outer join returns "null" values for columns from the right table
> ----------------------------------------------------------------------
>
>                 Key: DRILL-789
>                 URL: https://issues.apache.org/jira/browse/DRILL-789
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Functions - Drill
>            Reporter: Krystal
>
> git.commit.id.abbrev=5d7e3d3
> 0: jdbc:drill:schema=dfs> select voter.name voter_name, voter.registration registration,
student.name student_name, student.gpa gpa from voter left outer join student on (student.name
= voter.name) where voter.age < 30;
> +------------+--------------+--------------+------------+
> | voter_name | registration | student_name |    gpa     |
> +------------+--------------+--------------+------------+
> | [B@6ca2652 | [B@4199d4f9  | null         | null       |
> | [B@5a6d4914 | [B@3cd8ee6d  | null         | null       |
> | [B@460d5550 | [B@155c1b1e  | null         | null       |
> | [B@51f85986 | [B@7bd9675   | null         | null       |
> | [B@2fe0df4b | [B@5463cd7b  | null         | null       |
> | [B@64477185 | [B@e6e0632   | null         | null       |



--
This message was sent by Atlassian JIRA
(v6.2#6252)

Mime
View raw message