Column Defaults

[peterlaursen]

I CREATE TABLE like create.jpg

table created like

Code:

CREATE TABLE `testtable` (                                                          
            `id` bigint(20) NOT NULL auto_increment,                                          
            `mytext` varchar(50) NOT NULL,                                                    
            `mynull` varchar(50) default NULL,                                                
            `myts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,  
            PRIMARY KEY  (`id`)                                                              
          ) ENGINE=MyISAM DEFAULT CHARSET=latin1

Column 'myts' is defined 'on update CURENT_TIMESTAMP' as it should (but only when NOT NULL is checked.

However column 'mynull' is not handled correctly. See nonull.jpg. I ONLY enter data into 'mytext' column and SQLyog writes

Code:

insert into `testtable` (`id`,`mytext`,`mynull`,`myts`) values ( '','d','',CURRENT_TIMESTAMP)

It should EITHER

Code:

insert into `testtable` (`id`,`mytext`,`mynull`,`myts`) values ( '','d',NULL,CURRENT_TIMESTAMP)

but better would be

Code:

insert into `testtable` (`id`,`mytext`,`myts`) values ( '','d',CURRENT_TIMESTAMP)

It is the idea with COLUMN DEFAULTS that the server should handle this – not the client. You did it right with CURRENT-TIMESTAMP – so why not NULL ? I think I wrote you more than 20 mails about this!

Also CURRENT_USER is not supported yet, it seems. default.jg produces

Code:

`myuser` varchar(50) default 'CURRENT_USER'

and not

`myuser` varchar(50) default CURRENT_USER as it should.





Could you provide a listing of which DEFAULTs are supposed to be supported?

[peterlaursen]

Correction:

it should write simply

Code:

insert into `testtable` (`mytext`) values ('d')

I don't believe you will ever make this work unless you understand that COLUMN DEFAULTS should handled by the server and not the client. Simply skip empty cells when generating an INSERT or UPDATE statement as you allready do with TIMESTAMPs defined as 'on update CURRENT_TIMESTAMP' when there are data in them.

In NON-STRICT mode as used by SQLyog connections this will never generate an error.

[peterlaursen]

more specific:

an INSERT or UPDATE statement generated from DATA or RESULT panes should skip a column when:

1) type is TIMESTAMP when defined as 'on update CURRENT_TIMESTAMP' (no matter if there is a DEFAULT and what it is and no matter if it is NULL or NOT NULL)

2) column has a DEFAULT (no matter what that default is) and cell is empty

3) cell contains a DEFAULT that is also a supported KEYWORD (CURRENT_USER etc)

4) column is auto-increment

This will let the server do its job as it should! It think that this is all there is to it!

[Ritesh]

What if a USER actually wants to enter a blank string and not NULL. We cannot differentiate in that case.

By default, SQLyog shows the default value in the grid. From v5.1 BETA 2, it will show the string (NULL) which SQLyog interprets as NULL.

We plan to keep BLANK cell as '' and if somebody specifically wants to insert a NULL value, he will need to write (NULL).

For a column having auto_increment value, SQLyog will insert '' if left blank and the server correctly handles it by inserting the next auto-increment value in the field. This works for MySQL versions 3.23.x to 5.x.

[peterlaursen]

Quote:

What if a USER actually wants to enter a blank string and not NULL.

he could then enter “`” (backquote). That is implemented allready though not documented

Quote:

For a column having auto_increment value….

that is true. Bull still the idea with auto-increment is taht server should handle it.

Quote:

if somebody specifically wants to insert a NULL value, he will need to write (NULL). 

That is an strange construction in my opinion.  Those paranthesises are absolutely non-standard.  Why not just NULL then?  If user wants to insert (literal) '(NULL)' he will have to write “`(NULL)”.  So you don't avaoid the use of backquote if you should handle all cases (of which most are close to absurdity – agreed!)

Quote:

We plan to keep BLACK cell …

I don't understand

Now tell me:

1) How will you handle CURRENT_USER etc. It should not insert 'CURRENT_USER'

2) I reported a BUG – documented with screenshots. Default was NULL – I did not enter anything in that cell and it inserted '' (empty string). Comments? Now if you say that it displays (NULL) when NULL is DEFAULT how will I be able to tell if it actually is NULL or if it is '(NULL)'

I still think my solution is the correct and logical one. It lets the server do what the server is designed to do. Also the solution most compatible with most sql_modes.

[peterlaursen]

Now ..

It looks like CURRENT_USER is not a keyword that can be used as DEFAULT with a column definition. I thought it was. CURRENT_USER() is a valid function however.

What KEYWORDS else than NULL and CURRENT_TIMESTAMP should be supported by the new SQLyog 'smart DEFAULTs' feature?

[peterlaursen]

have a look a this

'localtimestamp' can be used exactly as current_timestamp. Also 'on update …'

(howeveer the server (also 5.1.6) converts it to current_timestamp – 3.23 does not support 'localtimestamp' with 'on update' (tried – returned syntax error).

So they should be supported exactly the same way.

I think the MySQL docs http://dev.mysql.com/doc/refman/5.1/en/dat…-functions.html are ambigious. it says:

1) It says that current_timestamp is synonyn of now() and localtimestamp is synonym as now()

2) but these statements:

— CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().

— The CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), and FROM_UNIXTIME() functions return values in the connection's current time zone

contradict I believe!

Oracle behaves like http://www.cs.ncl.ac.uk/teaching/facilitie…unctions64a.htm and undoubtedly that is where MySQL aim at. But seems not to be implemented yet! Do I understand right, u think? Maybe a question for MySQL?

But (maybe) localtimestamp should be implemented in SQLyog exactly like current_timestamp. If the server wants to convert – then let it as long as it lasts!

And UTC_TIMESTAMP ??

I must admit that 'localtimestamp' is the only additional keyword I can find that works as DEFAULT when searching the embedded database ! 🙁

[Ritesh]

There is no function like CURRENT_USER(). CURRENT_USER is a special keyword like CURRENT_TIMESTAMP.

Currently, SQLyog correctly understands CURRENT_TIMESTAMP and CURRENT_USER and if a user enters one of above, SQLyog will not put it within ''.

session_user() is a function and SQLyog correctly handles it.

The two keywords that SQLyog handles are CURRENT_TIMESTAMP and CURRENT_USER and they are coded into the binary. They are not taken from Keywords.db.

We have improved SQLyog as it will now display (NULL) by default for columns whose default value has been defined to be NULL. It use to be BLANK earlier and thus on updation, SQLyog would generate statement like:

insert (….) values ( '' );

Now by default, we will put (NULL) in the column and on execution it will issue a query like:

insert (….) values ( NULL );

We use (NULL) because this has been a standard with SQLyog from the first version and we don't plan to change it.

[Ritesh]

Just read you last post. CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), and FROM_UNIXTIME() are handled correctly and they are taken from Keywords.db.

[Ritesh]

Sorry my mistake.

CURRENT_USER() is indeed a function. But CURRENT_USER is also a special keyword like CURRENT_TIMESTAMP.

[peterlaursen]

But CURRENT_USER cannot be used as DEFAULT?

If I try with SQLyog CREATE TABLE/ ALTER TABLE it is enclosed between '' in the SQL.

[Ritesh]

Fixed in BETA 2 development tree.

[Ritesh]

The four special KEYWORDS that SQLyog will now recognise are:

CURRENT_TIMESTAMP

CURRENT_USER

localtimestamp

UTC_TIMESTAMP

I couldn't find a congregated list of all such keywords.

[peterlaursen]

Quote:

Fixed in BETA 2 development tree.

What is fixed?

If I try use DEFAULT CURRENT_USER form SQLpane I get error 1064.

Do I do something wrong?

[peterlaursen]

Also UTC_TIMESTAMP generates error 1064 with DEFAULT like

alter table `test2`.`testtable` change `myts2` `myts2` timestamp NOT NULL default UTC_TIMESTAMP;

hmmm.. here is something to keep an eye at! We will se changes with this stuff as MySQL 5.1 'grows', I'm afraid!

[peterlaursen]

Now this is funny!

default for `mynull`is 'CURRENT_USER' (literal)

however when SQLyog saves it does not quote and thus it becomes CURRENT_USER (root@localhost)

SQLyog has enhanced the functionalities of MySQL 😮

funny .. yes! But not very good! I think we will have to display as (CURRENT_USER) and (UTC_TIMESTAMP) and to parse for (CURRENT_USER) and (UTC_TIMESTAMP) like for (NULL) when saving from the grid!

[Ritesh]

You cannot specify CURRENT_USER as default value in CREATE TABLE statement. Only CURRENT_TIMESTAMP is applicable.

If you specify CURRENT_USER as default value in CREATE/ALTER option, it will execute it as CREATE TABLE aaa ( a …. default 'CURRENT_USER' ).

In the DATA tab, it will show CURRENT_USER as default value (implemented in v5.1 BETA 2). When executed, it successfuly understands the constant and will insert data like: insert into (….) values ( CURRENT_USER ).

[peterlaursen]

I doubt if you've read my last post with your latest writing.

If default is defined 'CURRENT_USER' it should not become CURRENT_USER (aka root@localhost). This is why I think we should do with CURRENT_USER and UTC_TIMESTAMP as with NULL – enclose it in brackets () and parse for it like we allready parse for (NULL).

This 'feature' may at first sight look 'smart' but it is wrong actually, and not at all useful as SQLyog is the only client that does like this.

Quote:

Only CURRENT_TIMESTAMP is applicable

I think localtimestamp also is applicable as DEFAULT. Even with 'on update localtimestamp'. At least you can run it from commandline like this (but it is transformed by the server as of 5.1.6).

[peterlaursen]

Now …

suppose that a column contains (literal) '(NULL)'. It will then be displayed as (NULL). When saving it will be parsed as NULL .. and column has changed from '(NULL)' to NULL <_< Now that also applies if dispayed as NULL as I proposed. No difference. The whole problem is that any display is ambigious. NULL is nothing and a grid always contains something. Even an empty string is something!

This earlier proposal of mine

Quote:

an INSERT or UPDATE statement generated from DATA or RESULT panes should skip a column when:

3) cell contains a DEFAULT that is also a supported KEYWORD (CURRENT_USER etc)

even does not solve it. Because if you save a literal 'NULL' (using backquote = `NULL) and open it again it displays just like a NULL, and next you save it would become NULL.

Won't we simply need to display the BACKQUOTE for literal KEYWORDS (no matter if they are bracketed () or not) – to avoid that they become functional KEYWORDS after a save?

I am really sorry .. I don't mean to bother anybody.

Who invented that NULL (where is the hangman icon?) ?

[peterlaursen]

I could not help myself:

http://bugs.mysql.com/bug.php?id=17809&thanks=4

[Ritesh]

🙂

[peterlaursen]

One more then:

http://bugs.mysql.com/bug.php?id=17817

[peterlaursen]

@ritesh

I wrote this earlier

Quote:

… Because if you save a literal 'NULL' (using backquote = `NULL) and open it again it displays just like a NULL, and next you save it would become NULL.

Are you PERFECTLY SURE, that you read and understood this. You did not comment on it. (Too) often that implies that you were not concentrated when you read it!

Now I ask you again: The solution that you have come up with, will it secure that a literal-keywords (ALL of them!) do not become a functional-keywords after multible reads and saves (and the other way around of course too)?

[Ritesh]

peterlaursen wrote on Mar 3 2006, 02:14 PM:

@ritesh

I wrote this earlier

Are you PERFECTLY SURE, that you read and understood this.  You did not comment on it.  (Too) often that implies that you were not concentrated when you read it!

Now I ask you again: The solution that you have come up with, will it secure that a literal-keywords (ALL of them!) do not become a functional-keywords after multible reads and saves (and the other way around of course too)?

[post=”9004″]<{POST_SNAPBACK}>[/post]

I have read it completely. We have still not decided on the final structure of this feature. We will take it up completely after BETA 2.

Right now we are concentrating more on the auto-complete feature but let me ASSURE you, we will give 100% to each and every feature before releasing the FINAL.

[peterlaursen]

I am sorry 😀

But I think I know how to 'wake you up'. I just wait till after BETA2 to concentrate on this then. Maybe even then MySQL would reply to my requests at bugs.mysql.com so this can be taken into consideration

You know DEFAULTs are my personal pets! But the character/SQL issues and autocomplete finished with Beta 2 isn't bad either.

[peterlaursen]

BETA 2:

I see that you changed the use of backquote from prefixing to surrounding. That's is OK.

writing “`NULL`” Inserts literal NULL Also “NULL” and “(NULL)” both insert NULL. I like that I can simply write “NULL”.

There is a small issue: writing “`(NULL)`” does not insert literal (NULL) but NULL.

Now it just has to be documented! @ritesh: You or I ??

[peterlaursen]

I also see that with auto-icrements you changed from

insert into `t1` (`id`,`t`) values ('','gh')

to

insert into `t1` (`id`,`t`) values ( NULL,'gh')

Much better in my opinion! 😀

And you found a solution so tha a literal CURRENT_TIMESTAMP does not become CURRENT_TIMESTAMP with multible saves!

Would you mind tell HOW that is implemented ?

[Author]

Posts