Support expressions (functions) for field defaults (table definition), ALTER, INSERT, CREATE?

[OlafvdSpek]

MariaDB 10.2 supports expressions for field defaults. However pMA escapes and quotes the expression, causing it to become an ordinary string.

ALTER TABLE `photo_likes` CHANGE `created` `created` INT(11) NOT NULL DEFAULT unix_timestamp();

https://mariadb.com/kb/en/library/create-table/#default

[williamdes]

Confirmed.

Have a look to #15006, #14986 for data examples

CREATE TABLE issue_default_values (
  logtime01 varchar(40) NOT NULL DEFAULT current_timestamp,
  logtime0 datetime(2) NOT NULL DEFAULT current_timestamp,
  logtime datetime(2) NOT NULL DEFAULT current_timestamp(2),
  logtext varchar(255) NOT NULL DEFAULT current_timestamp(3),
  logtext11 text NOT NULL DEFAULT current_timestamp(3),
  uuid1 BINARY(16) DEFAULT unhex(replace(uuid(),'-','')),
  uuid int(11) DEFAULT year(now() )
);

[OlafvdSpek]

Could this be fixed before 5.0?

[williamdes]

@OlafvdSpek If someone finds a fix (before end of this month) that supports all use cases, why not

cc @Tithugues @saurass @ShailuJain @kartik1000

[OlafvdSpek]

Why not block the release until it's fixed?

[williamdes]

@OlafvdSpek you can email @ibennetch or discuss it here or on #15607 🤷‍♂️
For now the releases are scheduled to the end of the month

[OlafvdSpek]

Ah, you mean whether to block or not.

[yashrajbothra]

I will try to reproduce and fix this 👍

[saurass]

I will try to reproduce and fix this

Yeah sure go ahead : )

[yashrajbothra]

Can anyone help me reproduce this I tried it in -> DEMO
using MariaDB

[williamdes]

@yashrajbothra you need to use difficult examples
See my comment #14371 (comment)

[williamdes]

@yashrajbothra did you try queries from the linked issues in my comment?

[yashrajbothra]

Thanks @williamdes So,I looked into all the related issue and What i understood is that the when we insert any pre-defined functions of mysql into value it treats function as string.Execpt current_timestap() .
Because of this ->

So, current_timestap(2) is also treated as string.

Did i get it Alright ?

[williamdes]

Yes you dit get it alright @yashrajbothra
So I did see PRs that tried to list all possible functions and it was not the right solution because we can use user defined ones and even chained functions etc..

[OlafvdSpek]

Who approved that condition? ;)

[williamdes]

@OlafvdSpek can you be more explicit?

[OlafvdSpek]

The first part is too hard to understand, ($type != 'datetime' && $type != 'timestamp') would be easier.
(Just a comment in general)

[yashrajbothra]

@OlafvdSpek I think this de250fb commit .

[yashrajbothra]

We Expect these USER DEFINED VALUES in Functions right?
Like:

[williamdes]

That would maybe be a solution but be sure that very custom non standard functions also work

Basically I think we can say that the rule is : keep the user defined default value (function or mysql magic constants like timestamp) usable for insertion but also allow that the user changes the function or uses a hard coded value

Thoes conditions make a fix very complicated IMO but a great idea could solve all the cases :)

[yashrajbothra]

Basically I think we can say that the rule is : keep the user defined default value (function or mysql magic constants like timestamp) usable for insertion but also allow that the user changes the function or uses a hard coded value

But if we keep functions in USER DEFINED DEFAULT VALUES how will we diffrentiate that the input is a function or string?

@williamdes Did you mean to hard code all the mysql function like we did with current_timestamp() ?

[williamdes]

But if we keep functions in USER DEFINED DEFAULT VALUES how will we diffrentiate that the input is a function or string?

I have no idea it is a challenge to solve :)

And no for hard coding the functions, I will not work because the possibilities are endless

Maybe we could add an option the the selected function that would equal to the default value of the column
But it would be confusing for the user

[williamdes]

Did you mean if user gives a default function with param

No parameter is possible for a column that defines a function as default value ;)

[yashrajbothra]

Like if CURDATE() doesnt expect a param. we should disable the value for the same. If i am correct than i will definetly include that 👍

[williamdes]

Like if CURDATE() doesnt expect a param. we should disable the value for the same. If i am correct than i will definetly include that

I think I did not explain well my point

The list will contain

• functions as it did before
• a new entry that will be the value of the default value for the column

Obviously it is impossible that it can have a parameter because the table schema will not allow that.

So for that particular option I do not want that the user fills a value that will not be used anyway

Just to say in another way:
When you create the table you define the default value for a column and if you use a function it is impossible to include a parameter.

[yashrajbothra]

I this was able to understand your point 👍

The list will contain

• functions as it did before
• a new entry that will be the value of the default value for the column

but If in case lets say i have a table with a column without any default value. When i insert data from INSERT form i want to use USER_DEFINED function (which is not a default value). Can we do that as of now?.

Or this is totally diffrent issue?

[williamdes]

I this was able to understand your point

The list will contain

• functions as it did before
• a new entry that will be the value of the default value for the column

but If in case lets say i have a table with a column without any default value. When i insert data from INSERT form i want to use USER_DEFINED function (which is not a default value). Can we do that as of now?.

Or this is totally diffrent issue?

;)

Or this is totally diffrent issue?

Yes, I would like to avoid mixing features :)

[ghost]

2 years later and still not fixed...

[saurass]

2 years later and still not fixed...

Hello, @zaseth we are working on this issue, Please refer to this PR #15975

[ChrisHSandN]

Reading thought this thread, I wonder if maybe an easier solution is to add the DEFAULT() function as an <option> on the insert page to the function <select> dropdown. Even if nothing else was done this would at least allow users to pick that option manually as a workaround for #15006 etc.

Ideally this <option> could act a bit like the Null checkbox, where it is automatically selected (when there is a default entry for that field) but is cleared as soon as you start typing a value into the <textarea>.

The insert SQL with the option selected could then be e.g. INSERT INTO t (i,j) VALUES (DEFAULT,'foobar'); which would avoid all the complexities of having to parse the actual function expression and support parameters etc.

---

For the alter CREATE/ALTER views would it be too naïve just to look for a leading ' or " in the information_schema.COLUMNS.COLUMN_DEFAULT field, If it has one it is a string value, if not it is a function?

That could be combined with a simple Is Function checkbox which turns off the automatic quoting/escaping when checked.

[williamdes]

Thank you for the feedback, adding the DEFAULT function seems a good idea !

And adding a Is Function also seems a very good idea !

[OlafvdSpek]

Reading thought this thread, I wonder if maybe an easier solution is to add the DEFAULT() function as an <option> on the insert page to the function <select> dropdown. Even if nothing else was done this would at least allow users to pick that option manually as a workaround for #15006 etc.

Ideally this <option> could act a bit like the Null checkbox, where it is automatically selected (when there is a default entry for that field) but is cleared as soon as you start typing a value into the <textarea>.

Isn't mapping empty to default enough for the insert form?

[ChrisHSandN]

Isn't mapping empty to default enough for the insert form?

If the empty function <option> = DEFAULT then there would be no way to override that and insert a '' (blank string) as the value.

DEFAULT(): If the column has no default value ... an error is returned.
https://mariadb.com/kb/en/default/

For fields that don't have a DEFAULT value set you would have to treat these differently or DEFAULT() would produce an error if used in the INSERT SQL.

[OlafvdSpek]

For fields that don't have a DEFAULT value set you would have to treat these differently or DEFAULT() would produce an error if used in the INSERT SQL.

Currently '' is used for 'empty' integers, which produces a warning.
Fields without a default value need a value anyway..

[alexander-zierhut]

+1 Very glad seeing this in the 5.2.0 milestone! Thank you

[Nefcanto]

This is still not solved. I use uuid() as the default value for some of my string keys in my CMS. For example, the Slug or Key fields are either valid strings, or if they are not filled, instead of creating some random ugly string, I have decided to fill them with uuid values.

Thus they are string columns, that have uuid() set as their default values.

When I change the order of columns via phpMyAdmin, they would be wrapped with single, or multiple quotes, and the uuid() becomes a simple string like 'uuid()' or '''uuid()'''.