[Bug]: MYSQL giving unreliable results when columns have a default value as a built_in method.

[abhishekkumams]

What happened?

Let's say we have a table containing some default values as methods.

CREATE TABLE default_with_function_table
(
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_value INT,
    `current_date` TIMESTAMP DEFAULT (CURRENT_DATE) NOT NULL,
    `current_timestamp` TIMESTAMP DEFAULT (NOW()) NOT NULL,
    random_number INT DEFAULT (FLOOR(RAND() * 1000)) NOT NULL
);

And below is the generated query for insert:

INSERT INTO `default_with_function_table` (`user_value`) VALUES (@param0); 
SET @ROWCOUNT=ROW_COUNT(); 
SELECT 
  last_insert_id() as `id`,
  @param0 as `user_value`, 
  curdate() as `current_date`, 
  now() as `current_timestamp`, 
  floor((rand() * 1000)) as `random_number`
WHERE @ROWCOUNT > 0;

this query will insert a row but returned values of the inserted row will be different.

For example, in the above case we have a column which stores random_number or current_timestamp. The insert and update command will give incorrect result.

The above query will insert a different value and return a different value because the select query is not picking the inserted value from DB, instead it's giving the value from the methods set as default for those columns.

The selection sql query generated in the mysql insert/update query use the below code.

This will require update, we would have to select the columns using column names like we are doing for MsSql and PgSql.

Version

main

What database are you using?

MySQL

What hosting model are you using?

Local (including CLI)

Which API approach are you accessing DAB through?

REST, GraphQL

Relevant log output

No response

Code of Conduct

• I agree to follow this project's Code of Conduct

[ayush3797]

This one is also along the same lines: #1696