MySQL Bugs: #111971: Data format changed during Select/Insert query

Bug #111971	Data format changed during Select/Insert query
Submitted:	4 Aug 2023 10:54	Modified:	7 Aug 2023 7:00
Reporter:	Rens Huizenga	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	MySQL Community Server 8.1.0	OS:	Windows (Python MySQl connector V8)
Assigned to:		CPU Architecture:	x86
Tags:	VARCHAR DATE

Description:
When Running a Python script with a Select / Insert query on a table with VARCHAR(255) fields to a table with VARCHAR(255) fields the value of the data changes from 1-1-2012 in the originating table to 01-01-2012 in the destination table. Nowhere is mentioned it is data formatted LIKE a date.

Only by changing the query like below I could solve this (suggestion ChatGPT).

cursor.execute("""
    INSERT INTO Selections (CKSCCONTRACTNUMMER, Creadate, Deldate, SIEBELASSETINTEGRATIONID,
        CKSSTATUS, CKSDATUMSTATUSWIJZIGING, CKSRESTANTLOOPTIJD, CKSNETTORESTSCHULD,
        SIEBELKLANTIDHOOFD, LARAKLANTNRHOOFD, CKSBEHEERHOOFD, SIEBELKLANTIDMEDE,
        LARAKLANTNRMEDE, CKSBEHEERMEDE)
    SELECT
        CSV.CKSCCONTRACTNUMMER, current_timestamp(), NULL, CSV.SIEBELASSETINTEGRATIONID,
        CSV.CKSSTATUS, DATE_FORMAT(CSV.CKSDATUMSTATUSWIJZIGING, '%d-%m-%Y') AS formatted_date,
        CSV.CKSRESTANTLOOPTIJD, CSV.CKSNETTORESTSCHULD,
        CSV.SIEBELKLANTIDHOOFD, CSV.LARAKLANTNRHOOFD, CSV.CKSBEHEERHOOFD, CSV.SIEBELKLANTIDMEDE,
        CSV.LARAKLANTNRMEDE, CSV.CKSBEHEERMEDE
    FROM
        CSV
    WHERE
        CSV.CKSCCONTRACTNUMMER IN (SELECT CKSCCONTRACTNUMMER FROM Updates)

How to repeat:
See above

Suggested fix:
Don't change data formats (in fact content) in a select/insert when source and target formats are equal

Hi Mr. Huizenga,

Thank you for your bug report. However, ........

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php 

If you can provide sufficient information, we can then proceed to process the report.

We need a fully repeatable test case, that should consist of a set of SQL statements, as well as your Python code. We must be able to repeat it in order to process the report.

Thank you for your interest in MySQL.

Later I also found out that numbers with leading zeroes stored in the VARCHAR fields had lost the leading zeroes after the Insert/Select query had copied the data to the target table. Pretty annoying.

I am very very sorry to have bothered you. I have found a very stupid mistake on my side. MySQL works like expected. Thank you for your attention!

You are truly welcome.