databendlabs / databend

๐——๐—ฎ๐˜๐—ฎ, ๐—”๐—ป๐—ฎ๐—น๐˜†๐˜๐—ถ๐—ฐ๐˜€ & ๐—”๐—œ. Modern alternative to Snowflake. Cost-effective and simple for massive-scale analytics. https://databend.com
https://docs.databend.com
Other
7.88k stars 753 forks source link

fix(query): fix group by with alias column can't bind the column #16804

Closed b41sh closed 1 week ago

b41sh commented 2 weeks ago

I hereby agree to the terms of the CLA available at: https://docs.databend.com/dev/policies/cla/

Summary

for example:

MySQL [(none)]> CREATE OR REPLACE TABLE t_str (col1 string, col2 string);
Query OK, 0 rows affected (0.155 sec)

MySQL [(none)]> INSERT INTO t_str VALUES ('test', 'a1,a2'),('test', 'a1,a2,a3');
Query OK, 2 rows affected (0.206 sec)

MySQL [(none)]> SELECT t.col1 AS col1, unnest(split(t.col2, ',')) AS col2 FROM t_str AS t GROUP BY col1, col2 ORDER BY col2;
+------+------+
| col1 | col2 |
+------+------+
| test | a1   |
| test | a1   |
| test | a2   |
| test | a2   |
| test | a3   |
+------+------+
5 rows in set (0.095 sec)

MySQL [(none)]> SELECT t.col1 AS col1, unnest(split(t.col2, ',')) AS col3 FROM t_str AS t GROUP BY col1, col3 ORDER BY col3;
+------+------+
| col1 | col3 |
+------+------+
| test | a1   |
| test | a2   |
| test | a3   |
+------+------+
3 rows in set (0.102 sec)

MySQL [(none)]> SELECT max(unnest(split(t.col2, ','))) FROM t_str AS t;
+---------------------------------+
| max(unnest(split(t.col2, ','))) |
+---------------------------------+
| a3                              |
+---------------------------------+
1 row in set (0.106 sec)

MySQL [(none)]> SELECT unnest(split(max(t.col2), ',')) FROM t_str AS t;
+---------------------------------+
| unnest(split(max(t.col2), ',')) |
+---------------------------------+
| a1                              |
| a2                              |
| a3                              |
+---------------------------------+
3 rows in set (0.089 sec)

fixes: #16797

Tests

Type of change


This change isโ€‚Reviewable