Phpmyadmin: 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 :man_shrugging:
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 https://github.com/phpmyadmin/phpmyadmin/issues/14371#issuecomment-433714771

[yashrajbothra]

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

I tried above query in Demo server and it gave the issue as explained.
But when i tried to reproduce it in local machine i was unable to reproduce 😕

Configuration

OS : Windows 10 Pro 1909

cc @williamdes

[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 de250fbae3d8bb5f68ef816f63abd9d04e792ff2 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

[yashrajbothra]

IMO for diffrenciating BTW values there can be a field like DEFINED FUNCTION as we haveUSER DEFINED VALUES so it will not be treated as string.

[yashrajbothra]

OR Can we give a checkbox in INSERT tab which treats value as function ?
OR maybe a field in Function dropdown as USER DEFINED (taking about Insert Tab)

INSERT INTO film_text (film_id, title, description) VALUES (inventory_in_stock(1),'Hello' , 'World')
Like if i wanna execute this in GUI is it possible now?

[williamdes]

Can we give a checkbox in INSERT tab which treats value as function ?

Seems like a good idea, the user can fill the contents with another function or real data and it will work

[yashrajbothra]

this would be even better than checkbox i guess

It will solve all the problems 😃

[OlafvdSpek]

Alter table and create table functions are more important then the Insert tab..

[yashrajbothra]

Ya Sure @OlafvdSpek ,But this change can solve basic problem of executing query with functions in GUI. Because AFAIK the form is same for ALTER and INSERT. And for CREATE table we do the same change like this 🤨

[yashrajbothra]

Hey @williamdes can i start working on this or do we need suggestions from other members ?

this would be even better than checkbox i guess

It will solve all the problems 😃

[williamdes]

Hey @williamdes can i start working on this or do we need suggestions from other members ?

this would be even better than checkbox i guess

It will solve all the problems smiley

It would be an idea but it is not explicit enough, maybe in italic
A checkbox would be cool

• [ ] value is a function

Pre-filled with the default value

That would be a double enhancement

[yashrajbothra]

• [ ] value is a function

Ya this is also nice idea but there are some consequenses like

1. We need more UI space for whole row of value is a function and the will only one checkbox in it
2. If we check the value is a function then we have to disable the functionality of Function row because we cant use both.

If we use Value is a function field in Function than there is not need of those things.

@williamdes OR if these things doesnt bother than i can continue with checkbox ?

[williamdes]

@williamdes OR if these things doesnt bother than i can continue with checkbox ?

I agree with you we can do better

I was thinking what about setting the function with the value of the default value in functions list ?

I would more explicit that "user defined" and also would be a valid function to be in such a list

[yashrajbothra]

I was thinking what about setting the function with the value of the default value in functions list ?

Ya, Thats good but what about normal insertion of user defined functions (can we do that with current UI ?)

Like I have created a User Defined function foo (or any function which is not in the function list) can we use it while insertion or altering

[williamdes]

I was thinking what about setting the function with the value of the default value in functions list ?

Ya, Thats good but what about normal insertion of user defined functions (can we do that with current UI ?)

Like I have created a User Defined function foo (or any function which is not in the function list) can we use it while insertion or altering

Yes, let the user use it anytime :)

[yashrajbothra]

So in conclusion ,

1. All the function should be shown in Function field (including user defined function)
2. And Value will be treated as parameters

Correct me if i am wrong 👍

[williamdes]

So in conclusion ,

1. All the function should be shown in Function field (including user defined function)
2. And Value will be treated as parameters

Correct me if i am wrong

Maybe I would disable the input for the default function added in the list because it is not expected to have parameters in a normal mysql insert into workflow

[yashrajbothra]

Maybe I would disable the input for the default function added in the list because it is not expected to have parameters in a normal mysql insert into workflow

Some of the default mysql function expects parameters like Reverse() and currently value is being used for parameters

[williamdes]

Maybe I would disable the input for the default function added in the list because it is not expected to have parameters in a normal mysql insert into workflow

Some of the default mysql function expects parameters like Reverse() and currently value is being used for parameters

What I was pointing out is not to disable parameters for all functions but only for the one that will correspond to the default value of the column

[yashrajbothra]

@williamdes Did you mean if user gives a default function with param. then we should disable the value field ?

But if that is the case then what if user doesnt always want to use default param. and want to change the param. what should happen in that case?

[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 :)

[Zaseth]

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 https://github.com/phpmyadmin/phpmyadmin/pull/15975