TiDB-3.0.5 group by COALESCE(user_name)

🪐 TiDB 技术问题 性能调优
1
  • 【TiDB 版本】：3.0.5
  • 【问题描述】：select t.user_name from t group by COALESCE(user_name) 报错： 1054 - Unknown column ‘user_name’ in ‘group statement’ 时间: 0.001s
2

2.1.8是没问题的，3.0.5发现无法执行

3

请提供一下 sql_mode

4

sql_mode STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

5

我试了一下，没有报错，请检查一下 t 表的 user_name 字段是否存在

6

CREATE TABLE test01 ( id bigint(20) NOT NULL AUTO_INCREMENT, stat_date int(11) NOT NULL DEFAULT ‘0’, show_date varchar(20) NOT NULL DEFAULT ‘’, region_id bigint(20) unsigned NOT NULL DEFAULT ‘0’, period tinyint(3) unsigned NOT NULL DEFAULT ‘0’, registration_num bigint(20) unsigned NOT NULL DEFAULT ‘0’, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; CREATE TABLE test02 ( id bigint(20) NOT NULL AUTO_INCREMENT, region_name varchar(128) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

7

SELECT COUNT(1) FROM (SELECT COALESCE(b.region_name, ‘不详’) region_name, SUM(a.registration_num) registration_num FROM (SELECT stat_date, show_date, region_id, 0 registration_num FROM test01 WHERE period = 1 AND stat_date >= 20191202 AND stat_date <= 20191202 UNION ALL SELECT stat_date, show_date, region_id, registration_num registration_num FROM test01 WHERE period = 1 AND stat_date >= 20191202 AND stat_date <= 20191202) a LEFT JOIN test02 b ON a.region_id = b.id WHERE registration_num > 0 AND a.stat_date >= ‘20191202’ AND a.stat_date <= ‘20191202’ GROUP BY a.stat_date , a.show_date , COALESCE(b.region_name, ‘不详’) ) JLS;

8

试下这个SQL

9

你好： 在mysql测试了也报错，请先查看下sql

mysql> CREATE TABLE test01 ( id bigint(20) NOT NULL AUTO_INCREMENT, stat_date int(11) NOT NULL DEFAULT ‘0’, show_date varchar(20) NOT NULL DEFAULT ‘’, region_id bigint(20) unsigned NOT NULL DEFAULT ‘0’, period tinyint(3) unsigned NOT NULL DEFAULT ‘0’, registration_num bigint(20) unsigned NOT NULL DEFAULT ‘0’, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; CREATE TABLE test02 ( id bigint(20) NOT NULL AUTO_INCREMENT, region_name varchar(128) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘0’, show_date varchar(20) NOT NULL DEFAULT ‘’, region_id bigint(20) uns’ at line 1 Query OK, 0 rows affected (0.04 sec)

mysql> SELECT COUNT(1) FROM (SELECT COALESCE(b.region_name, ‘不详’) region_name, SUM(a.registration_num) registration_num FROM (SELECT stat_date, show_date, region_id, 0 registration_num FROM test01 WHERE period = 1 AND stat_date >= 20191202 AND stat_date <= 20191202 UNION ALL SELECT stat_date, show_date, region_id, registration_num registration_num FROM test01 WHERE period = 1 AND stat_date >= 20191202 AND stat_date <= 20191202) a LEFT JOIN test02 b ON a.region_id = b.id WHERE registration_num > 0 AND a.stat_date >= ‘20191202’ AND a.stat_date <= ‘20191202’ GROUP BY a.stat_date , a.show_date , COALESCE(b.region_name, ‘不详’) ) JLS; ERROR 1146 (42S02): Table ‘test.test01’ doesn’t exist

10

ceshi.sql (1.5 KB)

用这个附件，SQL直接提交之后，你们的文本编辑器把格式损坏了

11

好的，我测试了一下，在mysql可以成功，tidb有问题，稍后会答复，多谢

12

您好： 这个问题当前发现，可能是outer join 消除的问题。 可以使用以下方法绕过当前问题： insert into mysql.opt_rule_blacklist values(“outer_join_eliminate”); admin reload opt_rule_blacklist; 关掉会影响其他 SQL ，查询优化的时候都不会用这条规则了， 要恢复默认就把 mysql.opt_rule_blacklist 里的行删掉，再执行 admin reload opt_rule_blacklist; 之后的版本会修复这个问题，多谢

13

好的。

14

:+1:

15

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。