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

["Quan Chao (Jira)" <ji...@apache.org>]

Quan Chao created CALCITE-4451:
----------------------------------

             Summary: Group by is always case sensitive， but in mysql some Collate like *_ci is case insensitive
                 Key: CALCITE-4451
                 URL: https://issues.apache.org/jira/browse/CALCITE-4451
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.26.0, 1.25.0, 1.24.0, 1.23.0
            Reporter: Quan Chao
             Fix For: next


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)

 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)