[GitHub] spark issue #19193: [WIP][SPARK-21896][SQL] Fix Stack Overflow when window f...

[aokolnychyi <gi...@git.apache.org>]

Github user aokolnychyi commented on the issue:

    https://github.com/apache/spark/pull/19193
  
    I checked PostgreSQL(10.3), MySQL(8.0), Hive(2.1.0). 
    
    **1. PostgreSQL**
    
    ```
    postgres=# CREATE TABLE t1 (c1 integer, c2 integer);
    postgres=# INSERT INTO t1 VALUES (1, 2), (1, 3), (2,4), (5,5);
    postgres=# SELECT c1, c2, ROW_NUMBER() OVER() as c3 FROM t1;
     c1 | c2 | c3 
    ----+----+----
      1 |  2 |  1
      1 |  3 |  2
      2 |  4 |  3
      5 |  5 |  4
    (4 rows)
    postgres=# SELECT c1, MAX(ROW_NUMBER() OVER()) as c3 FROM t1;
    ERROR:  aggregate function calls cannot contain window function calls
    LINE 1: SELECT c1, MAX(ROW_NUMBER() OVER()) as c3 FROM t1;
    ```
    
    **2. MySQL**
    
    ```
    mysql> CREATE TABLE t1 (c1 integer, c2 integer);
    mysql> INSERT INTO t1 VALUES (1, 2), (1, 3), (2,4), (5,5);
    mysql> SELECT c1, c2, ROW_NUMBER() OVER() FROM t1;
    +------+------+---------------------+
    | c1   | c2   | ROW_NUMBER() OVER() |
    +------+------+---------------------+
    |    1 |    2 |                   1 |
    |    1 |    3 |                   2 |
    |    2 |    4 |                   3 |
    |    5 |    5 |                   4 |
    +------+------+---------------------+
    4 rows in set (0.00 sec)
    mysql> SELECT c1, MAX(ROW_NUMBER() OVER()) as c3 FROM t1;
    ERROR 3593 (HY000): You cannot use the window function 'row_number' in this context.'
    ```
    
    **3. Hive**
    
    ```
    hive> CREATE TABLE t1(c1 INT, c2 INT);
    hive> INSERT INTO t1 VALUES (1, 2), (1, 3), (2,4), (5,5);
    hive> SELECT c1, c2, ROW_NUMBER() OVER() as c3 FROM t1;
    OK
    5       5       1
    2       4       2
    1       3       3
    1       2       4
    hive> SELECT c1, MAX(ROW_NUMBER() OVER()) as c3 FROM t1;
    FAILED: SemanticException [Error 10002]: Line 1:15 Invalid column reference 'ROW_NUMBER': (possible column names are: c1, c2)
    ```
    
    I will adapt the PR to prohibit window functions inside aggregates.


---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org