1gZSptHt

1####################################
2#                                  #
3#  Example queries to demonsrate   #
4#  how SELECTing a NULL value is   #
5#  converting it into a 0 during a #
6#  INSERT...SELECT statement       #
7#                                  #
8####################################
9
10# recreate an empty example price table
11# to make sure our example is fresh, no old data
12DROP TABLE IF EXISTS `price`;
13CREATE TABLE `price`
14(
15    `note` TEXT,
16    `price` FLOAT NULL
17);
18
19# insert 2 values, an actual number and a NULL value,
20# label them accordingly
21INSERT INTO `price` VALUES
22    ( "100 number", 100 ),
23    ( "NULL row", NULL );
24
25# again recreate the test table,
26# where we'll see the some of the NULL values become 0
27# THERE IS NO DEFAULT VALUE BEING SET AND WE'RE SAYING NOT NULL
28DROP TABLE IF EXISTS `test`;
29CREATE TABLE `test`
30(
31    `note` TEXT,
32    `test` FLOAT NOT NULL
33);
34
35# Insert using a SELECT, but we're selecting fixed values
36# we're not selecting from another table
37# this converts the NULL value into a 0
38INSERT INTO `test`
39SELECT "SELECT NULL", NULL;
40
41# Now select all the values from the price table
42# which includes row with a NULL price, which is converted into 0
43INSERT INTO `test`
44SELECT `note`, `price` from `price`;
45
46# now try and insert a NULL _without_ using select first,
47# this fails as I would expect
48INSERT INTO `test`
49VALUES ( "VALUES NULL", NULL );
50
51# Error recieved:
52# [Err] 1048 - Column 'test' cannot be null