[
https://issues.apache.org/jira/browse/DRILL-3417?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14606727#comment-14606727
]
Steven Phillips commented on DRILL-3417:
----------------------------------------
This particular filter would not filter out any of the values, nor prune any of
the files. The current partition pruning logic does not choose the pruned plan
if no files were pruned from the scan, even though the filter could be removed.
I am not sure why this logic is in place, but it is not new. It has been this
way since partition pruning was first introduced.
> Filter present in query plan when we should not see one
> --------------------------------------------------------
>
> Key: DRILL-3417
> URL: https://issues.apache.org/jira/browse/DRILL-3417
> Project: Apache Drill
> Issue Type: Bug
> Components: Query Planning & Optimization
> Affects Versions: 1.1.0
> Environment: 4 node cluster CentOS
> Reporter: Khurram Faraaz
> Assignee: Steven Phillips
> Fix For: 1.2.0
>
>
> We are seeing a FILTER in the query plan, in this case we should not see one.
> {code}
> 0: jdbc:drill:schema=dfs.tmp> CREATE TABLE CTAS_ONE_MILN_RWS_PER_GROUP(col1,
> col2) PARTITION BY (col2) AS select cast(columns[0] as bigint) col1,
> cast(columns[1] as char(2)) col2 from `millionValGroup.csv`;
> +-----------+----------------------------+
> | Fragment | Number of records written |
> +-----------+----------------------------+
> | 1_1 | 21932064 |
> | 1_0 | 28067936 |
> +-----------+----------------------------+
> 2 rows selected (73.661 seconds)
> {code}
> Total number of rows in CTAS output
> {code}
> 0: jdbc:drill:schema=dfs.tmp> select count(*) from
> CTAS_ONE_MILN_RWS_PER_GROUP;
> +-----------+
> | EXPR$0 |
> +-----------+
> | 50000000 |
> +-----------+
> 1 row selected (0.197 seconds)
> {code}
> {code}
> explain plan for select col1, col2 from CTAS_ONE_MILN_RWS_PER_GROUP where
> col2 >= 'AK';
> | 00-00 Screen
> 00-01 Project(col1=[$0], col2=[$1])
> 00-02 UnionExchange
> 01-01 Project(col1=[$1], col2=[$0])
> 01-02 SelectionVectorRemover
> 01-03 Filter(condition=[>=($0, 'AK')])
> 01-04 Project(col2=[$1], col1=[$0])
> 01-05 Scan(groupscan=[ParquetGroupScan
> [entries=[ReadEntryWithPath
> [path=maprfs:///tmp/CTAS_ONE_MILN_RWS_PER_GROUP]],
> selectionRoot=/tmp/CTAS_ONE_MILN_RWS_PER_GROUP, numFiles=1, columns=[`col2`,
> `col1`]]])
> {code}
> Number of files created by CTAS
> {code}
> [root@centos-01 ~]# hadoop fs -ls /tmp/CTAS_ONE_MILN_RWS_PER_GROUP
> Found 98 items
> -rwxr-xr-x 3 mapr mapr 2907957 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_1.parquet
> -rwxr-xr-x 3 mapr mapr 2902189 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_10.parquet
> -rwxr-xr-x 3 mapr mapr 2910365 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_11.parquet
> -rwxr-xr-x 3 mapr mapr 2906479 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_12.parquet
> -rwxr-xr-x 3 mapr mapr 2900842 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_13.parquet
> -rwxr-xr-x 3 mapr mapr 2901196 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_14.parquet
> -rwxr-xr-x 3 mapr mapr 2909687 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_15.parquet
> -rwxr-xr-x 3 mapr mapr 2908603 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_16.parquet
> -rwxr-xr-x 3 mapr mapr 2903334 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_17.parquet
> -rwxr-xr-x 3 mapr mapr 2906378 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_18.parquet
> -rwxr-xr-x 3 mapr mapr 2904710 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_19.parquet
> -rwxr-xr-x 3 mapr mapr 2903170 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_2.parquet
> -rwxr-xr-x 3 mapr mapr 2908703 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_20.parquet
> -rwxr-xr-x 3 mapr mapr 2903634 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_21.parquet
> -rwxr-xr-x 3 mapr mapr 2898076 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_22.parquet
> -rwxr-xr-x 3 mapr mapr 2899426 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_23.parquet
> -rwxr-xr-x 3 mapr mapr 2903914 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_24.parquet
> -rwxr-xr-x 3 mapr mapr 2906561 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_25.parquet
> -rwxr-xr-x 3 mapr mapr 2899655 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_26.parquet
> -rwxr-xr-x 3 mapr mapr 2902479 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_27.parquet
> -rwxr-xr-x 3 mapr mapr 2905985 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_28.parquet
> -rwxr-xr-x 3 mapr mapr 2901645 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_29.parquet
> -rwxr-xr-x 3 mapr mapr 2901653 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_3.parquet
> -rwxr-xr-x 3 mapr mapr 2903008 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_30.parquet
> -rwxr-xr-x 3 mapr mapr 2898135 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_31.parquet
> -rwxr-xr-x 3 mapr mapr 2908631 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_32.parquet
> -rwxr-xr-x 3 mapr mapr 2903475 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_33.parquet
> -rwxr-xr-x 3 mapr mapr 2902909 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_34.parquet
> -rwxr-xr-x 3 mapr mapr 2901894 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_35.parquet
> -rwxr-xr-x 3 mapr mapr 2904224 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_36.parquet
> -rwxr-xr-x 3 mapr mapr 2909076 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_37.parquet
> -rwxr-xr-x 3 mapr mapr 2904889 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_38.parquet
> -rwxr-xr-x 3 mapr mapr 2913608 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_39.parquet
> -rwxr-xr-x 3 mapr mapr 2913936 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_4.parquet
> -rwxr-xr-x 3 mapr mapr 2906174 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_40.parquet
> -rwxr-xr-x 3 mapr mapr 2904253 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_41.parquet
> -rwxr-xr-x 3 mapr mapr 2903476 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_42.parquet
> -rwxr-xr-x 3 mapr mapr 2900126 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_43.parquet
> -rwxr-xr-x 3 mapr mapr 2905705 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_44.parquet
> -rwxr-xr-x 3 mapr mapr 2902369 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_45.parquet
> -rwxr-xr-x 3 mapr mapr 2903906 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_46.parquet
> -rwxr-xr-x 3 mapr mapr 2909380 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_47.parquet
> -rwxr-xr-x 3 mapr mapr 2903031 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_48.parquet
> -rwxr-xr-x 3 mapr mapr 2904777 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_49.parquet
> -rwxr-xr-x 3 mapr mapr 2899192 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_5.parquet
> -rwxr-xr-x 3 mapr mapr 2907455 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_6.parquet
> -rwxr-xr-x 3 mapr mapr 2906999 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_7.parquet
> -rwxr-xr-x 3 mapr mapr 2904151 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_8.parquet
> -rwxr-xr-x 3 mapr mapr 2905863 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_0_9.parquet
> -rwxr-xr-x 3 mapr mapr 2270312 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_1.parquet
> -rwxr-xr-x 3 mapr mapr 2270599 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_10.parquet
> -rwxr-xr-x 3 mapr mapr 2269672 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_11.parquet
> -rwxr-xr-x 3 mapr mapr 2274815 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_12.parquet
> -rwxr-xr-x 3 mapr mapr 2267282 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_13.parquet
> -rwxr-xr-x 3 mapr mapr 2266010 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_14.parquet
> -rwxr-xr-x 3 mapr mapr 2271821 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_15.parquet
> -rwxr-xr-x 3 mapr mapr 2270785 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_16.parquet
> -rwxr-xr-x 3 mapr mapr 2265995 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_17.parquet
> -rwxr-xr-x 3 mapr mapr 2271190 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_18.parquet
> -rwxr-xr-x 3 mapr mapr 2269116 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_19.parquet
> -rwxr-xr-x 3 mapr mapr 2268704 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_2.parquet
> -rwxr-xr-x 3 mapr mapr 2267580 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_20.parquet
> -rwxr-xr-x 3 mapr mapr 2265678 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_21.parquet
> -rwxr-xr-x 3 mapr mapr 2273167 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_22.parquet
> -rwxr-xr-x 3 mapr mapr 2274213 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_23.parquet
> -rwxr-xr-x 3 mapr mapr 2266500 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_24.parquet
> -rwxr-xr-x 3 mapr mapr 2266566 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_25.parquet
> -rwxr-xr-x 3 mapr mapr 2272232 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_26.parquet
> -rwxr-xr-x 3 mapr mapr 2270939 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_27.parquet
> -rwxr-xr-x 3 mapr mapr 2270231 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_28.parquet
> -rwxr-xr-x 3 mapr mapr 2264174 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_29.parquet
> -rwxr-xr-x 3 mapr mapr 2269219 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_3.parquet
> -rwxr-xr-x 3 mapr mapr 2270494 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_30.parquet
> -rwxr-xr-x 3 mapr mapr 2270156 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_31.parquet
> -rwxr-xr-x 3 mapr mapr 2268924 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_32.parquet
> -rwxr-xr-x 3 mapr mapr 2267579 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_33.parquet
> -rwxr-xr-x 3 mapr mapr 2272149 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_34.parquet
> -rwxr-xr-x 3 mapr mapr 2270060 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_35.parquet
> -rwxr-xr-x 3 mapr mapr 2271747 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_36.parquet
> -rwxr-xr-x 3 mapr mapr 2268924 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_37.parquet
> -rwxr-xr-x 3 mapr mapr 2273458 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_38.parquet
> -rwxr-xr-x 3 mapr mapr 2272753 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_39.parquet
> -rwxr-xr-x 3 mapr mapr 2272782 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_4.parquet
> -rwxr-xr-x 3 mapr mapr 2272757 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_40.parquet
> -rwxr-xr-x 3 mapr mapr 2271248 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_41.parquet
> -rwxr-xr-x 3 mapr mapr 2270459 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_42.parquet
> -rwxr-xr-x 3 mapr mapr 2268137 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_43.parquet
> -rwxr-xr-x 3 mapr mapr 2271262 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_44.parquet
> -rwxr-xr-x 3 mapr mapr 2265678 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_45.parquet
> -rwxr-xr-x 3 mapr mapr 2267805 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_46.parquet
> -rwxr-xr-x 3 mapr mapr 2270113 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_47.parquet
> -rwxr-xr-x 3 mapr mapr 2269154 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_48.parquet
> -rwxr-xr-x 3 mapr mapr 2269094 2015-06-29 17:56
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_49.parquet
> -rwxr-xr-x 3 mapr mapr 2267380 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_5.parquet
> -rwxr-xr-x 3 mapr mapr 2265674 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_6.parquet
> -rwxr-xr-x 3 mapr mapr 2275239 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_7.parquet
> -rwxr-xr-x 3 mapr mapr 2269091 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_8.parquet
> -rwxr-xr-x 3 mapr mapr 2266302 2015-06-29 17:55
> /tmp/CTAS_ONE_MILN_RWS_PER_GROUP/1_1_9.parquet
> [root@centos-01 ~]# hadoop fs -ls /tmp/CTAS_ONE_MILN_RWS_PER_GROUP | wc -l
> 99
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
