Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-3417

Filter present in query plan when we should not see one

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 1.1.0
    • Future
    • Query Planning & Optimization
    • None

    • 4 node cluster CentOS

    Description

      We are seeing a FILTER in the query plan, in this case we should not see one.

      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)

      Total number of rows in CTAS output

      0: jdbc:drill:schema=dfs.tmp> select count(*) from CTAS_ONE_MILN_RWS_PER_GROUP;
+-----------+
|  EXPR$0   |
+-----------+
| 50000000  |
+-----------+
1 row selected (0.197 seconds)

      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`]]])

      Number of files created by CTAS

      [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

      Attachments

        Activity

          People

            Unassigned Unassigned
            khfaraaz Khurram Faraaz
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated: