Table column default set to NULL, but NULL insert query makes a "0" instead.

[Volitics]

.
Greetings!

I have got a table that has a columm that has the following settings:

Field (or name): Opponent
Type: smallint(5)
Collation: blank
Attributes: UNSIGNED
Null: Yes
Default: NULL

If I do an insert query using the below $team_id_opp variable the table new row will have a value of zero (0) instead of the value of NULL.

$team_id_opp    =    NULL;

INSERT INTO Games ( Opponent ) VALUES ('$team_id_opp')  // Value for the Opponent cell turns out to be "0" instead of NULL 

If I put single quotes or double quotes around NULL like below the table row still has a value of zero instead of NULL for the Opponent table cell value.

$team_id_opp    =    'NULL'; // Still has 0 instead of NULL
$team_id_opp    =    "NULL"; // Still has 0 instead of NULL 

If I remove the $team_id_opp variable from the script altogether and do an insert query with no variable value at all it still inserts a 0 instead of the NULL value.

Question. How can I make the Opponent column cell in a new row have the default NULL value instead of a value of 0?

Thanks.

[r937]

Question. How can I make the Opponent column cell in a new row have the default NULL value instead of a value of 0?

easy peasy

INSERT INTO Games ( Opponent ) VALUES ( NULL )

[Volitics]

.
That won't work. Some of the Opponent column cells need to have a numerical value. Other records/rows have no value for the opponent column. That's why I need to use NULL.

The Opponent column is for Division 1 baseball opponents. If the opposing team is not a Division 1 team then I have another column in the same row for those ones. For a non D1 opposing team the Opponent column cell needs to have no value or a value of NULL.

Just as a test I used the value as you suggested just to see the results.

INSERT INTO Games ( Opponent ) VALUES ( NULL )

Strangely, oddly the value, using the above query, still resulted in a cell value of 0 instead of NULL.

It's unusual that it would do that. There has got to be a simple answer.

[itmitică]

There has got to be a simple answer.

Try a little checking:

SELECT *
  FROM Games
 WHERE Opponent is NULL

and

SELECT *
  FROM Games
 WHERE Opponent = 0

Have you declared any triggers on the Games table?

Do you have an index on that column?

Are you using a test db and a production db?

Have you replaced default display values in your SQL manager?

[r937]

Just as a test I used the value as you suggested just to see the results.

INSERT INTO Games ( Opponent ) VALUES ( NULL )

Strangely, oddly the value, using the above query, still resulted in a cell value of 0 instead of NULL.

fascinating

would you kindly do a SHOW CREATE TABLE for that table

[Volitics]

.

//----
CREATE TABLE `Games` (
  `team_id` smallint(5) unsigned NOT NULL,
  `Year` year(4) NOT NULL,
  `game_id` mediumint(8) unsigned NOT NULL,
  `GameDate` int(10) unsigned NOT NULL,
  `GameOrder` tinyint(2) NOT NULL default '1',
  `Stadium` set('vs','at','neu','t','c','r','sr','cws') NOT NULL default 'vs',
  `Opponent` smallint(5) unsigned default NULL,
  `NonD1` varchar(75) NOT NULL COMMENT 'Not D1 opponent',
  `ConferenceGame` set('','*') NOT NULL,
  `DivisionalPlay` set('','*') NOT NULL,
  `Comment` varchar(75) NOT NULL COMMENT 'For footnotes',
  `Status` set('ForTesting','Tournament','Scheduled','Done','Resumed','Suspended','Postponed','Cancelled') NOT NULL,
  `Innings` tinyint(2) unsigned default NULL,
  `Outcome` set('','W','L','T') NOT NULL,
  `RunsScored` tinyint(3) unsigned default NULL,
  `OpponentScore` tinyint(3) unsigned default NULL,
  `tourn_id` smallint(5) unsigned default NULL COMMENT 'id for in season tournament',
  `TournHomeTeam` set('','Yes','No') NOT NULL,
  `TournRoadTeam` set('','Yes','No') NOT NULL,
  `TournGameNum` smallint(3) unsigned default NULL,
  `Bracket` tinyint(1) default NULL COMMENT 'CWS has two brackets',
  `Finals` set('','Yes','No') NOT NULL COMMENT 'CWS has finals',
  UNIQUE KEY `game_id` (`game_id`),
  KEY `team_id` (`team_id`),
  KEY `Year` (`Year`),
  KEY `GameDate` (`GameDate`),
  KEY `GameOrder` (`GameOrder`),
  KEY `ConferenceGame` (`ConferenceGame`),
  KEY `DivisionalPlay` (`DivisionalPlay`),
  KEY `tourn_id` (`tourn_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Schedules and game results.';
//--- 

[Antnee]

It's unrelated to the actual question, but the use of a SET field for a yes/no isn't suitable, since a SET can be both yes AND no in this case (and '' looking at your code). You would want an enum, and rather than allow '' I would allow it to be NULL instead. Even better, use a TINYINT and set 1 for yes, 0 for no, and NULL for, well, NULL.

SET should only be used where you (may) need to choose multiples as not only does it allow this, but internally it also takes up more space. An ENUM, for example, would store the values internally as incrementing numbers (0, 1, 2, 3, 4, 5, 6, 7 etc), whereas a SET stores them as follows: 1, 2, 4, 8, 16, 32, 64.

[Antnee]

Actually related to your question, I just created the table according to your code and inserted a NULL record to the Opponent row with no problems at all. Are you able to insert NULL anywhere? Can you do it with a GUI such a PHPMyAdmin, or HeidiSQL? I'm not aware of a setting for it, nor a reason why you'd want to, but I wonder if there is a server config value that disallows NULL and forces a 0 instead?

Also, while I think, how are you checking the value? Are you checking it via PHP? If you use something like the aforementioned HeidiSQL it will tell you EXACTLY what the value of the field is, and not what another language interprets it as

[r937]

antnee beat me to it -- while i was fixing errors!

i created your table, then tried this --

INSERT INTO Games ( Opponent ) VALUES ( NULL )

unfortunately, this failed because i was missing a value for some column

so i supplied a value, and tried again, and got a similar message for another column

so i tried it again... and again... and again...

eventually, this was the query that actually ran --

INSERT 
  INTO Games 
     ( team_id
     , `year`
     , game_id 
     , gamedate
     , nond1
     , conferencegame
     , divisionalplay
     , `comment`
     , `status`
     , outcome
     , tournhometeam
     , tournroadteam
     , finals
     , Opponent ) -- this is the column i was trying to fill
VALUES 
     ( 9
     , 2012
     , 37
     , UNIX_TIMESTAMP()
     , 0
     , 0
     , 0
     , 'meh'
     , 'cancelled'
     , 't'
     , 'yes'
     , 'no'
     , 'yes'
     , NULL ) -- this is the value i was trying to fill it with

you'll be happy to know that mysql ~does~ accept NULL for Opponent, and does not change it to 0

[Antnee]

I wonder if we have conflicting server configs, r397? I had no problem creating the table and immediately inserting NULL

CREATE TABLE `Games` (
`team_id` smallint(5) unsigned NOT NULL, 
`Year` year(4) NOT NULL, 
`game_id` mediumint(8) unsigned NOT NULL, 
`GameDate` int(10) unsigned NOT NULL, 
`GameOrder` tinyint(2) NOT NULL default '1', 
`Stadium` set('vs','at','neu','t','c','r','sr','cws') NOT NULL default 'vs', 
`Opponent` smallint(5) unsigned default NULL, 
`NonD1` varchar(75) NOT NULL COMMENT 'Not D1 opponent', 
`ConferenceGame` set('','*') NOT NULL, 
`DivisionalPlay` set('','*') NOT NULL, 
`Comment` varchar(75) NOT NULL COMMENT 'For footnotes', 
`Status` set('ForTesting','Tournament','Scheduled','Done','Resumed','Suspended','Postponed','Cancelled') NOT NULL, 
`Innings` tinyint(2) unsigned default NULL, `Outcome` set('','W','L','T') NOT NULL, 
`RunsScored` tinyint(3) unsigned default NULL, 
`OpponentScore` tinyint(3) unsigned default NULL, 
`tourn_id` smallint(5) unsigned default NULL COMMENT 'id for in season tournament', 
`TournHomeTeam` set('','Yes','No') NOT NULL, 
`TournRoadTeam` set('','Yes','No') NOT NULL, 
`TournGameNum` smallint(3) unsigned default NULL, 
`Bracket` tinyint(1) default NULL COMMENT 'CWS has two brackets', 
`Finals` set('','Yes','No') NOT NULL COMMENT 'CWS has finals', 
UNIQUE KEY `game_id` (`game_id`),
KEY `team_id` (`team_id`), 
KEY `Year` (`Year`), 
KEY `GameDate` (`GameDate`), 
KEY `GameOrder` (`GameOrder`), 
KEY `ConferenceGame` (`ConferenceGame`), 
KEY `DivisionalPlay` (`DivisionalPlay`), 
KEY `tourn_id` (`tourn_id`) ) 
ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Schedules and game results.';
 
INSERT INTO Games ( Opponent ) VALUES ( NULL );
/* 1 rows affected, 0 rows found. Duration for 2 queries: 0.047 sec. */

I ran that all at once via HeidiSQL

[r937]

I wonder if we have conflicting server configs, r397?

well, there has to be some kind of explanation, doesn't there?

for instance, what was the value that got assigned to these two columns, for which you failed to provide a value, and which don't have a default --

`game_id` mediumint(8) unsigned NOT NULL, -- this is also UNIQUE
`TournHomeTeam` set('','Yes','No') NOT NULL,

btw i'm using heidisql as well

[Antnee]

PHP uses what they call "loose" comparisons as default, and isn't very good at understanding types. For example, if you do this:

<?php
$foo = FALSE;
$bar = NULL;
$baz = TRUE;
 
if ($foo == FALSE)  echo "Foo is FALSE\n";
if ($foo == 0)      echo "Foo is also 0\n";
if ($foo == NULL)   echo "hang on, Foo is also NULL?\n";
if ($foo == TRUE)   echo "Foo is true\n";
if ($foo === FALSE) echo "Foo is EXACTLY FALSE\n";
if ($foo === 0)     echo "Foo is EXACTLY ZERO\n";
if ($foo === NULL)  echo "Foo is EXACTLY NULL\n";
if ($foo === TRUE)  echo "Foo is EXACTLY TRUE\n";
 
if ($bar == FALSE)  echo "Bar is FALSE\n";
if ($bar == 0)      echo "Bar is also 0\n";
if ($bar == NULL)   echo "Bar is also NULL?\n";
if ($bar == TRUE)   echo "Bar is true\n";
if ($bar === FALSE) echo "Bar is EXACTLY FALSE\n";
if ($bar === 0)     echo "Bar is EXACTLY ZERO\n";
if ($bar === NULL)  echo "Bar is EXACTLY NULL\n";
if ($bar === TRUE)  echo "Bar is EXACTLY TRUE\n";
 
if ($baz == FALSE)  echo "Baz is FALSE\n";
if ($baz == 0)      echo "Baz is also 0\n";
if ($baz == NULL)   echo "hang on, Baz is also NULL?\n";
if ($baz == TRUE)   echo "Baz is true\n";
if ($baz === FALSE) echo "Baz is EXACTLY FALSE\n";
if ($baz === 0)     echo "Baz is EXACTLY ZERO\n";
if ($baz === NULL)  echo "Baz is EXACTLY NULL\n";
if ($baz === TRUE)  echo "Baz is EXACTLY TRUE\n";
 
echo "Foo = $foo\n";
echo "Bar = $bar\n";
echo "Baz = $baz\n";

You will get this output:

Foo is FALSE 
Foo is also 0 
hang on, Foo is also NULL? 
Foo is EXACTLY FALSE 
Bar is FALSE 
Bar is also 0 
Bar is also NULL? 
Bar is EXACTLY NULL 
Baz is true 
Baz is EXACTLY TRUE
Foo = 
Bar = 
Baz = 1

Not always what you expect

You need to check EXACTLY what you expect from PHP. And don't set a variable to be equal to the NULL constant before you insert, as that WILL go in as a zero. What you'd need to do is more like this:

$var = 'NULL';
$sql = "INSERT INTO Games ( Opponent ) VALUES ( $var );";

That will translate to the exact:

INSERT INTO Games ( Opponent ) VALUES ( NULL );

that you're after

If you did this:

$var = 'NULL';
$sql = "INSERT INTO Games ( Opponent ) VALUES ( '$var' );";

This would translate in to:

INSERT INTO Games ( Opponent ) VALUES ( 'NULL' );

and that would try to insert the string "NULL" instead, but since it's a SMALLINT field it will instead fall back to the type default, which is a zero (and not the NULL that you would expect). This happens when you try to insert an invalid value when you're not running MySQL in strict mode. If you immediately ran the MySQL command:

SHOW WARNINGS;

You would see somthing like this:

Incorrect integer value: 'NULL' for column 'Opponent' at row 1

[Volitics]

Thanks r937, Antnee, itmitică for responding to my problem.

Yesterday I thought it was a MySQL issue. Today I'm starting to think that it's more of a PHP issue.

I think what I need to do is go back and make a table with just one column and then just insert one variable at a time until I figure out what's causing the problem.

Thanks again.

[Antnee]

I agree, I think it's a PHP issue. Don't worry, we've all done it Just sort out your SETs and make the ENUMs or TINYINTs (Except where you do actually need multiple options)

[Antnee]

I did actually write some code for a previous employer where you could pass in a load of PHP data, whether an object or an array, and as long as you had the same key in your array (or property name in your object) as the DB row name, it would deal with PHP types correctly, so NULL would actually insert NULL instead of 0. Technically I guess it was a form of Object Relational Mapping (ORM) and it did a really good job of properly sanitising data and stopping mistakes like this happening. It's a shame that I had to leave it behind, as I think it would've helped you no end. Although some would argue that it also allows lazy coding

[Antnee]

game_id = 0, TournHomeTeam = ''

Full dataset in CSV:

team_id,Year,game_id,GameDate,GameOrder,Stadium,Opponent,NonD1,ConferenceGame,DivisionalPlay,Comment,Status,Innings,Outcome,RunsScored,OpponentScore,tourn_id,TournHomeTeam,TournRoadTeam,TournGameNum,Bracket,Finals
0,0000,0,0,1,vs,NULL,,,,,,NULL,,NULL,NULL,NULL,,,NULL,NULL,

Are you in strict mode? I'm using the default config that comes with WAMP Server and MySQL 5.5.20

[r937]

Are you in strict mode?

i honestly don't know

i installed mysql without touching a thing, so whatever its presets are, that's what i'm using

[Antnee]

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
SHOW VARIABLES LIKE "%mode%";

The first two are blank for me. The last one shows innodb_strict_mode OFF and sql_mode blank

I don't suppose it's important, unless you really want to know why it works for one of us and not for the other. Though I couldn't insert using that query above if there was any data already in the table

[r937]

... unless you really want to know why it works for one of us and not for the other.

not really, no

i'm happy just being able to run normal queries

SELECT @@GLOBAL.sql_mode;
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

SELECT @@SESSION.sql_mode;
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

SHOW VARIABLES LIKE "%mode%";
innodb_autoinc_lock_mode  1
innodb_strict_mode        OFF
slave_exec_mode           STRICT
sql_mode                  STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[Antnee]

Knew it

Mine threw warnings, yours threw errors. So mine continued, yours did not. Knew there'd be a simple explanation You're a good boy, I'm clearly not