[DRILL-4474] Inconsistent behavior while using COUNT in select (Apache drill 1.2.0) - ASF JIRA

XML

Word

Printable

JSON

Details

Type: Bug
Status: Closed
Priority: Blocker
Resolution: Fixed
Affects Version/s: 1.2.0, 1.5.0
Fix Version/s: 1.6.0
Component/s: None
Labels:
None
Environment:

m3.xlarge AWS instances ( 3 nodes)
CentOS6.5 x64

Description

We are using drill to retrieve the business data from game analytic.

We are running below queries on table of size 50GB (parquet)

We have found some major inconsistency in data when we use COUNT function.

Below is the case by case queries and their output. *Please analyse it carefully, to for clear understanding of behaviour. *

Please let me know how to resolve this ? (or any earlier JIRA has been already created).

Hope this may be fixed in later versions. If not please do the needful.

------------------------------------------------------------------------------------------------------------------------------------------------------
CASE-1 (Wrong result)
------------------------------------------------------------------------------------------------------------------------------------------------------

0: jdbc:drill:> select  
. . . . . . . > count(case when t.id = '/confirmDrop/btnYes/' and t.event = 'Click' then sessionid end) as cnt
. . . . . . . > from dfs.tmp.a_games_log_visit_base t
. . . . . . . > ; 
+-----------+
|   count   |
+-----------+
| 27645752  |
+-----------+
1 row selected (0.281 seconds)

------------------------------------------------------------------------------------------------------------------------------------------------------
CASE-2 (Wrong result)
------------------------------------------------------------------------------------------------------------------------------------------------------

0: jdbc:drill:> select  
. . . . . . . > count(sessionid), 
. . . . . . . > count(case when t.id = '/confirmDrop/btnYes/' and t.event = 'Click' then sessionid end) as cnt
. . . . . . . > from dfs.tmp.a_games_log_visit_base t
. . . . . . . > ; 
+-----------+-------+
|  EXPR$0   |  cnt  |
+-----------+-------+
| 37772844  | 2108  |
+-----------+-------+
1 row selected (12.597 seconds)

------------------------------------------------------------------------------------------------------------------------------------------------------
CASE-3 (Wrong result, only first count is correct)
------------------------------------------------------------------------------------------------------------------------------------------------------

0: jdbc:drill:> select  
. . . . . . . > count(distinct sessionid), 
. . . . . . . > count(case when t.id = '/confirmDrop/btnYes/' and t.event = 'Click' then sessionid end) as cnt
. . . . . . . > from dfs.tmp.a_games_log_visit_base t
. . . . . . . > ; 
+---------+-----------+
| EXPR$0  |    cnt    |
+---------+-----------+
| 201941  | 37772844  |
+---------+-----------+
1 row selected (8.259 seconds)

------------------------------------------------------------------------------------------------------------------------------------------------------
CASE-4 (Correct result)
------------------------------------------------------------------------------------------------------------------------------------------------------

0: jdbc:drill:> select  
. . . . . . . > count(distinct case when t.id = '/confirmDrop/btnYes/' and t.event = 'Click' then sessionid end) as cnt
. . . . . . . > from dfs.tmp.a_games_log_visit_base t
. . . . . . . > ; 
+------+
| cnt  |
+------+
| 525  |
+------+
1 row selected (14.318 seconds)

------------------------------------------------------------------------------------------------------------------------------------------------------
CASE-5 (Correct result)
------------------------------------------------------------------------------------------------------------------------------------------------------

0: jdbc:drill:> select  
. . . . . . . > count(sessionid),
. . . . . . . > count(distinct sessionid)
. . . . . . . > from dfs.tmp.a_games_log_visit_base t
. . . . . . . > where ( t.id = '/confirmDrop/btnYes/' and t.event = 'Click')
. . . . . . . > ;
+---------+---------+
| EXPR$0  | EXPR$1  |
+---------+---------+
| 2108    | 525     |
+---------+---------+
1 row selected (19.355 seconds)

------------------------------------------------------------------------------------------------------------------------------------------------------
CASE-6
------------------------------------------------------------------------------------------------------------------------------------------------------

0: jdbc:drill:> explain plan for 
. . . . . . . > 
. . . . . . . > select  
. . . . . . . > count(case when t.id = '/confirmDrop/btnYes/' and t.event = 'Click' then sessionid end) as cnt
. . . . . . . > from dfs.tmp.a_games_log_visit_base t
. . . . . . . > ; 
+------+------+
| text | json |
+------+------+
| 00-00    Screen
00-01      Scan(groupscan=[org.apache.drill.exec.store.pojo.PojoRecordReader@73ff10e1])
 | {
  "head" : {
    "version" : 1,
    "generator" : {
      "type" : "ExplainHandler",
      "info" : ""
    },
    "type" : "APACHE_DRILL_PHYSICAL",
    "options" : [ ],
    "queue" : 0,
    "resultMode" : "EXEC"
  },
  "graph" : [ {
    "pop" : "DirectGroupScan",
    "@id" : 1,
    "cost" : 20.0
  }, {
    "pop" : "screen",
    "@id" : 0,
    "child" : 1,
    "initialAllocation" : 1000000,
    "maxAllocation" : 10000000000,
    "cost" : 20.0
  } ]
} |
+------+------+
1 row selected (0.276 seconds)

------------------------------------------------------------------------------------------------------------------------------------------------------
CASE-7
------------------------------------------------------------------------------------------------------------------------------------------------------


0: jdbc:drill:> 
0: jdbc:drill:> explain plan for 
. . . . . . . > 
. . . . . . . > select  
. . . . . . . > count(distinct sessionid), 
. . . . . . . > count(case when t.id = '/confirmDrop/btnYes/' and t.event = 'Click' then sessionid end) as cnt
. . . . . . . > from dfs.tmp.a_games_log_visit_base t
. . . . . . . > ; 
+------+------+
| text | json |
+------+------+
| 00-00    Screen
00-01      Project(EXPR$0=[$0], cnt=[$1])
00-02        Project(EXPR$0=[$1], cnt=[$0])
00-03          NestedLoopJoin(condition=[true], joinType=[inner])
00-05            Scan(groupscan=[org.apache.drill.exec.store.pojo.PojoRecordReader@59b7cda9])
00-04            StreamAgg(group=[{}], EXPR$0=[$SUM0($0)])
00-06              UnionExchange
01-01                StreamAgg(group=[{}], EXPR$0=[COUNT($0)])
01-02                  HashAgg(group=[{0}])
01-03                    Project(sessionid=[$0])
01-04                      HashToRandomExchange(dist0=[[$0]])
02-01                        UnorderedMuxExchange
03-01                          Project(sessionid=[$0], E_X_P_R_H_A_S_H_F_I_E_L_D=[castInt(hash64AsDouble($0))])
03-02                            HashAgg(group=[{0}])
03-03                              Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=hdfs://namenode:9000/tmp/a_games_log_visit_base]], selectionRoot=hdfs://namenode:9000/tmp/a_games_log_visit_base, numFiles=1, usedMetadataFile=false, columns=[`sessionid`]]])
 | {
  "head" : {
    "version" : 1,
    "generator" : {
      "type" : "ExplainHandler",
      "info" : ""
    },
    "type" : "APACHE_DRILL_PHYSICAL",
    "options" : [ ],
    "queue" : 0,
    "resultMode" : "EXEC"
  },
  "graph" : [ {
    "pop" : "DirectGroupScan",
    "@id" : 5,
    "cost" : 20.0
  }, {
    "pop" : "parquet-scan",
    "@id" : 196611,
    "userName" : "hadoop",
    "entries" : [ {
      "path" : "hdfs://namenode:9000/tmp/a_games_log_visit_base"
    } ],
    "storage" : {
      "type" : "file",
      "enabled" : true,
      "connection" : "hdfs://namenode:9000",
      "workspaces" : {
        "root" : {
          "location" : "/tmp/",
          "writable" : true,
          "defaultInputFormat" : null
        },
        "tmp" : {
          "location" : "/tmp",
          "writable" : true,
          "defaultInputFormat" : null
        }
      },
      "formats" : {
        "psv" : {
          "type" : "text",
          "extensions" : [ "tbl" ],
          "delimiter" : "|"
        },
        "csv" : {
          "type" : "text",
          "extensions" : [ "csv" ],
          "delimiter" : ","
        },
        "tsv" : {
          "type" : "text",
          "extensions" : [ "tsv" ],
          "delimiter" : "\t"
        },
        "parquet" : {
          "type" : "parquet"
        },
        "json" : {
          "type" : "json"
        },
        "avro" : {
          "type" : "avro"
        }
      }
    },
    "format" : {
      "type" : "parquet"
    },
    "columns" : [ "`sessionid`" ],
    "selectionRoot" : "hdfs://namenode:9000/tmp/a_games_log_visit_base",
    "fileSet" : [ "/tmp/a_games_log_visit_base/1_6_15.parquet", "/tmp/a_games_log_visit_base/1_1_9.parquet", "/tmp/a_games_log_visit_base/1_7_2.parquet", "/tmp/a_games_log_visit_base/1_5_7.parquet", "/tmp/a_games_log_visit_base/1_2_1.parquet", "/tmp/a_games_log_visit_base/1_4_23.parquet", "/tmp/a_games_log_visit_base/1_8_22.parquet", "/tmp/a_games_log_visit_base/1_3_11.parquet", "/tmp/a_games_log_visit_base/1_6_30.parquet", "/tmp/a_games_log_visit_base/1_7_10.parquet", "/tmp/a_games_log_visit_base/1_1_10.parquet", "/tmp/a_games_log_visit_base/1_0_24.parquet", "/tmp/a_games_log_visit_base/1_8_39.parquet", "/tmp/a_games_log_visit_base/1_2_16.parquet", "/tmp/a_games_log_visit_base/1_7_27.parquet", "/tmp/a_games_log_visit_base/1_3_4.parquet", "/tmp/a_games_log_visit_base/1_8_5.parquet", "/tmp/a_games_log_visit_base/1_8_13.parquet", "/tmp/a_games_log_visit_base/1_4_7.parquet", "/tmp/a_games_log_visit_base/1_5_12.parquet", "/tmp/a_games_log_visit_base/1_0_9.parquet", "/tmp/a_games_log_visit_base/1_4_14.parquet", "/tmp/a_games_log_visit_base/1_2_13.parquet", "/tmp/a_games_log_visit_base/1_0_15.parquet", "/tmp/a_games_log_visit_base/1_2_4.parquet", "/tmp/a_games_log_visit_base/1_6_24.parquet", "/tmp/a_games_log_visit_base/1_7_5.parquet", "/tmp/a_games_log_visit_base/1_6_2.parquet", "/tmp/a_games_log_visit_base/1_1_13.parquet", "/tmp/a_games_log_visit_base/1_1_1.parquet", "/tmp/a_games_log_visit_base/1_3_7.parquet", "/tmp/a_games_log_visit_base/1_0_12.parquet", "/tmp/a_games_log_visit_base/1_0_3.parquet", "/tmp/a_games_log_visit_base/1_0_29.parquet", "/tmp/a_games_log_visit_base/1_1_24.parquet", "/tmp/a_games_log_visit_base/1_6_18.parquet", "/tmp/a_games_log_visit_base/1_2_19.parquet", "/tmp/a_games_log_visit_base/1_3_16.parquet", "/tmp/a_games_log_visit_base/1_6_27.parquet", "/tmp/a_games_log_visit_base/1_4_11.parquet", "/tmp/a_games_log_visit_base/1_7_15.parquet", "/tmp/a_games_log_visit_base/1_5_2.parquet", "/tmp/a_games_log_visit_base/1_8_10.parquet", "/tmp/a_games_log_visit_base/1_6_7.parquet", "/tmp/a_games_log_visit_base/1_8_2.parquet", "/tmp/a_games_log_visit_base/1_2_9.parquet", "/tmp/a_games_log_visit_base/1_7_18.parquet", "/tmp/a_games_log_visit_base/1_3_20.parquet", "/tmp/a_games_log_visit_base/1_7_31.parquet", "/tmp/a_games_log_visit_base/1_1_16.parquet", "/tmp/a_games_log_visit_base/1_6_35.parquet", "/tmp/a_games_log_visit_base/1_1_4.parquet", "/tmp/a_games_log_visit_base/1_4_19.parquet", "/tmp/a_games_log_visit_base/1_0_6.parquet", "/tmp/a_games_log_visit_base/1_8_18.parquet", "/tmp/a_games_log_visit_base/1_7_8.parquet", "/tmp/a_games_log_visit_base/1_5_15.parquet", "/tmp/a_games_log_visit_base/1_4_20.parquet", "/tmp/a_games_log_visit_base/1_0_21.parquet", "/tmp/a_games_log_visit_base/1_8_31.parquet", "/tmp/a_games_log_visit_base/1_4_4.parquet", "/tmp/a_games_log_visit_base/1_3_19.parquet", "/tmp/a_games_log_visit_base/1_1_21.parquet", "/tmp/a_games_log_visit_base/1_2_18.parquet", "/tmp/a_games_log_visit_base/1_8_20.parquet", "/tmp/a_games_log_visit_base/1_8_37.parquet", "/tmp/a_games_log_visit_base/1_0_13.parquet", "/tmp/a_games_log_visit_base/1_0_4.parquet", "/tmp/a_games_log_visit_base/1_6_13.parquet", "/tmp/a_games_log_visit_base/1_0_22.parquet", "/tmp/a_games_log_visit_base/1_1_19.parquet", "/tmp/a_games_log_visit_base/1_3_2.parquet", "/tmp/a_games_log_visit_base/1_7_29.parquet", "/tmp/a_games_log_visit_base/1_5_1.parquet", "/tmp/a_games_log_visit_base/1_8_11.parquet", "/tmp/a_games_log_visit_base/1_4_25.parquet", "/tmp/a_games_log_visit_base/1_6_32.parquet", "/tmp/a_games_log_visit_base/1_7_12.parquet", "/tmp/a_games_log_visit_base/1_7_0.parquet", "/tmp/a_games_log_visit_base/1_6_4.parquet", "/tmp/a_games_log_visit_base/1_6_22.parquet", "/tmp/a_games_log_visit_base/1_4_5.parquet", "/tmp/a_games_log_visit_base/1_8_3.parquet", "/tmp/a_games_log_visit_base/1_1_11.parquet", "/tmp/a_games_log_visit_base/1_1_3.parquet", "/tmp/a_games_log_visit_base/1_8_34.parquet", "/tmp/a_games_log_visit_base/1_2_6.parquet", "/tmp/a_games_log_visit_base/1_5_14.parquet", "/tmp/a_games_log_visit_base/1_0_7.parquet", "/tmp/a_games_log_visit_base/1_4_16.parquet", "/tmp/a_games_log_visit_base/1_6_10.parquet", "/tmp/a_games_log_visit_base/1_7_3.parquet", "/tmp/a_games_log_visit_base/1_0_27.parquet", "/tmp/a_games_log_visit_base/1_0_1.parquet", "/tmp/a_games_log_visit_base/1_0_10.parquet", "/tmp/a_games_log_visit_base/1_5_4.parquet", "/tmp/a_games_log_visit_base/1_8_19.parquet", "/tmp/a_games_log_visit_base/1_4_22.parquet", "/tmp/a_games_log_visit_base/1_7_24.parquet", "/tmp/a_games_log_visit_base/1_8_8.parquet", "/tmp/a_games_log_visit_base/1_8_25.parquet", "/tmp/a_games_log_visit_base/1_6_9.parquet", "/tmp/a_games_log_visit_base/1_5_17.parquet", "/tmp/a_games_log_visit_base/1_3_5.parquet", "/tmp/a_games_log_visit_base/1_7_30.parquet", "/tmp/a_games_log_visit_base/1_3_14.parquet", "/tmp/a_games_log_visit_base/1_1_6.parquet", "/tmp/a_games_log_visit_base/1_6_16.parquet", "/tmp/a_games_log_visit_base/1_2_0.parquet", "/tmp/a_games_log_visit_base/1_5_21.parquet", "/tmp/a_games_log_visit_base/1_1_14.parquet", "/tmp/a_games_log_visit_base/1_7_33.parquet", "/tmp/a_games_log_visit_base/1_8_28.parquet", "/tmp/a_games_log_visit_base/1_0_18.parquet", "/tmp/a_games_log_visit_base/1_4_13.parquet", "/tmp/a_games_log_visit_base/1_7_21.parquet", "/tmp/a_games_log_visit_base/1_2_3.parquet", "/tmp/a_games_log_visit_base/1_3_17.parquet", "/tmp/a_games_log_visit_base/1_4_2.parquet", "/tmp/a_games_log_visit_base/1_8_16.parquet", "/tmp/a_games_log_visit_base/1_6_25.parquet", "/tmp/a_games_log_visit_base/1_8_0.parquet", "/tmp/a_games_log_visit_base/1_2_10.parquet", "/tmp/a_games_log_visit_base/1_6_1.parquet", "/tmp/a_games_log_visit_base/1_7_6.parquet", "/tmp/a_games_log_visit_base/1_3_8.parquet", "/tmp/a_games_log_visit_base/1_8_9.parquet", "/tmp/a_games_log_visit_base/1_3_15.parquet", "/tmp/a_games_log_visit_base/1_7_23.parquet", "/tmp/a_games_log_visit_base/1_6_28.parquet", "/tmp/a_games_log_visit_base/1_4_0.parquet", "/tmp/a_games_log_visit_base/1_4_27.parquet", "/tmp/a_games_log_visit_base/1_8_35.parquet", "/tmp/a_games_log_visit_base/1_6_19.parquet", "/tmp/a_games_log_visit_base/1_0_28.parquet", "/tmp/a_games_log_visit_base/1_5_16.parquet", "/tmp/a_games_log_visit_base/1_7_14.parquet", "/tmp/a_games_log_visit_base/1_5_22.parquet", "/tmp/a_games_log_visit_base/1_8_26.parquet", "/tmp/a_games_log_visit_base/1_6_34.parquet", "/tmp/a_games_log_visit_base/1_1_5.parquet", "/tmp/a_games_log_visit_base/1_1_23.parquet", "/tmp/a_games_log_visit_base/1_6_6.parquet", "/tmp/a_games_log_visit_base/1_5_3.parquet", "/tmp/a_games_log_visit_base/1_0_11.parquet", "/tmp/a_games_log_visit_base/1_0_2.parquet", "/tmp/a_games_log_visit_base/1_4_10.parquet", "/tmp/a_games_log_visit_base/1_2_8.parquet", "/tmp/a_games_log_visit_base/1_0_19.parquet", "/tmp/a_games_log_visit_base/1_7_32.parquet", "/tmp/a_games_log_visit_base/1_8_29.parquet", "/tmp/a_games_log_visit_base/1_8_1.parquet", "/tmp/a_games_log_visit_base/1_6_20.parquet", "/tmp/a_games_log_visit_base/1_7_17.parquet", "/tmp/a_games_log_visit_base/1_3_0.parquet", "/tmp/a_games_log_visit_base/1_1_17.parquet", "/tmp/a_games_log_visit_base/1_4_18.parquet", "/tmp/a_games_log_visit_base/1_7_9.parquet", "/tmp/a_games_log_visit_base/1_8_32.parquet", "/tmp/a_games_log_visit_base/1_7_20.parquet", "/tmp/a_ |
+------+------+
1 row selected (0.503 seconds)

Attachments

Activity

People

Assignee:: Jacques Nadeau

Reporter:: Shankar

Reviewer:: Khurram Faraaz

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 04/Mar/16 19:24

Updated:: 24/Aug/16 04:45

Resolved:: 14/Mar/16 17:23