Critical Partition Query Error, SQL Error 1105: interface conversion: interface{} is nil, not types.Time

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 临界分区查询报错,sql错误1105,interface conversion: interface{} is nil ,not types.Time

| username: grafanab

[Test Environment for TiDB] Testing
[TiDB Version] 5.7.25-TiDB-v6.5.2

Table creation statement:

CREATE TABLE `test1` (
	`ID` VARCHAR(50) NOT NULL COMMENT 'Primary Key',
	`PARTITION_NO` INT(11) NOT NULL DEFAULT '0' COMMENT 'Partition',
	`CREATE_TIME` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation Time',
	PRIMARY KEY (`ID`,`PARTITION_NO`,`CREATE_TIME`) USING BTREE,
	INDEX `index_partition_no` (`PARTITION_NO`) USING BTREE
)
COMMENT='Test Table'
ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(PARTITION_NO,CREATE_TIME)
(PARTITION `2023p1` VALUES LESS THAN (200000,'2023-01-01'),
PARTITION `2023p2` VALUES LESS THAN (300000,'2023-01-01'));

When the partition value is 200000, querying results equal to 200000 will report an error. What is the reason?

Query SQL:

  1. SELECT * FROM test1 WHERE PARTITION_NO = 200000
  2. SELECT * FROM test1 WHERE PARTITION_NO >= 200000

Both execution results report an error.

| username: xfworld | Original post link

Is there any error information in the TiDB logs?

| username: grafanab | Original post link

There is no other information. The value 200000 happens to be the partition value. I don’t know if it’s a bug or an operational error on my part. I couldn’t find a corresponding explanation in the official documentation.

| username: grafanab | Original post link

Using Java JDBC to query results in the same error, indicating that there is no issue with the SQL tool.

| username: xfworld | Original post link

Is it possible that when defining the partition, CREATE_TIME did not follow the requirements for dateTime? Is date not a valid value?

You can try
**


**

| username: grafanab | Original post link

The time format is correct, but the database doesn’t have any data.

| username: grafanab | Original post link

The time formats ‘2023-01-01’ and ‘2023-01-01 00:00:00’ should be the same.

| username: grafanab | Original post link

TiDB should not be unable to distinguish ‘yyyy-MM-dd’ default hours, minutes, and seconds.

| username: xfworld | Original post link

Try again.

| username: grafanab | Original post link

Already tried it.

| username: xfworld | Original post link

It’s probably a bug~ Congratulations on winning~ :upside_down_face:

| username: grafanab | Original post link

:joy: There’s no other way; we can only avoid this issue from the business side for now. Not sure if it’s a bug or if the version doesn’t support it.

| username: db_user | Original post link

I tested it locally, and it looks very similar to this bug. After running analyze and then querying, there will be a disconnection. It seems to be treated as a null value for some reason.
connection disconnected when select * from t as of timestamp NULL · Issue #33728 · pingcap/tidb (github.com)

| username: aytrack | Original post link

It’s a bug, and it will be tracked in this issue: query range colums partition report error · Issue #43459 · pingcap/tidb · GitHub

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.