1105 - Other Error: Invalid Data Type: Invalid Range, range.start Should Be Smaller Than

Translated
1

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

Original topic: 1105 - other error: invalid data type: invalid range,range.start should be smaller than

| username: wluckdog

[TiDB Usage Environment] Production Environment / Testing
[TiDB Version] tidb v6.5
[Reproduction Path] SQL Query

SELECT
 MAX(d_time) AS d_time,
 MAX(CASE WHEN c_time > '2011-01-01 00:00:00' THEN c_time ELSE NULL END) AS c_time,
 MAX(CASE WHEN b_time > '2011-01-01 00:00:00' THEN b_time ELSE NULL END) AS b_time,
 MAX(CASE WHEN a_time > '2011-01-01 00:00:00' THEN a_time ELSE NULL END) AS a_time 
FROM
 tab_a AS t
 JOIN tab_b AS o ON t._code = o.code 
 AND o.p_code = '99999990' 
 AND o.valid = 10 
 AND o.s_code = 20 
WHERE
 t.e_time >= '2023-03-22 16:57:03.0' 
 AND t.e_time <= '2023-03-22 16:57:03.0'

[Encountered Problem: Problem Phenomenon and Impact]

1105 - other error: invalid data type: invalid range, range.start should be smaller than range.end, but got [[116, 128, 0, 0, 0, 0, 0, 20, 125, 95, 105, 128, 0, 0, 0, 0, 0, 0, 21, 1, 55, 53, 53, 55, 48, 48, 52, 55, 255, 57, 0, 0, 0, 0, 0, 0, 0, 248, 4, 25, 175, 173, 14, 67, 0, 0, 0], [116, 128, 0, 0, 0, 0, 0, 20, 125, 95, 105, 128, 0, 0, 0, 0, 0, 0, 21, 1, 55, 53, 53, 55, 48, 48, 52, 55, 255, 57, 0, 0, 0, 0, 0, 0, 0, 248, 4, 25, 175, 172, 142, 67, 0, 0, 1]]
Time: 0.147s

[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

2
| username: tidb菜鸟一只 | Original post link

Try changing the condition?

3
| username: wluckdog | Original post link

This error does not always occur; sometimes it can query results, and sometimes it reports an error.

4
| username: 沈阳盛京征信有限公司 | Original post link

Is it recommended in the development guidelines to compare date and time sizes without explicitly adding conversion functions? :sweat_smile:

5
| username: wluckdog | Original post link

This has nothing to do with it. Even if you add STR_TO_DATE, it will still report an error.

6
| username: wluckdog | Original post link

I think it is a bug related to the timestamp type.

7
| username: tidb菜鸟一只 | Original post link

If convenient, please share the table structure for a look.

8
| username: wluckdog | Original post link

Inconvenient, the table structure is too long.

9
| username: yilong | Original post link
  1. You can provide the table structure used in the SQL, other columns are not needed, but other information is required, such as whether it is a cluster table, character set, etc.
  2. Please upload the complete execution plan and error stack information.
10
| username: ljluestc | Original post link

The error message “invalid data type: invalid range, range.start should be smaller than range.end” usually indicates an issue with range queries in TiDB or TiKV. Range queries involve a range of values, such as a time range, and are common in SQL statements that filter rows based on a range of values.

The root cause of the error can be due to various factors, including incorrect data types or formats in the range values, a mismatch between the data types of the columns and the range values, or issues with the query execution plan. One potential cause could be the use of columns with different precisions in the query and the data stored in TiDB. For example, if the query uses a time column with higher precision than the data stored in TiDB, it may lead to such an error.

To debug this error, you can first review the query execution plan and identify the range query causing the error. You can use the EXPLAIN statement in TiDB to generate the query execution plan and analyze it for potential issues. Additionally, you can check the data types and formats of the range values to ensure they match the expected data types and formats of the columns being queried.

Another way to debug this error is to use TiDB/TiKV log files to identify any potential issues with query execution or data storage. You can enable TiDB/TiKV log files in the configuration file and set the log level to a higher level to capture more detailed information. Then, you can search the log files for any relevant error messages or warnings that may indicate the root cause of the error.

Here is an example script that can help identify range queries and their corresponding time ranges in a TiDB cluster:

#!/bin/bash

echo "Query ID,Time Range"
echo "-------------------"

for id in $(tidb-server debug zip | grep -Po 'query-id-\d+' | sort -u); do
  query=$(tidb-server debug decode --id "$id" | jq -r .query)
  if [[ "$query" == *"BETWEEN"* ]]; then
    start_time=$(tidb-server debug decode --id "$id" | grep -oP "BETWEEN '\K[0-9:\-\. ]+" | head -1)
    end_time=$(tidb-server debug decode --id "$id" | grep -oP "AND '\K[0-9:\-\. ]+" | head -1)
    echo "$id,$start_time - $end_time"
  fi
done

This script uses the TiDB debug tool to identify all queries that use the BETWEEN operator, which is commonly used in range queries, and extracts the start and end time values from the queries. It then prints the query ID and the corresponding time range to the console. This helps identify any potential range query issues in the TiDB cluster.