drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Khurram Faraaz (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DRILL-4678) Query HANG - SELECT DISTINCT over date data
Date Thu, 09 Mar 2017 06:04:38 GMT

    [ https://issues.apache.org/jira/browse/DRILL-4678?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15286232#comment-15286232
] 

Khurram Faraaz edited comment on DRILL-4678 at 3/9/17 6:04 AM:
---------------------------------------------------------------

Upon reducing the size the query returns results, however if we add one or two more values
the time taken to execute almost doubles.

This query took 16 seconds.
{noformat}
0: jdbc:drill:schema=dfs.tmp> SELECT DISTINCT dt FROM (
. . . . . . . . . . . . . . > VALUES(CAST('1964-03-07' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('2002-03-04' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1966-09-04' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1993-08-18' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1959-10-23' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1992-01-14' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1994-07-24' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1979-11-25' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1945-01-14' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1982-07-25' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1966-09-06' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1989-05-01' AS DATE)),
. . . . . . . . . . . . . . > (CAST('1951-05-16' AS DATE)))
. . . . . . . . . . . . . . > tbl(dt);
+-------------+
|     dt      |
+-------------+
| 1964-03-07  |
| 2002-03-04  |
| 1966-09-04  |
| 1993-08-18  |
| 1970-06-11  |
| 1959-10-23  |
| 1992-01-14  |
| 1994-07-24  |
| 1979-11-25  |
| 1945-01-14  |
| 1982-07-25  |
| 1966-09-06  |
| 1989-05-01  |
| 1951-05-16  |
+-------------+
14 rows selected (16.718 seconds)
{noformat}

I added few more rows enclosed in parentheses and it took 32 seconds (almost twice the time)
{noformat}
0: jdbc:drill:schema=dfs.tmp> SELECT DISTINCT dt FROM (
. . . . . . . . . . . . . . > VALUES(CAST('1964-03-07' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('2002-03-04' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1966-09-04' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1993-08-18' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1959-10-23' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1992-01-14' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1994-07-24' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1979-11-25' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1945-01-14' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1982-07-25' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1966-09-06' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1989-05-01' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1996-03-08' AS DATE)),
. . . . . . . . . . . . . . > (CAST('1951-05-16' AS DATE)))
. . . . . . . . . . . . . . > tbl(dt);
+-------------+
|     dt      |
+-------------+
| 1964-03-07  |
| 2002-03-04  |
| 1966-09-04  |
| 1993-08-18  |
| 1970-06-11  |
| 1959-10-23  |
| 1992-01-14  |
| 1994-07-24  |
| 1979-11-25  |
| 1945-01-14  |
| 1982-07-25  |
| 1966-09-06  |
| 1989-05-01  |
| 1996-03-08  |
| 1951-05-16  |
+-------------+
15 rows selected (32.182 seconds)
{noformat}

Adding any more rows (even adding just one) would keep the query in STARTING state and the
query would never complete.
explain plan over the original query reported in this JIRA would not return any results. So
it looks like it has to do something with the total number of values enclosed in parentheses
inside the VALUES clause.


was (Author: khfaraaz):
Upon reducing the size the query returns results, however if we add one or two more values
the time taken to execute almost doubles.

This query took 16 seconds.
{noormat}
0: jdbc:drill:schema=dfs.tmp> SELECT DISTINCT dt FROM (
. . . . . . . . . . . . . . > VALUES(CAST('1964-03-07' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('2002-03-04' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1966-09-04' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1993-08-18' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1959-10-23' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1992-01-14' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1994-07-24' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1979-11-25' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1945-01-14' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1982-07-25' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1966-09-06' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1989-05-01' AS DATE)),
. . . . . . . . . . . . . . > (CAST('1951-05-16' AS DATE)))
. . . . . . . . . . . . . . > tbl(dt);
+-------------+
|     dt      |
+-------------+
| 1964-03-07  |
| 2002-03-04  |
| 1966-09-04  |
| 1993-08-18  |
| 1970-06-11  |
| 1959-10-23  |
| 1992-01-14  |
| 1994-07-24  |
| 1979-11-25  |
| 1945-01-14  |
| 1982-07-25  |
| 1966-09-06  |
| 1989-05-01  |
| 1951-05-16  |
+-------------+
14 rows selected (16.718 seconds)
{noformat}

I added few more rows enclosed in parentheses and it took 32 seconds (almost twice the time)
{noformat}
0: jdbc:drill:schema=dfs.tmp> SELECT DISTINCT dt FROM (
. . . . . . . . . . . . . . > VALUES(CAST('1964-03-07' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('2002-03-04' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1966-09-04' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1993-08-18' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1970-06-11' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1959-10-23' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1992-01-14' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1994-07-24' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1979-11-25' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1945-01-14' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1982-07-25' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1966-09-06' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1989-05-01' AS DATE)),
. . . . . . . . . . . . . . >       (CAST('1996-03-08' AS DATE)),
. . . . . . . . . . . . . . > (CAST('1951-05-16' AS DATE)))
. . . . . . . . . . . . . . > tbl(dt);
+-------------+
|     dt      |
+-------------+
| 1964-03-07  |
| 2002-03-04  |
| 1966-09-04  |
| 1993-08-18  |
| 1970-06-11  |
| 1959-10-23  |
| 1992-01-14  |
| 1994-07-24  |
| 1979-11-25  |
| 1945-01-14  |
| 1982-07-25  |
| 1966-09-06  |
| 1989-05-01  |
| 1996-03-08  |
| 1951-05-16  |
+-------------+
15 rows selected (32.182 seconds)
{noformat}

Adding any more rows (even adding just one) would keep the query in STARTING state and the
query would never complete.
explain plan over the original query reported in this JIRA would not return any results. So
it looks like it has to do something with the total number of values enclosed in parentheses
inside the VALUES clause.

> Query HANG - SELECT DISTINCT over date data
> -------------------------------------------
>
>                 Key: DRILL-4678
>                 URL: https://issues.apache.org/jira/browse/DRILL-4678
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Query Planning & Optimization
>    Affects Versions: 1.7.0
>         Environment: 4 node cluster CentOS
>            Reporter: Khurram Faraaz
>            Priority: Critical
>         Attachments: hung_Date_Query.log
>
>
> Below query hangs
> {noformat}
> 2016-05-16 10:33:57,506 [28c65de9-9f67-dadb-5e4e-e1a12f8dda49:foreman] INFO  o.a.drill.exec.work.foreman.Foreman
- Query text for query id 28c65de9-9f67-dadb-5e4e-e1a12f8dda49: SELECT DISTINCT dt FROM (
> VALUES(CAST('1964-03-07' AS DATE)),
>       (CAST('2002-03-04' AS DATE)),
>       (CAST('1966-09-04' AS DATE)),
>       (CAST('1993-08-18' AS DATE)),
>       (CAST('1970-06-11' AS DATE)),
>       (CAST('1970-06-11' AS DATE)),
>       (CAST('1970-06-11' AS DATE)),
>       (CAST('1970-06-11' AS DATE)),
>       (CAST('1970-06-11' AS DATE)),
>       (CAST('1959-10-23' AS DATE)),
>       (CAST('1992-01-14' AS DATE)),
>       (CAST('1994-07-24' AS DATE)),
>       (CAST('1979-11-25' AS DATE)),
>       (CAST('1945-01-14' AS DATE)),
>       (CAST('1982-07-25' AS DATE)),
>       (CAST('1966-09-06' AS DATE)),
>       (CAST('1989-05-01' AS DATE)),
>       (CAST('1996-03-08' AS DATE)),
>       (CAST('1998-08-19' AS DATE)),
>       (CAST('2013-08-13' AS DATE)),
>       (CAST('2013-08-13' AS DATE)),
>       (CAST('2013-08-13' AS DATE)),
>       (CAST('2013-08-13' AS DATE)),
>       (CAST('2013-08-13' AS DATE)),
>       (CAST('2013-08-13' AS DATE)),
> (CAST('1999-07-20' AS DATE)),
>     (CAST('1962-07-03' AS DATE)),
>       (CAST('2011-08-17' AS DATE)),
>       (CAST('2011-05-16' AS DATE)),
>       (CAST('1946-05-08' AS DATE)),
>       (CAST('1994-02-13' AS DATE)),
>       (CAST('1978-08-09' AS DATE)),
>       (CAST('1978-08-09' AS DATE)),
>       (CAST('1978-08-09' AS DATE)),
>       (CAST('1978-08-09' AS DATE)),
>       (CAST('1958-02-06' AS DATE)),
>       (CAST('2012-06-11' AS DATE)),
>       (CAST('2012-06-11' AS DATE)),
>       (CAST('2012-06-11' AS DATE)),
>       (CAST('2012-06-11' AS DATE)),
>       (CAST('1998-03-26' AS DATE)),
>       (CAST('1996-11-04' AS DATE)),
>       (CAST('1953-09-25' AS DATE)),
>       (CAST('2003-06-17' AS DATE)),
>       (CAST('2003-06-17' AS DATE)),
>       (CAST('2003-06-17' AS DATE)),
>       (CAST('2003-06-17' AS DATE)),
>       (CAST('2003-06-17' AS DATE)),
>       (CAST('1980-07-05' AS DATE)),
>       (CAST('1982-06-15' AS DATE)),
>       (CAST('1951-05-16' AS DATE)))
> tbl(dt)
> {noformat}
> Details from Web UI Profile tab, please note that the query is still in STARTING state
> {noformat}
> Running Queries
> Time	User	Query	State	Foreman
> 05/16/2016 10:33:57	
> mapr
>  SELECT DISTINCT dt FROM ( VALUES(CAST('1964-03-07' AS DATE)), (CAST('2002-03-04' AS
DATE)), (CAST('1966-09-04' AS DATE)), (CAST('199
> STARTING
> centos-01.qa.lab
> {noformat}
> There is no other useful information in drillbit.log. jstack output is attached here
for your reference.
> The same query works fine on Postgres 9.3



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

Mime
View raw message