Khurram Faraaz created DRILL-3417: ------------------------------------- Summary: 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: Jinfeng Ni Priority: Critical
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)