[转帖]tidb关闭sql_mode=ONLY_FULL_GROUP_BY模式

tidb,关闭,sql,mode,only,full,group,by,模式 · 浏览次数 : 0

小编点评

**错误原因:** 1. `ORDER BY` clause中的 `column_name` 不是 `SELECT` 中的列名。 2. `DISTINCT` 运算符只能使用在 `SELECT` 中的列名。 **解决方法:** 1. 在 `SELECT` 语句中列出所有需要排序的列名。 2. 将 `DISTINCT` 运算符应用于 `SELECT` 语句的 `column_name`。 **修改后的 SQL 语句:** ```sql SELECT distinct sub_order_no, item_snapshot_id FROM tab_od_item toi WHERE toi.sub_order_no in ('111', '222') ORDER BY id; ``` **注意:** * `DISTINCT` 只能用于 `SELECT` 语句中的列名。 * `column_name` 在 `SELECT` 语句中必须在 `ORDER BY` clause中使用。

正文

报错:

1 of ORDER BY clause is not in SELECT list, references column 'xxx' which is not in SELECT list

最近发现有个sql

执行报错:
xpression #1 of ORDER BY clause is not in SELECT list, references column ‘database.table.column’ which is not in SELECT list; this is incompatible with DISTINCT

以下是程序执行的原sql:

select	distinct sub_order_no ,	item_snapshot_idfrom	tab_od_item toiwhere	toi.sub_order_no in('111','222')	order by id
复制

查阅资料发现在mysql5.7.5(Tidb的mysql模式)及以上版本实现了对功能依赖的检测。默认启用了ONLY_FULL_GROUP_BY SQL模式。在该模式下:

1、当使用GROUP BY查询时,出现在SELECT字段后面的只能是GROUP BY后面的分组字段,或使用聚合函数包裹着的字段,否则会报错如下信息:Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.table.column' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by


2、当使用ORDER BY查询时,不能使用SELECT DISTINCT去重查询。否则会报错如下信息:Expression #1 of ORDER BY clause is not in SELECT list, references column 'database.table.column' which is not in SELECT list; this is incompatible with DISTINCT

解决办法

关闭sql_mode=ONLY_FULL_GROUP_BY模式即可

1、查看是否开启了ONLY_FULL_GROUP_BY规则校验,在数据库中执行如下sql

SELECT VERSION(), @@sql_mode;
复制

2、关闭ONLY_FULL_GROUP_BY的规则校验,关闭规则校验需要执行如下sql。以下两个sql单引号中的内容是第一步查询的结果去掉“ONLY_FULL_GROUP_BY,”之后的值

SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
复制

 

文章知识点与官方知识档案匹配,可进一步学习相关知识

与[转帖]tidb关闭sql_mode=ONLY_FULL_GROUP_BY模式相似的内容:

[转帖]tidb关闭sql_mode=ONLY_FULL_GROUP_BY模式

报错: 1 of ORDER BY clause is not in SELECT list, references column 'xxx' which is not in SELECT list 最近发现有个sql 执行报错: xpression #1 of ORDER BY clause is

[转帖]关系模型到 Key-Value 模型的映射

https://cn.pingcap.com/blog/tidb-internal-2 在这我们将关系模型简单理解为 Table 和 SQL 语句,那么问题变为如何在 KV 结构上保存 Table 以及如何在 KV 结构上运行 SQL 语句。 假设我们有这样一个表的定义: CREATE TABLE

[转帖]7.5 TiKV 磁盘空间占用与回收常见问题

https://book.tidb.io/session4/chapter7/compact.html TiKV 作为 TiDB 的存储节点,用户通过 SQL 导入或更改的所有数据都存储在 TiKV。这里整理了一些关于 TiKV 空间占用的常见问题 TiKV 的空间放大 监控上显示的 Number

[转帖]tidb-系统内核调优及对比

一、背景 验证系统调优对性能的影响,用sysbench做了一些简单的测试,具体调整方法可见官方文档 二、特殊说明 1.透明大页查看 # 查看透明大页是否开启,[]在always处表示开启,[]在never处表示关闭 cat /sys/kernel/mm/transparent_hugepage/en

[转帖]TiUP 常见运维操作

https://docs.pingcap.com/zh/tidb/stable/maintain-tidb-using-tiup 本文介绍了使用 TiUP 运维 TiDB 集群的常见操作,包括查看集群列表、启动集群、查看集群状态、修改配置参数、关闭集群、销毁集群等。 查看集群列表 TiUP clus

[转帖]TiUP Cluster 命令合集

https://docs.pingcap.com/zh/tidb/stable/tiup-component-cluster TiUP Cluster 是 TiUP 提供的使用 Golang 编写的集群管理组件,通过 TiUP Cluster 组件就可以进行日常的运维工作,包括部署、启动、关闭、销毁

[转帖]TIDB_HOT_REGIONS

https://docs.pingcap.com/zh/tidb/stable/information-schema-tidb-hot-regions TIDB_HOT_REGIONS 表提供了关于当前热点 Region 的相关信息。历史热点信息可以在 TIDB_HOT_REGIONS_HISTOR

[转帖]浅析TiDB二阶段提交

https://cloud.tencent.com/developer/article/1608073 关键内容说明: TiDB 对于每个事务,会涉及改动的所有key中,选择出一个作为当前事务的Primary Key,其他的则为Secondary keys。 当Primary Key提交成功,标识整

[转帖]TiFlash 简介

overview TiFlash 是 TiDB HTAP 形态的关键组件,它是 TiKV 的列存扩展,在提供了良好的隔离性的同时,也兼顾了强一致性。列存副本通过 Raft Learner 协议异步复制,但是在读取的时候通过 Raft 校对索引配合 MVCC 的方式获得 Snapshot Isolat

[转帖]黄东旭:TiDB的优势是什么?

https://tidb.net/book/tidb-monthly/2022/2022-07/usercase/tidb-advantage “云原生”、“分布式”,近几年云原生概念热度不减,成为了许多开发人员关注的焦点。在 CNCF 云原生基金会数据库领域,PingCAP 已毕业的项目 TiKV