Description:
** As below **
How to repeat:
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t1 ( a LONGTEXT );
Query OK, 0 rows affected (0.10 sec)
mysql> INSERT INTO t1 VALUES ('a'),('a '),('a '),('b'),('a ');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT SUBSTRING(a,1) AS x FROM t1 GROUP BY SUBSTRING(a,1);
+------+
| x |
+------+
| |
+------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING(a,1,1) AS x FROM t1 GROUP BY SUBSTRING(a,1);
+------+
| x |
+------+
| |
+------+
1 row in set (0.00 sec)
Then with text:
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t1 ( a TEXT );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES ('a'),('a '),('a '),('b'),('a ');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT SUBSTRING(a,1) AS x FROM t1 GROUP BY SUBSTRING(a,1);
+------+
| x |
+------+
| a |
| b |
+------+
2 rows in set (0.01 sec)
mysql> SELECT SUBSTRING(a,1,1) AS x FROM t1 GROUP BY SUBSTRING(a,1);
+------+
| x |
+------+
| a |
| b |
+------+
2 rows in set (0.00 sec)
Suggested fix:
Behave as with TEXT.
Description: ** As below ** How to repeat: mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t1 ( a LONGTEXT ); Query OK, 0 rows affected (0.10 sec) mysql> INSERT INTO t1 VALUES ('a'),('a '),('a '),('b'),('a '); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT SUBSTRING(a,1) AS x FROM t1 GROUP BY SUBSTRING(a,1); +------+ | x | +------+ | | +------+ 1 row in set (0.00 sec) mysql> SELECT SUBSTRING(a,1,1) AS x FROM t1 GROUP BY SUBSTRING(a,1); +------+ | x | +------+ | | +------+ 1 row in set (0.00 sec) Then with text: mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t1 ( a TEXT ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t1 VALUES ('a'),('a '),('a '),('b'),('a '); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT SUBSTRING(a,1) AS x FROM t1 GROUP BY SUBSTRING(a,1); +------+ | x | +------+ | a | | b | +------+ 2 rows in set (0.01 sec) mysql> SELECT SUBSTRING(a,1,1) AS x FROM t1 GROUP BY SUBSTRING(a,1); +------+ | x | +------+ | a | | b | +------+ 2 rows in set (0.00 sec) Suggested fix: Behave as with TEXT.