Bug: subquery in partition by clause return wrong result

[xudong963]

Summary

mysql> insert into t1 values(1), (2), (2), (3);
Query OK, 4 rows affected (0.38 sec)

mysql> insert into t2 values(1), (2), (3);
Query OK, 3 rows affected (0.25 sec)

mysql> select count(distinct a) over (partition by (select b from t2)) from t1;
+-----------+
| count(_1) |
+-----------+
|         3 |
+-----------+
1 row in set (0.09 sec)
Read 4 rows, 17.00 B in 0.048 sec., 83.91 rows/sec., 356.61 B/sec.

[xudong963]

pg: error: more than one row returned by a subquery used as an expression

mysql: error: ER_SUBQUERY_NO_1_ROW: Subquery returns more than 1 row

sqlite:

4
4
4
4

[xudong963]

Which behavior should we follow? Open to discuss.

[TCeason]

I think pg is better. It's more clear.

[Dousir9]

Snowflake: