[
https://issues.apache.org/jira/browse/DRILL-2153?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14304115#comment-14304115
]
Sudhakar Thota commented on DRILL-2153:
---------------------------------------
[root@maprdemo clicks]# cat sthota_test_1.json
{"a":"r1cl1", "b":"r1c2","c":{ "x" : 1, "y" : "a string", "z" : [ 1] }}
{"a":"r2cl1", "b":"r2c2","c":{ "x" : 2, "y" : "a string", "z" : [ 1,2] }}
{"a":"r3cl1", "b":"r3c2","c":{ "x" : 3, "y" : "a string", "z" : [ 1,2,3] }}
{"a":"r4cl1", "b":"r4c2","c":{ "x" : 4, "y" : "a string", "z" : [ 1,2,3,4] }}
{"a":"r5cl1", "b":"r5c2","c":{ "x" : 5, "y" : "a string", "z" : [ 1,2,3,4,5] }}
{"a":"r6cl1", "b":"r6c2","c":{ "x" : 6, "y" : "a string" }}
Listing-1 is contents of my json collection where I have intentionally included
a empty array.
Listing-2 shows that if I don’t call flatten function it shows all records.
Listing-3 shows that if I call flatten function, row6 (r6cl1…) is ignored. I am
using that in my projections not in predicate, but it looks like treated as
predicate.
Please correct me if I am wrong and let me know if there is any work around.
Listing:1
0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, t.c.z from
dfs.`/data/nested/clicks/sthota_test_1.json` as t;
+------------+------------+------------+------------+------------+
| a | b | EXPR$2 | EXPR$3 | EXPR$4 |
+------------+------------+------------+------------+------------+
| r1cl1 | r1c2 | 1 | a string | ["1"] |
| r2cl1 | r2c2 | 2 | a string | ["1","2"] |
| r3cl1 | r3c2 | 3 | a string | ["1","2","3"] |
| r4cl1 | r4c2 | 4 | a string | ["1","2","3","4"] |
| r5cl1 | r5c2 | 5 | a string | ["1","2","3","4","5"] |
| r6cl1 | r6c2 | 6 | a string | [] |
+------------+------------+------------+------------+------------+
6 rows selected (0.125 seconds)
0: jdbc:drill:>
Listing:2
0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y from
dfs.`/data/nested/clicks/sthota_test_1.json` as t;
+------------+------------+------------+------------+
| a | b | EXPR$2 | EXPR$3 |
+------------+------------+------------+------------+
| r1cl1 | r1c2 | 1 | a string |
| r2cl1 | r2c2 | 2 | a string |
| r3cl1 | r3c2 | 3 | a string |
| r4cl1 | r4c2 | 4 | a string |
| r5cl1 | r5c2 | 5 | a string |
| r6cl1 | r6c2 | 6 | a string |
+------------+------------+------------+------------+
6 rows selected (0.163 seconds)
Listing:3
0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, flatten(t.c.z) from
dfs.`/data/nested/clicks/sthota_test_1.json` as t;
+------------+------------+------------+------------+------------+
| a | b | EXPR$2 | EXPR$3 | EXPR$4 |
+------------+------------+------------+------------+------------+
| r1cl1 | r1c2 | 1 | a string | 1 |
| r2cl1 | r2c2 | 2 | a string | 1 |
| r2cl1 | r2c2 | 2 | a string | 2 |
| r3cl1 | r3c2 | 3 | a string | 1 |
| r3cl1 | r3c2 | 3 | a string | 2 |
| r3cl1 | r3c2 | 3 | a string | 3 |
| r4cl1 | r4c2 | 4 | a string | 1 |
| r4cl1 | r4c2 | 4 | a string | 2 |
| r4cl1 | r4c2 | 4 | a string | 3 |
| r4cl1 | r4c2 | 4 | a string | 4 |
| r5cl1 | r5c2 | 5 | a string | 1 |
| r5cl1 | r5c2 | 5 | a string | 2 |
| r5cl1 | r5c2 | 5 | a string | 3 |
| r5cl1 | r5c2 | 5 | a string | 4 |
| r5cl1 | r5c2 | 5 | a string | 5 |
+------------+------------+------------+------------+------------+
15 rows selected (0.171 seconds)
> flatten function not handling nulls
> -----------------------------------
>
> Key: DRILL-2153
> URL: https://issues.apache.org/jira/browse/DRILL-2153
> Project: Apache Drill
> Issue Type: Bug
> Components: Functions - Drill
> Affects Versions: 0.7.0
> Environment: Sandbox 4.0.2
> Reporter: Sudhakar Thota
> Assignee: Daniel Barclay (Drill/MapR)
>
> Function flatten not handling nulls resulting in eliminating relevant records
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
