[CALCITE-4451] Group by is always case sensitive， but in mysql some Collate like *_ci is case insensitive

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: core
• Assignee: Unassigned
• Reporter: super486
• Created: 31/Dec/20 02:28
• Updated: 04/Jan/21 06:26

Description

CREATE TABLE `a` (
`id` int(11) DEFAULT NULL,
`a` int(11) DEFAULT NULL,
`c` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `b` (
`id` int(11) DEFAULT NULL,
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into a values(1,1,'a'),(2,2,'B'),(3,3,'A'),(4,4,'b');

insert into b values(1,1),(2,2),(3,3),(4,4);

in Calcite:

mysql> select concat(a.c,1) from a join b on a.a=b.a group by a.c;
---------------

concat(a.c,1)

---------------

A1

a1

B1

b1

---------------
4 rows in set (0.13 sec)

in Mysql:

mysql> select collation(a.c) from a limit 1;
-----------------

collation(a.c)

-----------------

utf8_general_ci

-----------------
1 row in set (0.00 sec)

mysql> select concat(a.c,1) from a join b on a.a=b.a group by a.c;
---------------

concat(a.c,1)

---------------

a1

B1

---------------
2 rows in set (0.00 sec)