Update Bug

[dany]

Tested in SQLyog 5.12 beta 7

1) Create a table (innodb)

Field a type int

Field b type char(10) NOT NULL

2) Select table and press F11

3) Insert two row:

type 1 in field “a” of the first row

type 2 in field “a” of the second row

4) Now press F11 (everything is ok)

5) Insert “Default” in field b of the first row.

6) Change row then press F11

–> bug: the “Default” is not in the table (wrong UPDATE command)

[peterlaursen]

Confirmed! But is not a bug – but an undocumented feature I think

With your description:

The update command executed is

update `TableName1` set `a`='1',`b`='' where `a`='1' and `b`=''

and you expected

update `TableName1` set `a`='1',`b`='Default' where `a`='1' and `b`=''

Now if you change to:

5) Insert “`Default`” in field b of the first row. — notice the use of backquotes

it does:

update `TableName1` set `a`='1',`b`='Default' where `a`='1' and `b`=''

******************************************

Now refer to the FAQ: http://webyog.com/faq/8_99_en.html

******************************************

Next do (give column a default-value):

alter table `test`.`TableName` change `b` `b` char (10) DEFAULT 'my_default' NOT NULL COLLATE latin1_swedish_ci

Delete all b-values and

repeat step 5+6: and it displays 'my_default'

******************************************

Conclusion:

1) Writing 'default' sets cell to column-default.

2) To write 'default' as a literal you must enclose in backquotes like '`default`' – just like you must write '`null`' or '`current_user`' to enter literals 'null' and 'current_user'.

Even I was not aware of that when writing this FAQ. Ritesh and Sarat never told me 😉

********************************************************************************

***

[peterlaursen]

FAQ write-up accordingly!

[dany]

Hi Peter,

first of all remember to change the last update date in the FAQ 🙂

I think that the behavior of SQLyog should be different:

if you right click the cell and choose “Set to default” the cell is filled with “(Default)”

The very same thing should happend when you try to manually insert “Default”: it should be changed to “(Default)” when you change row so that you understand what is happening.

I think it is very strange that SQLyog does not understands the “Default” keyword if you define field b (in my example) as a char(10) without enabling the “NOT NULL” flag.

Daniele

[peterlaursen]

Quote:

first of all remember to change the last update date in the FAQ 🙂

I bypassed the FAQ administrative backend and edited the database directly as it is faster:

Quote:

I think it is very strange that SQLyog does not understands the “Default” keyword if you define field b (in my example) as a char(10) without enabling the “NOT NULL” flag.

Well it does I think ???? See attached. There is no 'NOT NULL' for the varchar column and it inserts 'blabla' OK. Would you mind explain

Quote:

… the cell is filled with “(Default)”

I hate those paranthesis'es myself. Wish Ritesh did never invent (NULL) . NULL is clear as DEFAULT is. (NULL) and (DEFAULT) is a weird construction like (Ritesh). (Dany) and (Peter) is 😆 Nobody shall call me (Peter) ….

[dany]

In this moment I'm using SQLyog 5.11 Enterprise.

See attachment:

Without the “NOT NULL” flag I can insert “Default” and it is saved in the row as a string.

[peterlaursen]

5.12 beta 7 does

(edited)

Quote:

update `t1` set `id`='1',`t`='blabla' where `id`='1' and `t`=''

— or whatever values id and t hold

.. even if there is no default for any column in the table and no PK

If there is a PK only the PK column is used in the WHERE …

[Ritesh]

Added the DEFAULT handling with ( in the TO-DO list of SQLyog.

[peterlaursen]

@ I did not understand that ..

What do you mean ?

[Ritesh]

Quote:

Wish Ritesh did never invent (NULL) . NULL is clear as DEFAULT is. (NULL) and (DEFAULT) is a weird construction like (Ritesh). (Dany) and (Peter) is laugh.gif Nobody shall call me (Peter) ….

We will look into the above issue in v5.3.

[peterlaursen]

As of now NULL and (NULL) are synonyms with the grid, as DEFAULT and (DEFAULT) is.

To me just NULL and DEFAULT would have been OK … but no need to change it now.

[Author]

Posts