Improper handling of DATE field with DEFAULT clause

[UrsusArctos]

Preconditions
HeidiSQL version: 12.4.0.6670
MySQL 8.0.32
OS: Gentoo Linux

Describe the bug
I was creating a table with DATE field and I wanted default clause for it to be (CURDATE()). Here's how it is done in mysql CLI:

mysql> CREATE TABLE `test` (`pdate` DATE DEFAULT (CURDATE()));
Query OK, 0 rows affected (0.29 sec)

mysql> SHOW CREATE TABLE test;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `pdate` date DEFAULT (curdate())
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

I then tried to do the same in HeidiSQL. But it seems to be unaware of CURDATE() and/or of the fact that default clauses for DATE fields can be expressions.

To Reproduce
First of all, CURDATE() is missing from the function list

But that's OK, I can type it in. So HeidiSQL does this:
CREATE TABLE `test` ( `pdate` INT NULL DEFAULT (CURDATE()) ) COLLATE='utf8mb4_0900_ai_ci' ;
And it kind of works. But then after I refresh, table editor shows default clause as a string literal???

And the editor of the column is messed up too.

Screenshots or Crash reports
It does not crash per se, but makes any table alterations a big hassle.

[ansgarbecker]

I just created the same table here on MariaDB, and the table designer shows me "curdate()" as an expression, plus the dropdown has that function name:

Could be this is only an issue on MySQL, not on MariaDB.

Are you using HeidiSQL on Gentoo Linux or was that the server OS? Your screens look like you're not on Wine but Windows.

This is related to #1666 for which I added CURDATE to the functions-mariadb.ini, but not to functions-mysql.ini - will do that now.

[UrsusArctos]

@ansgarbecker Yes I was running HeiqiSQL under Windows, connected via TCP/IP to MySQL under Gentoo Linux.

Could be this is only an issue on MySQL, not on MariaDB.

Unlikely, given that mysql CLI does fine.

Interestingly, I patched that ini file and CURDATE() is now present in the function list, but table editor STILL messes up it with string literal :(

[ansgarbecker]

Handling of default expressions of MySQL and MariaDB in HeidiSQL is different. See my IsTextDefault implementation: https://github.com/HeidiSQL/HeidiSQL/blob/master/source/dbconnection.pas#L5579

On top of that, MySQL up to v8.0.13 was different than newer versions (like yours). See the docs: https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html#data-type-defaults-explicit-old
Older versions only allowed very limited set of functions as default value. Newer versions seem to allow everything you like.

[ansgarbecker]

Here we go, next build checks if the default value has a parentheses in the default value of a MySQL 8.0.13+ column, showing it as an expression then. Watch out for the single quotes in the screen:

Please update to the latest build in an hour and re-test.

[UrsusArctos]

Yes, the column editor how displays expression correctly. You almost fixed it. However...

Unless one manually adds the parentheses around the expression in the editor, the ALTER statement produces a syntax error as it lacks the parentheses too. Would it be prudent to automatically enclose the expression in the parentheses?

ALTER TABLE `chronicle` CHANGE COLUMN `pubdate` `pubdate` DATE NOT NULL DEFAULT curdate() COMMENT 'publication date' AFTER `id`;
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'curdate() COMMENT 'publication date' AFTER `id`' at line 2 */

[ansgarbecker]

Definitely, the table editor should wrap in parentheses. Just modified that for the next nightly build.

[UrsusArctos]

Now it works fine, thank you very much.

[ansgarbecker]

Thank you for your consistent feedback!