You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Yuming Wang (Jira)" <ji...@apache.org> on 2019/10/15 05:34:00 UTC
[jira] [Commented] (SPARK-29451) Some queries with divisions in SQL
windows are failling in Thrift
[ https://issues.apache.org/jira/browse/SPARK-29451?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16951622#comment-16951622 ]
Yuming Wang commented on SPARK-29451:
-------------------------------------
It works for me.
{code:sql}
CREATE OR REPLACE TEMPORARY VIEW tenk1
(unique1 int, unique2 int, two int, four int, ten int, twenty int, hundred int,
thousand int, twothousand int, fivethous int, tenthous int, odd int, even int,
stringu1 string, stringu2 string, string4 string)
USING csv
OPTIONS (path '/Users/yumwang/spark/SPARK-28216/sql/core/src/test/resources/test-data/postgresql/tenk.data',
header 'false', delimiter '\t');
SELECT four, ten/4 as two,
sum(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row),
last(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row)
FROM (select distinct ten, four from tenk1) ss;
SELECT four, ten/4 as two,
sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row),
last(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row)
FROM (select distinct ten, four from tenk1) ss;
{code}
{noformat}
LM-SHC-16502798:SPARK-28216 yumwang$ bin/beeline -u jdbc:hive2://localhost:10000
log4j:WARN No appenders could be found for logger (org.apache.hadoop.util.Shell).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
Connecting to jdbc:hive2://localhost:10000
Connected to: Spark SQL (version 3.0.0-SNAPSHOT)
Driver: Hive JDBC (version 2.3.6)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 2.3.6 by Apache Hive
0: jdbc:hive2://localhost:10000> CREATE OR REPLACE TEMPORARY VIEW tenk1
. . . . . . . . . . . . . . . .> (unique1 int, unique2 int, two int, four int, ten int, twenty int, hundred int,
. . . . . . . . . . . . . . . .> thousand int, twothousand int, fivethous int, tenthous int, odd int, even int,
. . . . . . . . . . . . . . . .> stringu1 string, stringu2 string, string4 string)
. . . . . . . . . . . . . . . .> USING csv
. . . . . . . . . . . . . . . .> OPTIONS (path '/Users/yumwang/spark/SPARK-28216/sql/core/src/test/resources/test-data/postgresql/tenk.data',
. . . . . . . . . . . . . . . .> header 'false', delimiter '\t');
+---------+
| Result |
+---------+
+---------+
No rows selected (0.499 seconds)
0: jdbc:hive2://localhost:10000> SELECT four, ten/4 as two,
. . . . . . . . . . . . . . . .> sum(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row),
. . . . . . . . . . . . . . . .> last(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row)
. . . . . . . . . . . . . . . .> FROM (select distinct ten, four from tenk1) ss;
+-------+-------+----------------------------------------------------+----------------------------------------------------+
| four | two | sum((CAST(ten AS DOUBLE) / CAST(4 AS DOUBLE))) OVER (PARTITION BY four ORDER BY (CAST(ten AS DOUBLE) / CAST(4 AS DOUBLE)) ASC NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) | last((CAST(ten AS DOUBLE) / CAST(4 AS DOUBLE)), false) OVER (PARTITION BY four ORDER BY (CAST(ten AS DOUBLE) / CAST(4 AS DOUBLE)) ASC NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) |
+-------+-------+----------------------------------------------------+----------------------------------------------------+
| 1 | 0.25 | 0.25 | 0.25 |
| 1 | 0.75 | 1.0 | 0.75 |
| 1 | 1.25 | 2.25 | 1.25 |
| 1 | 1.75 | 4.0 | 1.75 |
| 1 | 2.25 | 6.25 | 2.25 |
| 3 | 0.25 | 0.25 | 0.25 |
| 3 | 0.75 | 1.0 | 0.75 |
| 3 | 1.25 | 2.25 | 1.25 |
| 3 | 1.75 | 4.0 | 1.75 |
| 3 | 2.25 | 6.25 | 2.25 |
| 2 | 0.0 | 0.0 | 0.0 |
| 2 | 0.5 | 0.5 | 0.5 |
| 2 | 1.0 | 1.5 | 1.0 |
| 2 | 1.5 | 3.0 | 1.5 |
| 2 | 2.0 | 5.0 | 2.0 |
| 0 | 0.0 | 0.0 | 0.0 |
| 0 | 0.5 | 0.5 | 0.5 |
| 0 | 1.0 | 1.5 | 1.0 |
| 0 | 1.5 | 3.0 | 1.5 |
| 0 | 2.0 | 5.0 | 2.0 |
+-------+-------+----------------------------------------------------+----------------------------------------------------+
20 rows selected (4.651 seconds)
0: jdbc:hive2://localhost:10000> SELECT four, ten/4 as two,
. . . . . . . . . . . . . . . .> sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row),
. . . . . . . . . . . . . . . .> last(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row)
. . . . . . . . . . . . . . . .> FROM (select distinct ten, four from tenk1) ss;
+-------+-------+----------------------------------------------------+----------------------------------------------------+
| four | two | sum((CAST(ten AS DOUBLE) / CAST(4 AS DOUBLE))) OVER (PARTITION BY four ORDER BY (CAST(ten AS DOUBLE) / CAST(4 AS DOUBLE)) ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) | last((CAST(ten AS DOUBLE) / CAST(4 AS DOUBLE)), false) OVER (PARTITION BY four ORDER BY (CAST(ten AS DOUBLE) / CAST(4 AS DOUBLE)) ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) |
+-------+-------+----------------------------------------------------+----------------------------------------------------+
| 1 | 0.25 | 0.25 | 0.25 |
| 1 | 0.75 | 1.0 | 0.75 |
| 1 | 1.25 | 2.25 | 1.25 |
| 1 | 1.75 | 4.0 | 1.75 |
| 1 | 2.25 | 6.25 | 2.25 |
| 3 | 0.25 | 0.25 | 0.25 |
| 3 | 0.75 | 1.0 | 0.75 |
| 3 | 1.25 | 2.25 | 1.25 |
| 3 | 1.75 | 4.0 | 1.75 |
| 3 | 2.25 | 6.25 | 2.25 |
| 2 | 0.0 | 0.0 | 0.0 |
| 2 | 0.5 | 0.5 | 0.5 |
| 2 | 1.0 | 1.5 | 1.0 |
| 2 | 1.5 | 3.0 | 1.5 |
| 2 | 2.0 | 5.0 | 2.0 |
| 0 | 0.0 | 0.0 | 0.0 |
| 0 | 0.5 | 0.5 | 0.5 |
| 0 | 1.0 | 1.5 | 1.0 |
| 0 | 1.5 | 3.0 | 1.5 |
| 0 | 2.0 | 5.0 | 2.0 |
+-------+-------+----------------------------------------------------+----------------------------------------------------+
20 rows selected (1.433 seconds)
0: jdbc:hive2://localhost:10000>
{noformat}
> Some queries with divisions in SQL windows are failling in Thrift
> -----------------------------------------------------------------
>
> Key: SPARK-29451
> URL: https://issues.apache.org/jira/browse/SPARK-29451
> Project: Spark
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 3.0.0
> Reporter: Dylan Guedes
> Priority: Major
>
> Hello,
> the following queries are not properly working on Thrift. The only difference between them and some other queries that works fine are the numeric divisions, I think.
> {code:sql}
> SELECT four, ten/4 as two,
> sum(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row),
> last(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row)
> FROM (select distinct ten, four from tenk1) ss;
> {code}
> {code:sql}
> SELECT four, ten/4 as two,
> sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row),
> last(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row)
> FROM (select distinct ten, four from tenk1) ss;
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org