TiDB 4.0 用到 json 生成列的查询无法执行

tminglei · 2020 年7 月 8 日 04:46

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

【TiDB 版本】：5.7.25-TiDB-v4.0.0
【问题描述】：执行以下SQL的时候，

SELECT date(if(a.confirm_time1=0,a.apply_time,a.confirm_time)) day,
a.sid apply_sid,a.product_cid,a.user_sid,ifnull(b.gjj_sid,g.gjj_sid) gjj_sid,b.shebao_sid,b.bill_sid,
a.apply_time,if(a.confirm_time1=0 and a.apply_time<‘2017-04-01’,a.apply_time,a.confirm_time) confirm_time,a.approve_time,
case when d.sid is null then (case when e.loan_time is not null then e.loan_time else a.loan_time end) else d.loan_time end loan_time,
case when e.apply_id is not null and aa.big_category<>‘钱包系’ then e.loan_state when e.loan_time is not null and a.state <> ‘已到期’ then ‘放款成功’ else a.state end status
,c.state,cw_state,in_state,
case when e.apply_id is not null and aa.big_category<>‘钱包系’ then e.loan_state_time when e.loan_time is not null and a.state <> ‘已到期’ then e.loan_time else a.state_time end state_time
,f.RealName name,
ifnull(b.apply_amount1,0) apply_amount,
ifnull(apply_period1,0) apply_period,
ifnull(approve_amount1,0) approve_amount,
– 处理ppd隐藏订单期数不存在
case when ifnull(approve_period1,0)<=0 then ifnull(h.LoanMonths1,0) else ifnull(approve_period1,0)end approve_period,
– ifnull(d.loan_amount1,0) loan_amount,
ifnull((case when d.sid is not null and d.loan_amount<>0 then d.loan_amount else b.settlement_amount end)1,0) loan_amount,
– 处理ppd隐藏订单结算金额不存在
case when ifnull(settlement_amount1,0)<=0 then ifnull(h.LoanAmount1,0) else ifnull(settlement_amount1,0) end settlement_amount,
ifnull(reconciliation_amount1,0) reconciliation_amount,
c.refresh,
a.src
,a.user_type
FROM ods.tmp_20181107 a
LEFT JOIN ods.ods_zfgjj_sys_apply_info b ON a.sid = b.apply_id
– 对隐藏订单的公积金编号进行处理
left join hidden.hidden_gjj_brief_apply g on a.sid=g.apply_id
left join zfgjj.sys_apply_discard h on a.sid=h.apply_id
LEFT JOIN zfgjj.sys_certification_tri f on a.user_sid=f.user_sid
LEFT JOIN zfgjj.stat_apply_revenue d on a.sid=d.sid – 取放款时间，金额
LEFT JOIN ods.tmp_ods_zfgjj_sys_apply_standard_sub e on a.product_cid = e.product_cid and a.sid = e.apply_id – 取标准化放款成功状态、时间
LEFT JOIN zfgjj.cfg_stat_status c ON a.product_cid=c.product_cid and case when e.loan_time is not null and e.loan_state <> ‘放款失败’ and a.state <> ‘已到期’ then ‘放款成功’ else a.state end=c.status
left join dm.dm_cfg_background_product aa on a.product_cid=aa.product_cid_all
WHERE (a.product_cid in (1,5,6,7,10,11,12,14,16,17,18,19,20,23,24,25,26,28,31,32,34,35,36,37,38,39,40,42,43,45,48,50,46,49,47,53,27,51,56,57,55,60,58,59,64,61,62,65,67,69,66,68,54,72,73,70,71,63,74,76,78,75,81,82,84,83,87,86,90,96,97,91,92,93,100,101,94,106,105,102,103,107,108,109,112,99,111,110,104,113,114,117,118,116,119,120,121,122,123,124,127,128,142,143,126,132,125,134,135,136,95,138,139,140,137,149,150) or aa.product_cid_all is not null)
AND a.sid>0

碰到这样的异常，

1105 - Can’t find column zfgjj.sys_apply_discard.apply_detail_json in schema Column: [zfgjj.sys_apply_discard.apply_id,zfgjj.sys_apply_discard.loanmonths,zfgjj.sys_apply_discard.loanamount,zfgjj.sys_apply_discard._tidb_rowid] Unique key: , Time: 0.048000s

这应该跟 zfgjj.sys_apply_discard 表有关，它的定义是这样的，

CREATE TABLE sys_apply_discard (
sid int(11) NOT NULL AUTO_INCREMENT,
apply_id int(11) DEFAULT NULL,
yys_cid int(11) DEFAULT NULL,
product_cid int(11) DEFAULT NULL,
user_sid int(11) DEFAULT NULL,
src int(11) DEFAULT 10 COMMENT ‘来源，传那边的yys_cid’,
apply_time timestamp DEFAULT CURRENT_TIMESTAMP,
state varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
state_time timestamp NULL DEFAULT NULL,
msg varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘比如错误的时候，对方会传过来一些错误信息’,
invalid tinyint(1) NOT NULL DEFAULT 0 COMMENT ‘是否可见。默认可见，0为可见。废单过多，用户可能会要求删除，或者我们业务末端控制该订单是否可见。’,
succ tinyint(1) NOT NULL DEFAULT 1 COMMENT ‘是否是成功订单，如果失败，请置为0’,
is_end tinyint(1) NOT NULL DEFAULT 0 COMMENT ‘该订单是不是结束状态。默认为未结束。’,
apply_json json DEFAULT NULL COMMENT ‘订单表json’,
apply_detail_json json DEFAULT NULL COMMENT ‘扩展表信息json’,
add_time timestamp DEFAULT CURRENT_TIMESTAMP,
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
__ts_ms timestamp NOT NULL DEFAULT ‘1971-01-01 00:00:00’,
confirm_time timestamp GENERATED ALWAYS AS (str_to_date(json_unquote(json_extract(apply_json, ‘$.confirm_time’)), ‘%Y-%m-%d %h:%i:%s’)) VIRTUAL,
approve_time timestamp GENERATED ALWAYS AS (str_to_date(json_unquote(json_extract(apply_json, ‘$.approve_time’)), ‘%Y-%m-%d %h:%i:%s’)) VIRTUAL,
loan_time timestamp GENERATED ALWAYS AS (str_to_date(json_unquote(json_extract(apply_json, ‘$.loan_time’)), ‘%Y-%m-%d %h:%i:%s’)) VIRTUAL,
ppd_sid int(11) GENERATED ALWAYS AS (json_extract(apply_detail_json, ‘$.sid’)) VIRTUAL,
LoanMonths int(11) GENERATED ALWAYS AS (json_extract(apply_detail_json, ‘$.LoanMonths’)) VIRTUAL,
LoanAmount int(11) GENERATED ALWAYS AS (json_extract(apply_detail_json, ‘$.LoanAmount’)) VIRTUAL,
RealName text GENERATED ALWAYS AS (json_unquote(json_extract(apply_detail_json, ‘$.RealName’))) VIRTUAL,
PRIMARY KEY (sid),
KEY idx_apply (apply_id),
KEY idx_product_cid (product_cid),
KEY idx_uptime (update_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=5235410;

其中有定义了 json 生成列。

请帮忙调查一下这个问题。这个问题挺严重，实际上复杂点的查询里面 json 生成列已经不能用了。

tminglei · 2020 年7 月 8 日 04:50

其实问题 TiDB Server 执行特定 SQL 崩溃也跟 json 生成列有关。
那个表现更严重，导致 TiDB Server 直接崩溃了。

后来只好先把 json 里面需要用到的数据先抽取出来，另外存好，查询时再 join，并且严格控制了事务大小，才得以执行下去。

yilong · 2020 年7 月 8 日 05:48

好的，我们先查看一下，会尽快答复。

yilong · 2020 年7 月 8 日 07:50

我这边根据两个表结构，剩下的sql比较简单，查询没有问题

winoros · 2020 年7 月 8 日 10:04

你好，请使用 select tidb_version(); 看一下 tidb 具体的版本信息

winoros · 2020 年7 月 8 日 10:09

可以贴一下具体

mysql> select tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()

                                                            |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.0
Edition: Community
Git Commit Hash: 689a6b6439ae7835947fcaccf329a3fc303986cb
Git Branch: release-4.0
UTC Build Time: 2020-07-08 10:07:53
GoVersion: go1.14.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

具体的结果吗，这个可以看到更详细的版本情况

tminglei · 2020 年7 月 8 日 10:13

Release Version: v4.0.0
Edition: Community
Git Commit Hash: 689a6b6439ae7835947fcaccf329a3fc303986cb
Git Branch: heads/refs/tags/v4.0.0
UTC Build Time: 2020-05-28 01:37:40
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

winoros · 2020 年7 月 8 日 10:20

方便贴一下 tidb 的 log，这里执行对应的查询是正常的

tminglei · 2020 年7 月 8 日 10:32

链接: https://pan.baidu.com/s/1ixFzs_ipic55-dUhGfFsnQ 提取码: gfgg

winoros · 2020 年7 月 8 日 11:21

再提供一下 tmp_ods_stat_apply_detail 这个表的表结构吧

tminglei · 2020 年7 月 8 日 12:01

我没找到什么地方用到了 tmp_ods_stat_apply_detail 表啊。
你是在什么地方看到的？

winoros · 2020 年7 月 8 日 12:04

[2020/07/08 02:23:10.402 +08:00] [WARN] [session.go:1136] ["compile SQL failed"] [conn=8587] [error="Can't find column zfgjj.sys_apply_discard.apply_detail_json in schema Column: [zfgjj.sys_apply_discard.apply_id,zfgjj.sys_apply_discard.loanmonths,zfgjj.sys_apply_discard.loanamount,zfgjj.sys_apply_discard._tidb_rowid] Unique key: []"] [SQL="insert into ods.tmp_ods_zfgjj_stat_apply_detail \
\t    SELECT \tdate(if(a.confirm_time*1=0,a.apply_time,a.confirm_time)) day,\
\t\t\t\t\t\ta.sid apply_sid,a.product_cid,a.user_sid,ifnull(b.gjj_sid,g.gjj_sid) gjj_sid,b.shebao_sid,b.bill_sid,\
\t\t\t\t\t\ta.apply_time,if(a.confirm_time*1=0 and a.apply_time<'2017-04-01',a.apply_time,a.confirm_time) confirm_time,a.approve_time,\
\t          case when d.sid is null then (case when e.loan_time is not null then e.loan_time  else a.loan_time end) else d.loan_time end loan_time,\
\t\t\t\t\t\tcase when e.apply_id is not null and aa.big_category<>'钱包系' then e.loan_state when e.loan_time is not null and a.state <> '已到期' then '放款成功' else a.state  end status\
\t\t\t\t\t\t,c.state,cw_state,in_state,\
\t\t\t\t\t\tcase when e.apply_id is not null and aa.big_category<>'钱包系' then e.loan_state_time when e.loan_time is not null and a.state <> '已到期' then e.loan_time else a.state_time  end state_time\
\t\t\t\t\t\t,f.RealName name,\
\t\t\t\t\t\tifnull(b.apply_amount*1,0) apply_amount,\
\t\t\t\t\t\tifnull(apply_period*1,0)   apply_period,\
\t\t\t\t\t\tifnull(approve_amount*1,0) approve_amount,\
\t\t\t\t\t\t-- 处理ppd隐藏订单期数不存在\
\t\t\t\t\t\tcase when ifnull(approve_period*1,0)<=0 then ifnull(h.LoanMonths*1,0) else ifnull(approve_period*1,0)end  approve_period,\
\t        --    ifnull(d.loan_amount*1,0) loan_amount,\
\t\t\t\t\t  ifnull((case when d.sid is not null and d.loan_amount<>0 then d.loan_amount else  b.settlement_amount end)*1,0) loan_amount,\
\t\t\t\t\t\t-- 处理ppd隐藏订单结算金额不存在\
\t\t\t\t\t\tcase when ifnull(settlement_amount*1,0)<=0 then ifnull(h.LoanAmount*1,0)  else ifnull(settlement_amount*1,0) end settlement_amount,\
\t          ifnull(reconciliation_amount*1,0) reconciliation_amount,\
\t          c.refresh,\
\t          a.src\
\t\t\t\t\t\t,a.user_type\
\t\t\tFROM ods.tmp_20181107 a\
\t\t\t\tLEFT JOIN ods.ods_zfgjj_sys_apply_info b ON a.sid = b.apply_id\
\t\t\t\t-- 对隐藏订单的公积金编号进行处理\
\t\t\t\tleft join hidden.hidden_gjj_brief_apply g on a.sid=g.apply_id\
\t\t\t\tleft join zfgjj.sys_apply_discard h on a.sid=h.apply_id\
\t\t    LEFT JOIN zfgjj.sys_certification_tri f on a.user_sid=f.user_sid\
\t      LEFT JOIN zfgjj.stat_apply_revenue d on a.sid=d.sid  -- 取放款时间，金额\
\t\t\t\tLEFT JOIN ods.tmp_ods_zfgjj_sys_apply_standard_sub e \ton a.product_cid = e.product_cid and a.sid = e.apply_id  -- 取标准化放款成功状态、时间\
\t\t\t\tLEFT JOIN zfgjj.cfg_stat_status c ON a.product_cid=c.product_cid  and case when e.loan_time is not null and e.loan_state <> '放款失败' and a.state <> '已到期' then '放款成功'  else a.state end=c.status \
\t\t\t\tleft join dm.dm_cfg_background_product aa on a.product_cid=aa.product_cid_all \
\t\t\tWHERE (a.product_cid in (1,5,6,7,10,11,12,14,16,17,18,19,20,23,24,25,26,28,31,32,34,35,36,37,38,39,40,42,43,45,48,50,46,49,47,53,27,51,56,57,55,60,58,59,64,61,62,65,67,69,66,68,54,72,73,70,71,63,74,76,78,75,81,82,84,83,87,86,90,96,97,91,92,93,100,101,94,106,105,102,103,107,108,109,112,99,111,110,104,113,114,117,118,116,119,120,121,122,123,124,127,128,142,143,126,132,125,134,135,136,95,138,139,140,137,149,150) or aa.product_cid_all is not null)\
\t\t\t\tAND a.sid>0 ;\
\t    "]
[2020/07/08 02:23:10.402 +08:00] [ERROR] [conn.go:728] ["command dispatched failed"] [conn=8587] [connInfo="id:8587, addr:172.16.150.112:5564 status:10, collation:utf8mb4_general_ci, user:procs"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="insert into ods.tmp_ods_zfgjj_stat_apply_detail \
\t    SELECT \tdate(if(a.confirm_time*1=0,a.apply_time,a.confirm_time)) day,\
\t\t\t\t\t\ta.sid apply_sid,a.product_cid,a.user_sid,ifnull(b.gjj_sid,g.gjj_sid) gjj_sid,b.shebao_sid,b.bill_sid,\
\t\t\t\t\t\ta.apply_time,if(a.confirm_time*1=0 and a.apply_time<'2017-04-01',a.apply_time,a.confirm_time) confirm_time,a.approve_time,\
\t          case when d.sid is null then (case when e.loan_time is not null then e.loan_time  else a.loan_time end) else d.loan_time end loan_time,\
\t\t\t\t\t\tcase when e.apply_id is not null and aa.big_category<>'钱包系' then e.loan_state when e.loan_time is not null and a.state <> '已到期' then '放款成功' else a.state  end status\
\t\t\t\t\t\t,c.state,cw_state,in_state,\
\t\t\t\t\t\tcase when e.apply_id is not null and aa.big_category<>'钱包系' then e.loan_state_time when e.loan_time is not null and a.state <> '已到期' then e.loan_time else a.state_time  end state_time\
\t\t\t\t\t\t,f.RealName name,\
\t\t\t\t\t\tifnull(b.apply_amount*1,0) apply_amount,\
\t\t\t\t\t\tifnull(apply_period*1,0)   apply_period,\
\t\t\t\t\t\tifnull(approve_amount*1,0) approve_amount,\
\t\t\t\t\t\t-- 处理ppd隐藏订单期数不存在\
\t\t\t\t\t\tcase when ifnull(approve_period*1,0)<=0 then ifnull(h.LoanMonths*1,0) else ifnull(approve_period*1,0)end  approve_period,\
\t        --    ifnull(d.loan_amount*1,0) loan_amount,\
\t\t\t\t\t  ifnull((case when d.sid is not null and d.loan_amount<>0 then d.loan_amount else  b.settlement_amount end)*1,0) loan_amount,\
\t\t\t\t\t\t-- 处理ppd隐藏订单结算金额不存在\
\t\t\t\t\t\tcase when ifnull(settlement_amount*1,0)<=0 then ifnull(h.LoanAmount*1,0)  else ifnull(settlement_amount*1,0) end settlement_amount,\
\t          ifnull(reconciliation_amount*1,0) reconciliation_amount,\
\t          c.refresh,\
\t          a.src\
\t\t\t\t\t\t,a.user_type\
\t\t\tFROM ods.tmp_20181107 a\
\t\t\t\tLEFT JOIN ods.ods_zfgjj_sys_apply_info b ON a.sid = b.apply_id\
\t\t\t\t-- 对隐藏订单的公积金>编号进行处理\
\t\t\t\tleft join hidden.hidden_gjj_brief_apply g on a.sid=g.apply_id\
\t\t\t\tleft join zfgjj.sys_apply_discard h on a.sid=h.apply_id\
\t\t    LEFT JOIN zfgjj.sys_certification_tri f on a.user_sid=f.user_sid\
\t      LEFT JOIN zfgjj.stat_apply_revenue d on a.sid=d.sid  -- 取放款时间，金额\
\t\t\t\tLEFT JOIN ods.tmp_ods_zfgjj_sys_apply_standard_sub e \ton a.product_cid = e.product_cid and a.sid = e.apply_id  -- 取标准化放款成功状态、时间\
\t\t\t\tLEFT JOIN zfgjj.cfg_stat_status c ON a.product_cid=c.product_cid  and case when e.loan_time is not null and e.loan_state <> '放款失败' and a.state <> '已到期' then '放款成功'  else a.state end=c.status \
\t\t\t\tleft join dm.dm_cfg_background_product aa on a.product_cid=aa.product_cid_all \
\t\t\tWHERE (a.product_cid in (1,5,6,7,10,11,12,14,16,17,18,19,20,23,24,25,26,28,31,32,34,35,36,37,38,39,40,42,43,45,48,50,46,49,47,53,27,51,56,57,55,60,58,59,64,61,62,65,67,69,66,68,54,72,73,70,71,63,74,76,78,75,81,82,84,83,87,86,90,96,97,91,92,93,100,101,94,106,105,102,103,107,108,109,112,99,111,110,104,113,114,117,118,116,119,120,121,122,123,124,127,128,142,143,126,132,125,134,135,136,95,138,139,140,137,149,150) or aa.product_cid_all is not null)\
\t\t\t\tAND a.sid>0 ;\
\t    "] [txn_mode=OPTIMISTIC] [err="Can't find column zfgjj.sys_apply_discard.apply_detail_json in schema Column: [zfgjj.sys_apply_discard.apply_id,zfgjj.sys_apply_discard.loanmonths,zfgjj.sys_apply_discard.loanamount,zfgjj.sys_apply_discard._tidb_rowid] Unique key: []\
github.com/pingcap/tidb/expression.(*Column).resolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/expression/column.go:465\
github.com/pingcap/tidb/expression.(*ScalarFunction).resolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/expression/scalar_function.go:427\
github.com/pingcap/tidb/expression.(*ScalarFunction).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/expression/scalar_function.go:398\
github.com/pingcap/tidb/planner/core.resolveIndicesForVirtualColumn\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:238\
github.com/pingcap/tidb/planner/core.(*PhysicalIndexLookUpReader).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:279\
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:600\
github.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:589\
github.com/pingcap/tidb/planner/core.(*PhysicalProjection).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/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/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:600\
github.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:589\
github.com/pingcap/tidb/planner/core.(*PhysicalIndexJoin).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:161\
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:600\
github.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:589\
github.com/pingcap/tidb/planner/core.(*PhysicalIndexJoin).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:161\
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:600\
github.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:589\
github.com/pingcap/tidb/planner/core.(*PhysicalMergeJoin).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:118\
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:600\
github.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:589\
github.com/pingcap/tidb/planner/core.(*PhysicalHashJoin).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/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/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:600\
github.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:589\
github.com/pingcap/tidb/planner/core.(*PhysicalProjection).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/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/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:600\
github.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:589\
github.com/pingcap/tidb/planner/core.(*PhysicalHashJoin).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/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/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:600\
github.com/pingcap/tidb/planner/core.(*physicalSchemaProducer).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:589\
github.com/pingcap/tidb/planner/core.(*PhysicalHashJoin).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/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/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:600\
github.com/pingcap/tidb/planner/core.(*PhysicalSelection).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:324\
github.com/pingcap/tidb/planner/core.(*basePhysicalPlan).ResolveIndices\
\t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:600"]

这一条 log 里出现的，是一个 insert 语句

tminglei · 2020 年7 月 8 日 13:12

嗯，你开始表名写错了。这是相关的表结构，

CREATE TABLE tmp_ods_zfgjj_stat_apply_detail (
day date DEFAULT NULL,
apply_sid int(11) DEFAULT NULL,
product_cid int(11) DEFAULT NULL,
user_sid int(11) DEFAULT NULL,
gjj_sid int(11) DEFAULT NULL,
shebao_sid int(11) DEFAULT NULL,
bill_sid int(11) DEFAULT NULL,
apply_time timestamp NULL DEFAULT NULL,
confirm_time timestamp NULL DEFAULT NULL,
approve_time timestamp NULL DEFAULT NULL,
loan_time timestamp NULL DEFAULT NULL,
status varchar(255) DEFAULT NULL,
state tinyint(4) DEFAULT NULL,
cw_state tinyint(4) DEFAULT NULL,
in_state tinyint(4) DEFAULT NULL,
state_time timestamp NULL DEFAULT NULL,
name varchar(40) DEFAULT NULL,
apply_amount bigint(20) DEFAULT NULL,
apply_period bigint(20) DEFAULT NULL,
approve_amount bigint(20) DEFAULT NULL,
approve_period bigint(20) DEFAULT NULL,
loan_amount bigint(21) DEFAULT NULL,
settlement_amount bigint(20) DEFAULT NULL,
reconciliation_amount bigint(20) DEFAULT NULL,
refresh tinyint(4) DEFAULT NULL,
src int(11) DEFAULT NULL,
user_type tinyint(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

GangShen · 2020 年7 月 10 日 03:36

收到，我们还在分析问题原因中，有新的进度的话，会及时同步，感谢理解

wjhuang2016-PINGCAP · 2020 年7 月 13 日 08:41

@tminglei

你好，问题的原因已经查明，该问题会在后续版本中修复。触发的条件是走 IndexJoin 时，孩子结点是 IndexLookupExecutor 回表读并有虚拟生成列时。

tminglei · 2020 年7 月 13 日 08:55

收到，谢谢

来了老弟 · 2020 年7 月 13 日 09:07

system · 2022 年10 月 31 日 19:11

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