Re: [Bacula-devel] Re: SQL stupidity

[Kern S. <ke...@si...>]

On Thu, 2004-09-23 at 05:00, Dan Langille wrote:
> On 22 Sep 2004 at 21:44, Matt Howard wrote:
> 
> > Looks sane on MySQL ... ( NULL ne "" ne " " )
> > See below, in case I'm asleep at the wheel.
> 
> For PostgreSQL:
> 
> > 
> > ===
> > mysql> CREATE DATABASE `test`;
> > Query OK, 1 row affected (0.02 sec)
> > 
> > mysql> use test;
> > Database changed
> > 
> > mysql> CREATE TABLE Filename (
> >     ->   FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
> >     ->   Name BLOB NOT NULL,
> >     ->   PRIMARY KEY(FilenameId),
> >     ->   INDEX (Name(30))
> >     ->   );
> > Query OK, 0 rows affected (0.01 sec)
> 
> test=# CREATE TABLE Filename (
> test(#    FilenameId serial not null,
> test(#    Name text NOT NULL,
> test(#    PRIMARY KEY(FilenameId)
> test(#    );
> NOTICE:  CREATE TABLE will create implicit sequence "filename_filenameid_seq" for "serial" column "filename.filenameid"
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "filename_pkey" for table "filename"
> CREATE TABLE
> test=# create index filename_name on filename(name);
> CREATE INDEX
> test=#
> 
> > mysql> INSERT INTO Filename ( Name ) VALUES ( "" );
> > Query OK, 1 row affected (0.00 sec)
> > 
> > mysql> INSERT INTO Filename ( Name ) VALUES ( NULL );
> > ERROR 1048: Column 'Name' cannot be null    (Sanity test -MH)
> > 
> > mysql> INSERT INTO Filename ( Name ) VALUES ( " " );
> > Query OK, 1 row affected (0.00 sec)
> > 
> > mysql> INSERT INTO Filename ( Name ) VALUES ( "foo" );
> > Query OK, 1 row affected (0.00 sec)
> > 
> > mysql> SELECT * FROM Filename
> >     -> ;
> > +------------+------+
> > | FilenameId | Name |
> > +------------+------+
> > |          1 |      |
> > |          2 |      |
> > |          3 | foo  |
> > +------------+------+
> > 3 rows in set (0.00 sec)
> 
> test=# INSERT INTO Filename ( Name ) VALUES ( '' );
> INSERT 15512954 1
> test=# INSERT INTO Filename ( Name ) VALUES ( NULL );
> ERROR:  null value in column "name" violates not-null constraint
> test=# INSERT INTO Filename ( Name ) VALUES ( ' ' );
> INSERT 15512955 1
> test=# INSERT INTO Filename ( Name ) VALUES ( 'foo' );
> INSERT 15512956 1
> test=# SELECT * FROM Filename;
>  filenameid | name
> ------------+------
>           1 |
>           3 |
>           4 | foo
> (3 rows)
> 
> 
> > mysql> SELECT * FROM Filename WHERE Name = "";
> > +------------+------+
> > | FilenameId | Name |
> > +------------+------+
> > |          1 |      |
> > +------------+------+
> > 1 row in set (0.02 sec)
> 
> test=# SELECT * FROM Filename WHERE Name = '';
>  filenameid | name
> ------------+------
>           1 |
> (1 row)
> 
> 
> > mysql> SELECT * FROM Filename WHERE Name = NULL;
> > Empty set (0.00 sec)
> 
> test=# SELECT * FROM Filename WHERE Name = NULL;
>  filenameid | name
> ------------+------
> (0 rows)
> 
> > 
> > mysql> SELECT * FROM Filename WHERE Name = " ";
> > +------------+------+
> > | FilenameId | Name |
> > +------------+------+
> > |          2 |      |
> > +------------+------+
> > 1 row in set (0.00 sec)
> 
> test=# SELECT * FROM Filename WHERE Name = ' ';
>  filenameid | name
> ------------+------
>           3 |
> (1 row)

Ah I should have looked a bit further in this thread. Thanks for
confirming this for PostgreSQL too.  Now I can sleep better at night not
worrying about this ... :-)