[DRILL-2012] Flatten fails when we filter by a non-projected column

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: 0.8.0
• Component/s: Query Planning & Optimization
• Assignee: Jason Altekruse
• Reporter: Rahul Kumar Challapalli
• Created: 15/Jan/15 18:01
• Updated: 22/Apr/15 23:08
• Resolved: 19/Mar/15 03:40

Description

git.commit.id.abbrev=b491cdb

{
    "id": "0001",
    "type": "donut",
    "name": "Cake",
    "ppu": 0.55,
    "batters":
        {
            "batter":
                [
                    { "id": "1001", "type": "Regular" },
                    { "id": "1002", "type": "Chocolate" },
                    { "id": "1003", "type": "Blueberry" },
                    { "id": "1004", "type": "Devil's Food" }
                ]
        },
    "topping":
        [
            { "id": "5001", "type": "None" },
            { "id": "5002", "type": "Glazed" },
            { "id": "5005", "type": "Sugar" },
            { "id": "5007", "type": "Powdered Sugar" },
            { "id": "5006", "type": "Chocolate with Sprinkles" },
            { "id": "5003", "type": "Chocolate" },
            { "id": "5004", "type": "Maple" }
        ]
}

The below query fails :

select j.id id, j.name name, flatten(j.topping) tt, flatten(j.batters.batter) bb from `sample.json` j where j.type = 'donut';
Query failed: Query failed: Failure while running fragment., Trying to flatten a non-repeated filed. [ 98ff5db0-4aa7-4f65-9a7a-93ae8bc41f6b on qa-node190.qa.lab:31010 ]
[ 98ff5db0-4aa7-4f65-9a7a-93ae8bc41f6b on qa-node190.qa.lab:31010 ]

However if I include the filter column in the list of selected columns, then the query succeeds.

select j.type, j.id id, j.name name, flatten(j.topping) tt, flatten(j.batters.batter) bb from `user.json` j where j.type = 'donut';
+------------+------------+------------+------------+------------+
|    type    |     id     |    name    |     tt     |     bb     |
+------------+------------+------------+------------+------------+
| donut      | 0001       | Cake       | {"id":"5001","type":"None"} | {"id":"1001","type":"Regular"} |
| donut      | 0001       | Cake       | {"id":"5001","type":"None"} | {"id":"1002","type":"Chocolate"} |
| donut      | 0001       | Cake       | {"id":"5001","type":"None"} | {"id":"1003","type":"Blueberry"} |
| donut      | 0001       | Cake       | {"id":"5001","type":"None"} | {"id":"1004","type":"Devil's Food"} |
| donut      | 0001       | Cake       | {"id":"5002","type":"Glazed"} | {"id":"1001","type":"Regular"} |
| donut      | 0001       | Cake       | {"id":"5002","type":"Glazed"} | {"id":"1002","type":"Chocolate"} |
| donut      | 0001       | Cake       | {"id":"5002","type":"Glazed"} | {"id":"1003","type":"Blueberry"} |
| donut      | 0001       | Cake       | {"id":"5002","type":"Glazed"} | {"id":"1004","type":"Devil's Food"} |
| donut      | 0001       | Cake       | {"id":"5005","type":"Sugar"} | {"id":"1001","type":"Regular"} |
| donut      | 0001       | Cake       | {"id":"5005","type":"Sugar"} | {"id":"1002","type":"Chocolate"} |
| donut      | 0001       | Cake       | {"id":"5005","type":"Sugar"} | {"id":"1003","type":"Blueberry"} |
| donut      | 0001       | Cake       | {"id":"5005","type":"Sugar"} | {"id":"1004","type":"Devil's Food"} |
| donut      | 0001       | Cake       | {"id":"5007","type":"Powdered Sugar"} | {"id":"1001","type":"Regular"} |
| donut      | 0001       | Cake       | {"id":"5007","type":"Powdered Sugar"} | {"id":"1002","type":"Chocolate"} |
| donut      | 0001       | Cake       | {"id":"5007","type":"Powdered Sugar"} | {"id":"1003","type":"Blueberry"} |
| donut      | 0001       | Cake       | {"id":"5007","type":"Powdered Sugar"} | {"id":"1004","type":"Devil's Food"} |
| donut      | 0001       | Cake       | {"id":"5006","type":"Chocolate with Sprinkles"} | {"id":"1001","type":"Regular"} |
| donut      | 0001       | Cake       | {"id":"5006","type":"Chocolate with Sprinkles"} | {"id":"1002","type":"Chocolate"} |
| donut      | 0001       | Cake       | {"id":"5006","type":"Chocolate with Sprinkles"} | {"id":"1003","type":"Blueberry"} |
| donut      | 0001       | Cake       | {"id":"5006","type":"Chocolate with Sprinkles"} | {"id":"1004","type":"Devil's Food"} |
| donut      | 0001       | Cake       | {"id":"5003","type":"Chocolate"} | {"id":"1001","type":"Regular"} |
| donut      | 0001       | Cake       | {"id":"5003","type":"Chocolate"} | {"id":"1002","type":"Chocolate"} |
| donut      | 0001       | Cake       | {"id":"5003","type":"Chocolate"} | {"id":"1003","type":"Blueberry"} |
| donut      | 0001       | Cake       | {"id":"5003","type":"Chocolate"} | {"id":"1004","type":"Devil's Food"} |
| donut      | 0001       | Cake       | {"id":"5004","type":"Maple"} | {"id":"1001","type":"Regular"} |
| donut      | 0001       | Cake       | {"id":"5004","type":"Maple"} | {"id":"1002","type":"Chocolate"} |
| donut      | 0001       | Cake       | {"id":"5004","type":"Maple"} | {"id":"1003","type":"Blueberry"} |
| donut      | 0001       | Cake       | {"id":"5004","type":"Maple"} | {"id":"1004","type":"Devil's Food"} |

I attached the relevant log file.

Issue Links

• depends upon: DRILL-2106 Flatten together with order by on a list within a list is causing an error
• relates to: DRILL-2228 Projecting '*' returns all nulls when we have flatten in a filter and order by
• relates to: DRILL-2208 Error message must be updated when query contains operations on a flattened column