tidb throws error when querying zero datetime with table statistics

[mzhang77]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

For certain sql_mode, timestamp data type allows values '0000-00-00 00:00:00'.

2. What did you expect to see? (Required)

There should be no error if a row has timpstamp column with value '0000-00-00 00:00:00'

3. What did you see instead (Required)

lots of following errors in tidb log:

[2024/09/30 22:03:52.873 +00:00] [ERROR] [terror.go:324] ["encountered error"] [error="[types:1292]Incorrect time value: '{0 0 0 0 0 0 0}'"] [stack="github.com/pingcap/tidb/pkg/parser/terror.Log\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/parser/terror/terror.go:324\ngithub.jparrowsec.cn/pingcap/tidb/pkg/types.(*Time).Sub\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/types/time.go:706\ngithub.jparrowsec.cn/pingcap/tidb/pkg/statistics.convertDatumToScalar\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/statistics/scalar.go:77\ngithub.jparrowsec.cn/pingcap/tidb/pkg/statistics.(*Histogram).OutOfRangeRowCount\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/statistics/histogram.go:955\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/cardinality.GetColumnRowCount\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/cardinality/row_count_column.go:291\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/cardinality.GetRowCountByColumnRanges\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/cardinality/row_count_column.go:66\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/cardinality.expBackoffEstimation\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/cardinality/row_count_index.go:435\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/cardinality.getIndexRowCountForStatsV2\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/cardinality/row_count_index.go:285\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/cardinality.GetRowCountByIndexRanges\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/cardinality/row_count_index.go:86\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/core.(*DataSource).detachCondAndBuildRangeForPath\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/core/logical_plans.go:1681\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/core.(*DataSource).fillIndexPath\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/core/logical_plans.go:1835\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/core.(*DataSource).DeriveStats\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/core/stats.go:474\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/core.(*baseLogicalPlan).recursiveDeriveStats\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/core/stats.go:139\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/core.(*baseLogicalPlan).recursiveDeriveStats\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/core/stats.go:132\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/core.(*baseLogicalPlan).recursiveDeriveStats\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/core/stats.go:132\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/core.(*baseLogicalPlan).recursiveDeriveStats\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/core/stats.go:132\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/core.physicalOptimize\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/core/optimizer.go:1189\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/core.DoOptimizeAndLogicAsRet\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/core/optimizer.go:334\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/core.DoOptimize\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/core/optimizer.go:359\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner.optimize\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/optimize.go:531\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner.Optimize\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/optimize.go:352\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/core.generateNewPlan\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/core/plan_cache.go:313\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner/core.GetPlanFromSessionPlanCache\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/core/plan_cache.go:212\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner.OptimizeExecStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/optimize.go:553\ngithub.jparrowsec.cn/pingcap/tidb/pkg/planner.Optimize\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/planner/optimize.go:180\ngithub.jparrowsec.cn/pingcap/tidb/pkg/executor.(*Compiler).Compile\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/executor/compiler.go:98\ngithub.jparrowsec.cn/pingcap/tidb/pkg/session.(*session).ExecuteStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/session/session.go:2221\ngithub.jparrowsec.cn/pingcap/tidb/pkg/server.(*TiDBContext).ExecuteStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/server/driver_tidb.go:292\ngithub.jparrowsec.cn/pingcap/tidb/pkg/server.(*clientConn).executePreparedStmtAndWriteResult\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/server/conn_stmt.go:306\ngithub.jparrowsec.cn/pingcap/tidb/pkg/server.(*clientConn).executePlanCacheStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/server/conn_stmt.go:234\ngithub.jparrowsec.cn/pingcap/tidb/pkg/server.(*clientConn).handleStmtExecute\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/server/conn_stmt.go:225\ngithub.jparrowsec.cn/pingcap/tidb/pkg/server.(*clientConn).dispatch\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/server/conn.go:1345\ngithub.jparrowsec.cn/pingcap/tidb/pkg/server.(*clientConn).Run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/server/conn.go:1091\ngithub.jparrowsec.cn/pingcap/tidb/pkg/server.(*Server).onConn\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/pkg/server/server.go:715"]

4. What is your TiDB version? (Required)

v7.5.1

[winoros]

It's been fixed by #52615, begin from v8.1.
<= 7.5 needs a extra fix for it.

[winoros]

This issue is created for recording the affected releases.
Close because it's fixed in master.

[seiya-annie]

/report customer