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

[LI-ldc]

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]]

[LI-ldc]

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

[北京大爷]

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

[CuteRay (Cherry🍒)]

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

[LI-ldc]

@LI-ldc

ws_type

[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
\t\t\tCASE WHEN b.sub_type in (0,3) THEN 1\r
\t\t\t\t\tWHEN b.sub_type = 1 THEN 2\r
\t\t\t\t\tWHEN b.sub_type in (2,4) THEN 3\r
\t\t\t\t\tWHEN b.sub_type = 5 THEN 4\r
\t\t\t\t\tELSE 0\r
\t\t\t\t\tEND as store_type \r
\t\tfrom ti_xxx__order.ts_yyy_order o\r
\t\t\t\tINNER JOIN ti_xxx__order.ts_yyy_order_detail d on o.id = d.order_id\r
\t\t\t\tINNER JOIN ti_xxx_dictionary.ts_drugstore_branch b on o.drugstore_branch_id = b.id\r
\t\t\t\tWHERE o.id in (\r
\tSELECT DISTINCT so.id from ti_xxx__order.ts_yyy_order so\r
\t\t\tINNER JOIN ti_xxx__order.ts_yyy_order_detail sd on sd.order_id = so.id\r
\t\t\tWHERE so.add_time >= UNIX_TIMESTAMP(DATE_SUB(‘2021-09-30’, INTERVAL 30 DAY))\r
\t\t\t\t\tAND so.add_time < UNIX_TIMESTAMP(‘2021-12-01 00:00:00’)\r
\t\t\t\t\tAND sd.ws_type = 7\r
) 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
\t\t\tCASE WHEN b.sub_type in (0,3) THEN 1\r
\t\t\t\t\tWHEN b.sub_type = 1 THEN 2\r
\t\t\t\t\tWHEN b.sub_type in (2,4) THEN 3\r
\t\t\t\t\tWHEN b.sub_type = 5 THEN 4\r
\t\t\t\t\tELSE 0\r
\t\t\t\t\tEND as store_type \r
\t\tfrom ti_xxx__order.ts_yyy_order o\r
\t\t\t\tINNER JOIN ti_xxx__order.ts_yyy_order_detail d on o.id = d.order_id\r
\t\t\t\tINNER JOIN ti_xxx_dictionary.ts_drugstore_branch b on o.drugstore_branch_id = b.id\r
\t\t\t\tWHERE o.id in (\r
\tSELECT DISTINCT so.id from ti_xxx__order.ts_yyy_order so\r
\t\t\tINNER JOIN ti_xxx__order.ts_yyy_order_detail sd on sd.order_id = so.id\r
\t\t\tWHERE so.add_time >= UNIX_TIMESTAMP(DATE_SUB(‘2021-09-30’, INTERVAL 30 DAY))\r
\t\t\t\t\tAND so.add_time < UNIX_TIMESTAMP(‘2021-12-01 00:00:00’)\r
\t\t\t\t\tAND sd.ws_type = 7\r
) 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]]
github.com/pingcap/tidb/expression.(*Column).resolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/expression/column.go:475
github.com/pingcap/tidb/expression.(*ScalarFunction).resolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/expression/scalar_function.go:439
github.com/pingcap/tidb/expression.(*ScalarFunction).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/expression/scalar_function.go:433
github.com/pingcap/tidb/planner/core.(*PhysicalProjection).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:28
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638
github.com/pingcap/tidb/planner/core.(*PhysicalExchangeSender).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:357
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638
github.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:627
github.com/pingcap/tidb/planner/core.(*PhysicalHashJoin).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:76
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638
github.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:627
github.com/pingcap/tidb/planner/core.(*PhysicalProjection).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:23
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638
github.com/pingcap/tidb/planner/core.(*PhysicalExchangeSender).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:357
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638
github.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:627
github.com/pingcap/tidb/planner/core.(*PhysicalHashJoin).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:76
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638
github.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:627
github.com/pingcap/tidb/planner/core.(*PhysicalProjection).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:23
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638
github.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:627
github.com/pingcap/tidb/planner/core.(*basePhysicalAgg).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:373
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638
github.com/pingcap/tidb/planner/core.(*PhysicalExchangeSender).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:357
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:638
github.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:627
github.com/pingcap/tidb/planner/core.(*basePhysicalAgg).ResolveIndices
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:373
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices
\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]

[QBin (Bin)]

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

[LI-ldc]

message.zip (12.3 KB)

[LI-ldc]

请问有找到问题所在吗

[Cong-PingCAP]

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

[QBin (Bin)]

• 根据表结构以及 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#导出统计信息

[LI-ldc]

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