[bug] New planner uses lower case alias name

[sundy-li]

Summary

:) select 1 as A, 2 as B ;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+

[leiysky]

cc @sundy-li @BohuTANG

I think we need a convention of the display name.

Here is the difference between different databases.

Postgres

In postgres, every unquoted identifier will be displayed as lower case string:

leiysky=# create table t (a int);
CREATE TABLE
leiysky=# insert into t values(1);
INSERT 0 1
leiysky=# select * from t;
 a
---
 1
(1 row)

leiysky=# select a as B from t;
 b
---
 1
(1 row)

And it will keep the original string for the quoted indentifier(which is part of SQL standard):

leiysky=# select a as "   BaB   aaa" from t;
    BaB   aaa
--------------
            1
(1 row)

For anoymous columns(e.g. scalar expressions select target without alias), it will display ?column?:

leiysky=# select a + 1 from t;
 ?column?
----------
        2
(1 row)

But for functions, it will display the function name, and it also allows to reference the functions with its name:

leiysky=# select sum(a), pow(1, 2) from t;
 sum | pow
-----+-----
   1 |   1
(1 row)

leiysky=# select sum, pow from (select sum(a), pow(1, 2) from t) as t1;
 sum | pow
-----+-----
   1 |   1
(1 row)

MySQL

In MySQL, things get much simpler. It seems MySQL just use the original string of the identifier(or expression) as it's display name. And the interesting thing is, it allows to reference the expressions with its name:

mysql> create table t(a int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select a as AbA from t;
+------+
| AbA  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select `cOUnt( *)` from (select cOUnt( *) from t) as t;
+-----------+
| cOUnt( *) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

Snowflake

Snowflake is much more simpler, it will display all the unquoted column in upper-case. And for the expressions with witespace inside, it will keep the whitespaces:

select pow(1, 2);

"POW(1, 2)"
1

My suggestion

For columns, there are three kinds situations:

• select a from t, unquoted identifier
• select "A" from t, quoted identifier
• select a as "A" from t, quoted alias

In SQL standard, a quoted identifier is case-sensitive, and the unquoted identifier aren't needed to be case-sensitive. So if we are tending to access a column bound with quoted identifier, we have to use a quoted identifier to reference it.

Therefore it's more reasonable to display quoted identifier in original case, and display other case-insensitive identifiers in lower-case or upper-case(I prefer lower-case because it's easy to type with keyboard):

select a, a as B, a as "C", "D";

a, b, C, D

For expressions, we can display the original string to help user to address the expression in SQL easier. For example:

select a + \n\n 1, coUNt(*  ) from t group by a+1;

a + \n\n 1, coUNt(*  )

[sundy-li]

For unquoted identifiers, what I worry about is the compatibility with the MySQL ecosystem.

Some client drivers or JDBC work fine with MySQL, but it has to be taken good care of if we use the lower case alias.

Such as JDBC API had a method which get the value from column name :

String name= rs.getString("A");

[leiysky]

We can provide some variables to control the style of display name in result set just as other databases did.

[leiysky]

IMO, the correct way is to define a reasonable convention of databend, and then try to provide compatibility solution for other ecosystems based on the convention.