The same query does not report an error, but insertion reports an error

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

Original topic: 相同语句查询不报错,插入报错

| username: Kongdom

[TiDB Usage Environment] Production Environment
[TiDB Version] v6.5.2
It is known that there are illegal date characters, and no error is reported when querying, but an error is reported when inserting the query result into the table.
Reproducible operation:

CREATE TABLE tmp_customer(c_no varchar(20), c_id varchar(20));
CREATE TABLE tmp_customer_import(c_no varchar(20), c_id varchar(20));
INSERT INTO tmp_customer values('1','19901901');
INSERT INTO tmp_customer values('2','19901001');
INSERT INTO tmp_customer values('3','19900901');

-- Query records with a date difference of more than 18 years
SELECT * FROM tmp_customer
WHERE TIMESTAMPDIFF(YEAR, IFNULL(str_to_date(c_id,'%Y%m%d'),'2024-02-22'), '2024-02-22') >= 18

-- Insert records with a date difference of more than 18 years
INSERT INTO tmp_customer_import
SELECT * FROM tmp_customer
WHERE TIMESTAMPDIFF(YEAR, IFNULL(str_to_date(c_id,'%Y%m%d'),'2024-02-22'), '2024-02-22') >= 18

SQL Error [1292] [22001]: Data truncation: Incorrect datetime value: '0000-00-00 00:00:00'
| username: 随便改个用户名 | Original post link

Indeed, this phenomenon has been reproduced in V6.5.6.

| username: 数据库真NB | Original post link

The result you queried is of date field type, while the target table you are inserting into is of string type, causing a type mismatch. List the query results separately and then convert them back to strings, that should work.

| username: 数据库真NB | Original post link

Field type mismatch, it is estimated that it can be reproduced in any version.

| username: TIDB-Learner | Original post link

update tmp_customer set c_id =‘19901201’ where c_no =‘1’

| username: TIDB-Learner | Original post link

This line has a time format issue, the month is out of range. MySQL and TiDB will report an error in any version.

| username: Soysauce520 | Original post link

When logging in, if you add the --comment option to the MySQL command, you will find warnings information in the query. There are issues that are just hidden, and when inserting, validation will result in errors.

| username: DBAER | Original post link

Version 6.1 also has this issue, where the condition after the WHERE clause causes type conversion.

| username: DBAER | Original post link

To add on, it should be related to sql_mode. Setting it to null allows insertion.

| username: WinterLiu | Original post link

Master, I’ve learned a lot. :+1:

| username: dba远航 | Original post link

The month value ‘19901901’ in the INSERT INTO tmp_customer statement is invalid. Try changing it to a reasonable value, and it should work.

| username: Trouble | Original post link

No, the queried data is from the original table, and various transformations of predicates are used as filters.

| username: TiDBer_vfJBUcxl | Original post link

Excellent, learned something new.

| username: 数据库真NB | Original post link

@Trouble, I see it, it should be that this data is invalid.

| username: 数据库真NB | Original post link

Thank you for your guidance.

| username: wangccsy | Original post link

After the date conversion, it overflowed and became 0 year 0 month 0 day, which is considered an invalid date.

| username: Kongdom | Original post link

Yes, but the strange thing is that last night, when inserting into two tables, one table reported an error while the other table did not. It seems like it shouldn’t be related to the target table.

| username: Kongdom | Original post link

:+1: :+1: :+1: This can also be used this way. It seems that a --comment should be added by default to the table.

| username: Kongdom | Original post link

:yum: I’m sorry to inform you that the client’s environment does not allow setting to null. However, this provides a direction, it should be caused by a certain configuration in sql_mode.

| username: Kongdom | Original post link

:joy: Logically, if there’s an error during insertion, there should also be an error during querying. After all, the date field wasn’t inserted during the insertion process.