Bug #11383 Can't enter an empty string literal for default value.
Submitted: 16 Jun 2005 13:58 Modified: 8 Jul 2005 14:05
Reporter: Paul van Rossem Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Query Browser Severity:S2 (Serious)
Version:1.1.10 OS:Windows (XP)
Assigned to: Vladimir Kolesnikov CPU Architecture:Any

[16 Jun 2005 13:58] Paul van Rossem
Description:
When editing a table definition in Query browser, it is impossible to define an empty string as a default value for a VARCHAR column.

How to repeat:
Start query browser. Define a column as VARCHAR(10). Select "Column details" tab. Select "Not Null".  Now try to specify in the "Default value" edit control an empty string as the default value for this column:
- leaving it empty is interpreted as "no default specified"; 
- entering '' (two single quotes) is interpreted as one single quote character
- entering "" (two double quotes) is interpreted as two double quote characters.

This can be seen after applying the changes, closing the table editor and then re-opening the table editor on the same table and inspecting the new definition.

How to specify an empty string literal?

Part of the problem may be the ambiguous definition of allowing to specify a single quote inside a string by entering two successive quotes. The empty string specified by two successive quotes is then ambiguous: is it an empty string or a single quote? Note that this rule is unneeded as a single quote can easily be specified by the escape sequence \' or \".

Suggested fix:
Add a separate button to specify an empty string (like the NULL button already present).
[8 Jul 2005 14:05] Vladimir Kolesnikov
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Hi Paul,

when I follow your steps to reproduce the bug I get the following SQL "ALTER TABLE `test`.`t1` ADD COLUMN `s` VARCHAR(10) NOT NULL AFTER `id`;" which successfully executes and alters the table. So I didn't notice any wrong behaviour at this step.

If you later issue "SHOW CREATE TABLE `test`.`t1`" query, you'll see a definition like (I used 4.1.11-nt server)

CREATE TABLE `t1` (
  `id` int(10) NOT NULL default '0',
  `s` char(45) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED

notice that the "default" clause is present for the 's' column and is the empty value. So in fact you don't have to do anything special to set empty string as default value.

For more information please refer to the documentation (section 13.2.5. CREATE TABLE Syntax), where this behaviour is explicitly described.

Regards
-------------------------------------------------
Vladimir Kolesnikov, GUI Developer
MySQL AB, Kyiv, Ukraine, www.mysql.com

Are you MySQL certified?  www.mysql.com/certification
[8 Jul 2005 14:38] Paul van Rossem
1) When I repeat exactly what you did, I get another result:

SHOW CREATE 'test','t1';

CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL,
  `s` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2) Although this may be a bug or not (it's not the one I mentioned!), I was trying to report a 
bug about the Query Builder, not about the command line tool. 
I think what you did was using the command line tool, not the query browser! There you don't have my problem...

So, there seem to be 2 bugs here?
[11 Jul 2005 10:02] Vladimir Kolesnikov
Hi Paul,
I produced all the steps using Query Browser (just enter the query into the query editor and press the lightning button). Can you tell me what version of MySQL server are you using?

Thanks.
-------------------------------------------------
Vladimir Kolesnikov, GUI Developer
MySQL AB, Kyiv, Ukraine, www.mysql.com

Are you MySQL certified?  www.mysql.com/certification
[23 Jul 2005 14:11] Paul van Rossem
I see. But I did not enter any query in a query editor window, but used the graphical interface instead (rightclik on a table in the rightmost pane and select "edit table"). It's this graphical interface that poses the problem I described, not the query itself.
Using MySQL server 5.0.7. beta, query editor 1.1.10.