TiDB-3.0.5 group by COALESCE(user_name)

  • 【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.1.8是没问题的,3.0.5发现无法执行

请提供一下 sql_mode

sql_mode STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

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

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;

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;

试下这个SQL

你好: 在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

ceshi.sql (1.5 KB)

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

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

您好: 这个问题当前发现,可能是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; 之后的版本会修复这个问题,多谢