Bacula

no you can't in oracle a empty string is equal to NULL -:)

g8

meno

On Wed, 2004-09-22 at 23:43, Meno Abels wrote:
> no you can't in oracle a empty string is equal to NULL -:)
> 
> g8
> 
> meno

Oh, cool. So much for SQL compatibility. Well, since we don't yet
support Oracle, I won't worry about it yet, but I will worry about MySQL
and PostgreSQL. SQLite seems to function as I would expect, handling
zero length strings quite nicely.

Kern Sibbald wrote:

> Hello Dan,
> 
> When I first wrote Bacula, I wasn't too sure how SQL worked (still have
> certain problems), so when it came to empty filenames rather than
> dealing with NULLs and other non-compatible horrors of SQL, I simply
> created a filename with a single space. It works really nice, except it
> isn't too cool if the filename really has a single space.
> 
> Now my question: can I safely create a zero length (non-NULL) string in
> all databases so I can get rid of this silly single space kludge and
> replace it by a zero length string?
> 
> I could replace it by a NULL, but that requires some special handling.
> 
> Best regards, Kern
> 

Looks sane on MySQL ... ( NULL ne "" ne " " )
See below, in case I'm asleep at the wheel.

===
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)

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)

mysql> SELECT * FROM Filename WHERE Name = "";
+------------+------+
| FilenameId | Name |
+------------+------+
|          1 |      |
+------------+------+
1 row in set (0.02 sec)

mysql> SELECT * FROM Filename WHERE Name = NULL;
Empty set (0.00 sec)

mysql> SELECT * FROM Filename WHERE Name = " ";
+------------+------+
| FilenameId | Name |
+------------+------+
|          2 |      |
+------------+------+
1 row in set (0.00 sec)
===

-- 
Matt Howard <mhoward@...>

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)

-- 
Dan Langille : http://www.langille.org/
BSDCan - The Technical BSD Conference - http://www.bsdcan.org/

Dan Langille wrote:
> 
> 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)
> 
> 

I'm curious, did that failed INSERT with the NULL increment the filenameid
counter?

-- 
Matt Howard <mhoward@...>

On Thu, 2004-09-23 at 04:44, Matt Howard wrote:
> Kern Sibbald wrote:
> 
> > Hello Dan,
> > 
> > When I first wrote Bacula, I wasn't too sure how SQL worked (still have
> > certain problems), so when it came to empty filenames rather than
> > dealing with NULLs and other non-compatible horrors of SQL, I simply
> > created a filename with a single space. It works really nice, except it
> > isn't too cool if the filename really has a single space.
> > 
> > Now my question: can I safely create a zero length (non-NULL) string in
> > all databases so I can get rid of this silly single space kludge and
> > replace it by a zero length string?
> > 
> > I could replace it by a NULL, but that requires some special handling.
> > 
> > Best regards, Kern
> > 
> 
> Looks sane on MySQL ... ( NULL ne "" ne " " )
> See below, in case I'm asleep at the wheel.
> 
> ===
> 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)
> 
> 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)
> 
> mysql> SELECT * FROM Filename WHERE Name = "";
> +------------+------+
> | FilenameId | Name |
> +------------+------+
> |          1 |      |
> +------------+------+
> 1 row in set (0.02 sec)
> 
> mysql> SELECT * FROM Filename WHERE Name = NULL;
> Empty set (0.00 sec)
> 
> mysql> SELECT * FROM Filename WHERE Name = " ";
> +------------+------+
> | FilenameId | Name |
> +------------+------+
> |          2 |      |
> +------------+------+
> 1 row in set (0.00 sec)
> ===

Thanks for confirming that this works correctly on MySQL. I confirmed it
for SQLite, and I'd be *really* surprised if if doesn't work correctly
on PostgreSQL too, since they are very careful about such things ...

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 ... :-)

On Thu, September 23, 2004 5:44, Matt Howard said:

> mysql> SELECT * FROM Filename WHERE Name = NULL;
> Empty set (0.00 sec)
>

Just a sidenote: that query returns always 0 rows, as NULL cannot be
compared to anything, not even to "an another" null. No matter how many
rows there were with NULL Filename, the result is Empty set.

Correct SQL:

SELECT * FROM Filename WHERE Name IS NULL;

On 25 Sep 2004 at 3:24, Jari Fredriksson wrote:

> On Thu, September 23, 2004 5:44, Matt Howard said:
> 
> > mysql> SELECT * FROM Filename WHERE Name = NULL;
> > Empty set (0.00 sec)
> >
> 
> Just a sidenote: that query returns always 0 rows, as NULL cannot be
> compared to anything, not even to "an another" null. No matter how many
> rows there were with NULL Filename, the result is Empty set.
> 
> Correct SQL:
> 
> SELECT * FROM Filename WHERE Name IS NULL;

A good catch.  Thank you.

I've redone the test on PostgreSQL.

test=# SELECT * FROM Filename WHERE Name IS NULL;
 filenameid | name
------------+------
(0 rows)

test=#
-- 
Dan Langille : http://www.langille.org/
BSDCan - The Technical BSD Conference - http://www.bsdcan.org/

+-le 22/09/2004 23:43 +0200, Meno Abels =E9crivait :
| no you can't in oracle a empty string is equal to NULL -:)

I think you used MySQL too much, empty string and NULL are not equal in any
SQL compliant RDBMS. in fact, NULL !=3D NULL, as NULL is an the undefined =
value.

--=20
Mathieu Arnold

+-le 25/09/2004 11:59 -0400, Dan Langille =E9crivait :
| On 25 Sep 2004 at 17:34, Mathieu Arnold wrote:
|=20
|> +-le 22/09/2004 23:43 +0200, Meno Abels =E9crivait :
|> | no you can't in oracle a empty string is equal to NULL -:)
|>=20
|> I think you used MySQL too much, empty string and NULL are not equal
|> in any SQL compliant RDBMS. in fact, NULL !=3D NULL, as NULL is an the
|> undefined value.=20
|=20
| The point was that ORACLE treats an empty string as NULL.

Oh, you're saying that I should not read my mail after a day of tech support
? :-/

--=20
Mathieu Arnold

On 25 Sep 2004 at 17:34, Mathieu Arnold wrote:

> +-le 22/09/2004 23:43 +0200, Meno Abels =E9crivait :
> | no you can't in oracle a empty string is equal to NULL -:)
> 
> I think you used MySQL too much, empty string and NULL are not equal
> in any SQL compliant RDBMS. in fact, NULL !=3D NULL, as NULL is an the
> undefined value. 

The point was that ORACLE treats an empty string as NULL.
-- 
Dan Langille : http://www.langille.org/
BSDCan - The Technical BSD Conference - http://www.bsdcan.org/

On 25 Sep 2004 at 18:02, Mathieu Arnold wrote:

> +-le 25/09/2004 11:59 -0400, Dan Langille =E9crivait :
> | On 25 Sep 2004 at 17:34, Mathieu Arnold wrote:
> | 
> |> +-le 22/09/2004 23:43 +0200, Meno Abels =E9crivait :
> |> | no you can't in oracle a empty string is equal to NULL -:)
> |> 
> |> I think you used MySQL too much, empty string and NULL are not equal
> |> in any SQL compliant RDBMS. in fact, NULL !=3D NULL, as NULL is an th=
e
> |> undefined value. 
> | 
> | The point was that ORACLE treats an empty string as NULL.
> 
> Oh, you're saying that I should not read my mail after a day of tech sup=
port
> ? :-/

By all means, read it.  Just don't reply!  ;)

I've regretted more than one early morning post.
-- 
Dan Langille : http://www.langille.org/
BSDCan - The Technical BSD Conference - http://www.bsdcan.org/