not null column with null defaults

[axelsino]

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

[Bill Karwin]

ax******@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_ SUBSTITUTION

Regards,
Bill K.

[axelsino]

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:

ax******@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_ SUBSTITUTION

Regards,
Bill K.

[Bill Karwin]

ax******@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:

mysqlshow 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.

[axelsino]

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

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

mysql>
mysql>
mysqlshow 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)

mysqlshow 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:

ax******@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:

mysqlshow 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.

[axelsino]

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:

ax******@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:

mysqlshow 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.

[Bill Karwin]

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.