not null column with null defaults

I know this has been asked before and yes, I have read the section in
the documentation about it.
But, my question is: If I have setup mysql with strict_trans_tables,
will MySQL allow "null" defaults in "not null" columns?
If the answer is yes, will that information be reflected when I ask for
metadata (column information, etc.)?
Currently, when I ask for metadata information, not-null columns will
return a default of "empty string".

As additional information, I currently use 5.0.21-community-nt

Re: not null column with null defaults

axelsino@gmail.com wrote:
> But, my question is: If I have setup mysql with strict_trans_tables,
> will MySQL allow "null" defaults in "not null" columns?

No: "NOT NULL DEFAULT NULL" is not a legal combination in strict mode.

Specifying no DEFAULT clause has the same effect as specifying DEFAULT
NULL, such that inserting to the table without specifying a value for
the column attempts to insert NULL to that column. A NOT NULL column
rejects it, and a NULLable column uses NULL as the implicit default.

The only difference is that "SHOW CREATE TABLE" reports "DEFAULT NULL"
if the column is NULLable, and no default clause for a column that is
declared as NOT NULL.

CREATE TABLE foo (i int NOT NULL);
SHOW CREATE TABLE foo;
-> CREATE TABLE `foo` (
`i` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE bar (i int);
SHOW CREATE TABLE bar;
-> CREATE TABLE `bar` (
`i` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SELECT VERSION();
-> 5.0.21-community-nt-log

SELECT @@SQL_MODE;
-> STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTI ON

Regards,
Bill K.

Re: not null column with null defaults

Thanks for your reply.

I do have one more problem..

given the following table (I have strict_trans_tables):

create table some_table
(id int not null auto_increment,
some_text varchar(20) not null,
other_text varchar(20) default 'aaa',
another_text varchar(20),
primary key (id)
) type=innodb;

I execute "show columns from some_table" using both ODBC, connector/J
and mysql command line.
The command line returns "some_text" as having a NULL default.
ODBC and connector/J return "some_text" as having an empty string as
default.
While all of them report "another_text" as having a NULL default

Is this a bug? Is this by design?


Bill Karwin wrote:
> axelsino@gmail.com wrote:
> > But, my question is: If I have setup mysql with strict_trans_tables,
> > will MySQL allow "null" defaults in "not null" columns?
>
> No: "NOT NULL DEFAULT NULL" is not a legal combination in strict mode.
>
> Specifying no DEFAULT clause has the same effect as specifying DEFAULT
> NULL, such that inserting to the table without specifying a value for
> the column attempts to insert NULL to that column. A NOT NULL column
> rejects it, and a NULLable column uses NULL as the implicit default.
>
> The only difference is that "SHOW CREATE TABLE" reports "DEFAULT NULL"
> if the column is NULLable, and no default clause for a column that is
> declared as NOT NULL.
>
> CREATE TABLE foo (i int NOT NULL);
> SHOW CREATE TABLE foo;
> -> CREATE TABLE `foo` (
> `i` int(11) NOT NULL
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>
> CREATE TABLE bar (i int);
> SHOW CREATE TABLE bar;
> -> CREATE TABLE `bar` (
> `i` int(11) default NULL
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>
> SELECT VERSION();
> -> 5.0.21-community-nt-log
>
> SELECT @@SQL_MODE;
> -> STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTI ON
>
> Regards,
> Bill K.

Re: not null column with null defaults

axelsino@gmail.com wrote:
> create table some_table
> (id int not null auto_increment,
> some_text varchar(20) not null,
> other_text varchar(20) default 'aaa',
> another_text varchar(20),
> primary key (id)
> ) type=innodb;
>
> I execute "show columns from some_table" using both ODBC, connector/J
> and mysql command line.
> The command line returns "some_text" as having a NULL default.

I just tried your example table on my instance of MySQL 5.0.21, and it
shows this:

mysql> show columns from some_table;
+--------------+-------------+------+-----+---------+------- ---------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+------- ---------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| some_text | varchar(20) | NO | | | |
| other_text | varchar(20) | YES | | aaa | |
| another_text | varchar(20) | YES | | NULL | |
+--------------+-------------+------+-----+---------+------- ---------+

Apologies if the formatting gets messed up by proportional fonts.

Anyway, notice that the "some_text" column shows NO for its nullability,
and an empty space for its default value.

So it is consistent with what you saw in ODBC and Java, and also agrees
with the docs on the issue. Perhaps you misread the output of show
columns in the mysql monitor?

Regards,
Bill K.

Re: not null column with null defaults

Here's my output... 5.0.21 running under XP.
Notice the column some_text.. Again, apologies for the lack of
formatting.

mysql> select version() ;
+---------------------+
| version() |
+---------------------+
| 5.0.21-community-nt |
+---------------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> show columns from my_table ;
+---------------+-------------+------+-----+---------+------ ----------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+------ ----------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| some_text | varchar(20) | NO | | NULL | |
| other_text | varchar(20) | YES | | aaa | |
| another_table | varchar(20) | YES | | NULL | |
+---------------+-------------+------+-----+---------+------ ----------+
4 rows in set (0.00 sec)

mysql> show create table my_table ;
+----------+----------------------------------
----------------------------------------------
---------------------------------------------+
| Table | Create Table

|
+----------+----------------------------------
----------------------------------------------
---------------------------------------------+
| my_table | CREATE TABLE "my_table" (
"id" int(11) NOT NULL auto_increment,
"some_text" varchar(20) NOT NULL,
"other_text" varchar(20) default 'aaa',
"another_table" varchar(20) default NULL,
PRIMARY KEY ("id")
) |
+----------+----------------------------------
----------------------------------------------
---------------------------------------------+


Bill Karwin wrote:
> axelsino@gmail.com wrote:
> > create table some_table
> > (id int not null auto_increment,
> > some_text varchar(20) not null,
> > other_text varchar(20) default 'aaa',
> > another_text varchar(20),
> > primary key (id)
> > ) type=innodb;
> >
> > I execute "show columns from some_table" using both ODBC, connector/J
> > and mysql command line.
> > The command line returns "some_text" as having a NULL default.
>
> I just tried your example table on my instance of MySQL 5.0.21, and it
> shows this:
>
> mysql> show columns from some_table;
> +--------------+-------------+------+-----+---------+------- ---------+
> | Field | Type | Null | Key | Default | Extra |
> +--------------+-------------+------+-----+---------+------- ---------+
> | id | int(11) | NO | PRI | NULL | auto_increment |
> | some_text | varchar(20) | NO | | | |
> | other_text | varchar(20) | YES | | aaa | |
> | another_text | varchar(20) | YES | | NULL | |
> +--------------+-------------+------+-----+---------+------- ---------+
>
> Apologies if the formatting gets messed up by proportional fonts.
>
> Anyway, notice that the "some_text" column shows NO for its nullability,
> and an empty space for its default value.
>
> So it is consistent with what you saw in ODBC and Java, and also agrees
> with the docs on the issue. Perhaps you misread the output of show
> columns in the mysql monitor?
>
> Regards,
> Bill K.

Re: not null column with null defaults

Here's my fundamental problem...

If I have a "not null" column with no default, MySQL treats it as if it
had "default NULL".
That's great, other DBMS do have "not null default null" as well.

My problem is whenever I query metadata or something like "show columns
from some_table"; MySQL reports these columns as having an "empty
string".

Now, from my point of view, an "empty string" is not the same thing as
a "null" value.

Could someone modify MySQL so that it returns a default of NULL
whenever "strict_trans_tables" is active?


Bill Karwin wrote:
> axelsino@gmail.com wrote:
> > create table some_table
> > (id int not null auto_increment,
> > some_text varchar(20) not null,
> > other_text varchar(20) default 'aaa',
> > another_text varchar(20),
> > primary key (id)
> > ) type=innodb;
> >
> > I execute "show columns from some_table" using both ODBC, connector/J
> > and mysql command line.
> > The command line returns "some_text" as having a NULL default.
>
> I just tried your example table on my instance of MySQL 5.0.21, and it
> shows this:
>
> mysql> show columns from some_table;
> +--------------+-------------+------+-----+---------+------- ---------+
> | Field | Type | Null | Key | Default | Extra |
> +--------------+-------------+------+-----+---------+------- ---------+
> | id | int(11) | NO | PRI | NULL | auto_increment |
> | some_text | varchar(20) | NO | | | |
> | other_text | varchar(20) | YES | | aaa | |
> | another_text | varchar(20) | YES | | NULL | |
> +--------------+-------------+------+-----+---------+------- ---------+
>
> Apologies if the formatting gets messed up by proportional fonts.
>
> Anyway, notice that the "some_text" column shows NO for its nullability,
> and an empty space for its default value.
>
> So it is consistent with what you saw in ODBC and Java, and also agrees
> with the docs on the issue. Perhaps you misread the output of show
> columns in the mysql monitor?
>
> Regards,
> Bill K.

Re: not null column with null defaults

axelsino wrote:
> My problem is whenever I query metadata or something like "show columns
> from some_table"; MySQL reports these columns as having an "empty
> string".

The output of "SHOW COLUMNS" or "DESCRIBE" is unfortunately ambiguous,
because it gives no visible distinction between no default and default ''.

Have a look at the output of "SHOW CREATE TABLE tablename" instead. It
makes the difference more clear.

You can also use the INFORMATION_SCHEMA if you use MySQL 5.0 or later:

create table test.foo (
c1 char(10) not null,
c2 char(10) not null default ''
);

select table_name, column_name, is_nullable, column_default
from information_schema.columns
where table_schema = 'test';

Returns:

table_name column_name is_nullable column_default
'foo' 'c1' 'NO' null
'foo' 'c2' 'NO' ''

Regards,
Bill K.