[
https://issues.apache.org/jira/browse/DRILL-4096?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15007802#comment-15007802
]
Rahul Challapalli commented on DRILL-4096:
------------------------------------------
The below file has schema changes which hive treats as null based on the
defined type
null_schema.tbl
{code}
1|98723980547|null|00:00:00|null|P18582D|jllkjsdhfg|2345.33|null|false
null|24509823475|null|01:00:00|1997-01-02
00:00:00.0|P1DT9045S|jhgduitweriuoert|3243.32|664522.332|true
3|05:10:36|null|null|null|P1DT9000S|gfdstweopiu|234533.2|234522.3221|true
4|null|1996-01-29|true|null|P10DT9000S|gjklhsdfgkjhkASDF|null|2222234543245.44|false
5|39575748|1996-03-01|12:00:00|P1DT9045|P1DT9045.100S|oieoiutriotureWERTgwgEWRg|23.5|654.4|false
6|47509823|1996-03-02|12:01:00|1997-02-11
17:32:01.0|-23|gjkdfkjglfd|2345552345.5342|null|false
7|7520398475|1997-02-28|23:59:00|1997-02-12
17:32:01.0|P2Y|-0|211.2|798276340.9|true
8|null|null|23:59:59.990|1997-02-13
17:32:01.0|null|lkjgfiurtoUYFHfahui|9823.435|98723.879|false
9|509238475|1997-03-01|15:36:39|null|P1DT9000S|IOUfiuodsfIUfjkh|null|98273.455|true
a|4345234|1997-03-02|15:36:39|1997-02-15
17:32:01.0|P10DT9000S|iweuoHUIhUwer|425.43|4756389.345|false
null|980347589|2000-04-01|00:01:00|1997-02-16
17:32:01.0|P1DT9045.100S|KfijUIwre|3433.3|87364.5|true
12|859702345|2000-04-03|null|1897-02-18
17:32:01.0|P1DT9045S|dfgoiuert|34980.43|78346.3452|false
13|57784356923|2038-04-08|23:59:00|2002-02-14
17:32:01.0|P2Y|uitreo|3425.543|765234.43|null
14|302|2039-04-09|12:03:00|1991-02-10
17:32:01.0|P1Y2M|uigoMnvjjkdf|3654.43|763478.5623|false
15|2983475089|2040-04-10|null|1900-02-16
17:32:01.0|P10DT9000S|NvvdfHVG|1997-02-11 17:32:01.0|8767985.43|true
16|null|null|19:59:00|null|P18582D|null|4784.5735|2037598.58734|null
null|3750982435|1999-02-08|11:59:59.990|1897-02-16
17:32:01.0|P1DT9045S|Nhkhuivb|6459.592|6538.7832|kjsahfd
18|4582345|1999-03-08|null|1997-02-16
17:32:01.0|null|uiuikjk|null|68791189.43|null
null|982|1999-01-18|15:36:39|null|P1DT9045.100S|null|645.2321|87623.34|true
20|7893759083|dsa|11:59:59.990|1996-02-28
17:32:01.0|P1DT9000S|hjiwgh|345.543|false|false
null|null|null|null|null|null|null|null|null|null
|||||||||
{code}
onlynulls.tbl :
{code}
null|null|null|null|null|null|null|null|null|null
|||||||||
{code}
> Incorrect result when we use coalesce in a join condition along with other
> filters
> ----------------------------------------------------------------------------------
>
> Key: DRILL-4096
> URL: https://issues.apache.org/jira/browse/DRILL-4096
> Project: Apache Drill
> Issue Type: Bug
> Components: Functions - Drill, Storage - Hive
> Affects Versions: 1.3.0
> Reporter: Rahul Challapalli
> Priority: Critical
>
> git.commit.id.abbrev=447f8ba
> The below query returns no results which is wrong based on the data set.
> Interestingly if we remove the second filter we get a cannot plan exception
> from drill. Will raise a different jira, if I cannot find an existing one
> {code}
> select * from hive.null_schemachange d, hive.onlynulls n where d.date_col =
> coalesce(n.date_col, date '2038-04-10', n.date_col) and d.date_col >
> '2015-01-01';
> {code}
> Hive DDL :
> {code}
> drop table if exists null_schemachange;
> create external table null_schemachange (
> int_col int,
> bigint_col bigint,
> date_col date,
> time_col string,
> timestamp_col timestamp,
> interval_col string,
> varchar_col string,
> float_col float,
> double_col double,
> bool_col boolean
> )
> ROW FORMAT DELIMITED FIELDS TERMINATED BY "|"
> LOCATION '/drill/testdata/hive_storage/null_schemachange.tbl'
> TBLPROPERTIES ("serialization.null.format"="null");
> drop table if exists onlynulls;
> create external table onlynulls (
> int_col int,
> bigint_col bigint,
> date_col date,
> time_col string,
> timestamp_col timestamp,
> interval_col string,
> varchar_col string,
> float_col float,
> double_col double,
> bool_col boolean
> )
> ROW FORMAT DELIMITED FIELDS TERMINATED BY "|"
> LOCATION '/drill/testdata/hive_storage/onlynulls.tbl'
> TBLPROPERTIES ("serialization.null.format"="null");
> {code}
> The data files are attached
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
