[Err] 1105 - Can't find column ***

tidb:v5.1.1

SELECT DISTINCT
o.id,
o.user_id,
o.branch_id,
d.drug_id,
d.ws_type,
CASE
WHEN b.sub_type IN (0, 3) THEN
1
WHEN b.sub_type = 1 THEN
2
WHEN b.sub_type IN (2, 4) THEN
3
WHEN b.sub_type = 5 THEN
4
ELSE
0
END AS store_type
FROM
ti_xxx_order.ts_yyy_order o
INNER JOIN ti_xxx_order.ts_yyy_order_detail d ON o.id = d.order_id
INNER JOIN ti_xxx_dictionary.ts_yyy_branch b ON o.branch_id = b.id
WHERE
o.id IN (
SELECT DISTINCT
so.id
FROM
ti_xxx_order.ts_yyy_order so
INNER JOIN ti_xxx_order.ts_yyy_order_detail sd ON sd.order_id = so.id
WHERE
so.add_time >= UNIX_TIMESTAMP(
DATE_SUB(‘2021-09-30’, INTERVAL 30 DAY)
)
AND so.add_time < UNIX_TIMESTAMP(‘2021-11-01 00:00:00’)
AND sd.ws_type = 7
)
ORDER BY
o.id;

[Err] 1105 - Can’t find column ti_xxx_order.ts_yyy_order_detail.order_id in schema Column: [ti_xxx_order.ts_yyy_order.id] Unique key: [[ti_xxx_order.ts_yyy_order.id]]

1赞

如果改为10月1号就可以正常运行,大于1号都会报错

1赞

请提供 对应 Table 的 schema 和 explain 信息(能成功执行的 SQL 和不能成功执行的 SQL 的)
以及 执行失败的 TiDB Server 的 log 信息。

1赞

能看看具体的报错日志吗?

1赞

[2021/11/11 15:03:40.277 +08:00] [WARN] [session.go:1515] [“compile SQL failed”] [conn=933] [error=“Can’t find column ti_xxx__order.ts_yyy_order_detail.order_id in schema Column: [ti_xxx__order.ts_yyy_order.id] Unique key: [[ti_xxx__order.ts_yyy_order.id]]”] [SQL=“SELECT DISTINCT o.id, o.user_id, o.drugstore_branch_id, d.drug_id, d.ws_type,\r\n\t\t\tCASE WHEN b.sub_type in (0,3) THEN 1\r\n\t\t\t\t\tWHEN b.sub_type = 1 THEN 2\r\n\t\t\t\t\tWHEN b.sub_type in (2,4) THEN 3\r\n\t\t\t\t\tWHEN b.sub_type = 5 THEN 4\r\n\t\t\t\t\tELSE 0\r\n\t\t\t\t\tEND as store_type \r\n\t\tfrom ti_xxx__order.ts_yyy_order o\r\n\t\t\t\tINNER JOIN ti_xxx__order.ts_yyy_order_detail d on o.id = d.order_id\r\n\t\t\t\tINNER JOIN ti_xxx_dictionary.ts_drugstore_branch b on o.drugstore_branch_id = b.id\r\n\t\t\t\tWHERE o.id in (\r\n\tSELECT DISTINCT so.id from ti_xxx__order.ts_yyy_order so\r\n\t\t\tINNER JOIN ti_xxx__order.ts_yyy_order_detail sd on sd.order_id = so.id\r\n\t\t\tWHERE so.add_time >= UNIX_TIMESTAMP(DATE_SUB(‘2021-09-30’, INTERVAL 30 DAY))\r\n\t\t\t\t\tAND so.add_time < UNIX_TIMESTAMP(‘2021-12-01 00:00:00’)\r\n\t\t\t\t\tAND sd.ws_type = 7\r\n) ORDER BY o.id;”]
[2021/11/11 15:03:40.277 +08:00] [INFO] [conn.go:877] [“command dispatched failed”] [conn=933] [connInfo=“id:933, addr:192.168.1.84:1078 status:10, collation:utf8_general_ci, user:oper”] [command=Query] [status=“inTxn:0, autocommit:1”][sql=“SELECT DISTINCT o.id, o.user_id, o.drugstore_branch_id, d.drug_id, d.ws_type,\r\n\t\t\tCASE WHEN b.sub_type in (0,3) THEN 1\r\n\t\t\t\t\tWHEN b.sub_type = 1 THEN 2\r\n\t\t\t\t\tWHEN b.sub_type in (2,4) THEN 3\r\n\t\t\t\t\tWHEN b.sub_type = 5 THEN 4\r\n\t\t\t\t\tELSE 0\r\n\t\t\t\t\tEND as store_type \r\n\t\tfrom ti_xxx__order.ts_yyy_order o\r\n\t\t\t\tINNER JOIN ti_xxx__order.ts_yyy_order_detail d on o.id = d.order_id\r\n\t\t\t\tINNER JOIN ti_xxx_dictionary.ts_drugstore_branch b on o.drugstore_branch_id = b.id\r\n\t\t\t\tWHERE o.id in (\r\n\tSELECT DISTINCT so.id from ti_xxx__order.ts_yyy_order so\r\n\t\t\tINNER JOIN ti_xxx__order.ts_yyy_order_detail sd on sd.order_id = so.id\r\n\t\t\tWHERE so.add_time >= UNIX_TIMESTAMP(DATE_SUB(‘2021-09-30’, INTERVAL 30 DAY))\r\n\t\t\t\t\tAND so.add_time < UNIX_TIMESTAMP(‘2021-12-01 00:00:00’)\r\n\t\t\t\t\tAND sd.ws_type = 7\r\n) ORDER BY o.id;”] [txn_mode=PESSIMISTIC] [err=“Can’t find column ti_xxx__order.ts_yyy_order_detail.order_id in schema Column: [ti_xxx__order.ts_yyy_order.id] Unique key: [[ti_xxx__order.ts_yyy_order.id]]\ngithub.com/pingcap/tidb/expression.(*Column).resolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/expression/column.go:475\ngithub.com/pingcap/tidb/expression.(*ScalarFunction).resolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/expression/scalar_function.go:439\ngithub.com/pingcap/tidb/expression.(*ScalarFunction).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/expression/scalar_function.go:433\ngithub.com/pingcap/tidb/planner/core.(*PhysicalProjection).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:28\ngithub.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638\ngithub.com/pingcap/tidb/planner/core.(*PhysicalExchangeSender).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:357\ngithub.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638\ngithub.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638\ngithub.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:627\ngithub.com/pingcap/tidb/planner/core.(*PhysicalHashJoin).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:76\ngithub.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638\ngithub.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:627\ngithub.com/pingcap/tidb/planner/core.(*PhysicalProjection).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:23\ngithub.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638\ngithub.com/pingcap/tidb/planner/core.(*PhysicalExchangeSender).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:357\ngithub.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638\ngithub.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638\ngithub.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:627\ngithub.com/pingcap/tidb/planner/core.(*PhysicalHashJoin).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:76\ngithub.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638\ngithub.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:627\ngithub.com/pingcap/tidb/planner/core.(*PhysicalProjection).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:23\ngithub.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638\ngithub.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:627\ngithub.com/pingcap/tidb/planner/core.(*basePhysicalAgg).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:373\ngithub.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638\ngithub.com/pingcap/tidb/planner/core.(*PhysicalExchangeSender).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:357\ngithub.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638\ngithub.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638\ngithub.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:627\ngithub.com/pingcap/tidb/planner/core.(*basePhysicalAgg).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:373\ngithub.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638”]
[2021/11/11 15:03:40.947 +08:00] [WARN] [grpclogger.go:85] [“grpc: Server.Serve failed to create ServerTransport: connection error: desc = “transport: http2Server.HandleStreams failed to receive the preface from client: EOF””] [system=grpc] [grpc_log=true]

请提供 对应 Table 的 schema 和 explain 信息(能成功执行的 SQL 和不能成功执行的 SQL 的)
以及 执行失败的 TiDB Server 的 log 信息。

message.zip (12.3 KB)

请问有找到问题所在吗

您好,您提供的 schema 信息看上去与查询中的不相同,我尝试用附件中的 ‘ts_yyy_order_detail’ 进行复现,但是查询的时候会提示 ’ts_yyy_branch.drug_id’ 列不存在。麻烦看一下这个

  • 根据表结构以及 SQL 有几点问题:
  1. 表结构 ts_yyy_order.sql 的文件内的表名是 ts_xxx_order 不清楚是否同一个表。这边测试的时候表明根据问题 SQL 修改为 ts_yyy_order 了。麻烦确认一下。
  2. 根据问题 SQL 尝试复现,会报 d.drug_id 不存在,如果去掉这个列,能正常查询。没有复现问题。
  3. 另外辛苦也导出统计信息。https://docs.pingcap.com/zh/tidb/stable/statistics#导出统计信息

1、是ts_yyy_order的;
2、我把 d.drug_id注释掉,还是会报Can’t find column…
3、我试着临时关闭mpp,“set @@session.tidb_allow_mpp=0;”,就可以正常出结果了