Bug? Empty string stored as NULL?

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Bug? Empty string stored as NULL?

Marc L. Allen
Is that expected?  To me, '' is different than NULL.

create table a
(
                a text
);

insert into a select  '';
select * from a;

Is there a way to store an empty string?

Marc

--
******************************************************************
*                             *                                  *
* Marc L. Allen               *  "... so many things are         *
*                             *  possible just as long as you    *
* Outsite Networks, Inc.      *  don't know they're impossible." *
* (757) 853-3000 #215         *                                  *
*                             *                                  *
* [hidden email] *       -- The Phantom Tollbooth   *
*                             *                                  *
******************************************************************
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug? Empty string stored as NULL?

Puneet Kishor-2

On Mar 8, 2012, at 1:48 PM, Marc L. Allen wrote:

> Is that expected?  To me, '' is different than NULL.
>
> create table a
> (
>                a text
> );
>
> insert into a select  '';
> select * from a;
>
> Is there a way to store an empty string?
>


works for me

punkish@mumbai ~$sqlite3
SQLite version 3.7.10 2012-01-16 13:28:40
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t (a TEXT NOT NULL);
sqlite> INSERT INTO t SELECT '';
sqlite> SELECT * FROM t;

sqlite> SELECT Count(*) FROM t;
1
sqlite>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug? Empty string stored as NULL?

Pavel Ivanov-2
In reply to this post by Marc L. Allen
Where do you see NULL? I see empty string.

SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a (t text);
sqlite> insert into a select '';
sqlite> .nullvalue NULL
sqlite> select * from a;

sqlite>


Pavel


On Thu, Mar 8, 2012 at 2:48 PM, Marc L. Allen
<[hidden email]> wrote:

> Is that expected?  To me, '' is different than NULL.
>
> create table a
> (
>                a text
> );
>
> insert into a select  '';
> select * from a;
>
> Is there a way to store an empty string?
>
> Marc
>
> --
> ******************************************************************
> *                             *                                  *
> * Marc L. Allen               *  "... so many things are         *
> *                             *  possible just as long as you    *
> * Outsite Networks, Inc.      *  don't know they're impossible." *
> * (757) 853-3000 #215         *                                  *
> *                             *                                  *
> * [hidden email] *       -- The Phantom Tollbooth   *
> *                             *                                  *
> ******************************************************************
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug? Empty string stored as NULL?

Roger Binns
In reply to this post by Marc L. Allen
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A good rule of thumb is that if you claim a bug in another component you
are almost certainly wrong.

On 08/03/12 11:48, Marc L. Allen wrote:
> Is that expected?  To me, '' is different than NULL.

They are indeed different.

> Is there a way to store an empty string?

Yes, store an empty string.

sqlite> create table a(a text);
sqlite> insert into a select '';
sqlite> insert into a select null;
sqlite> select * from a;


sqlite> select typeof(a) from a;
text
null
sqlite> select 'X' || a || 'Y' from a;
XY

sqlite>


Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9ZDqoACgkQmOOfHg372QSsAgCgjkuP3HrMs1eQA9IznpTEMYpw
KTsAoMsDH9DONdp0eJJn82TWW5ZtQeT1
=nrBP
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug? Empty string stored as NULL?

Luuk
In reply to this post by Marc L. Allen
On 08-03-2012 20:48, Marc L. Allen wrote:

> Is that expected?  To me, '' is different than NULL.
>
> create table a
> (
>                 a text
> );
>
> insert into a select  '';
> select * from a;
>
> Is there a way to store an empty string?
>

insert into a values(null);

.nullvalue NULL
select * from a;
a|length(a)
|0
NULL|NULL


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug? Empty string stored as NULL?

Marc L. Allen
In reply to this post by Roger Binns
> A good rule of thumb is that if you claim a bug in another component
> you are almost certainly wrong.

I don't have the command line version on this machine.  I use Navicat Lite, and it apparently treats an empty string as a NULL when displaying.

I always check my own code.  I didn't expect Navicat to make such an error.

Sorry.. and thanks.

Marc
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug? Empty string stored as NULL?

Roger Binns
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 08/03/12 11:59, Marc L. Allen wrote:
> I use Navicat Lite, and it apparently treats an empty string as a NULL
> when displaying.

It is far more likely to display the empty string as an empty string and
to also display null as an empty string (by default) otherwise the nulls
will litter your output which often isn't helpful.  The SQLite shell does
exactly the same thing by default.

You can however tell the SQLite shell to display null as a string of your
choice, but then you have the problem of telling the difference between
that and a string of the same value.  Navicat probably has a similar
setting somewhere.

Incidentally my Python SQLite wrapper includes a shell, which you can use
without knowing anything about Python:

  http://apidoc.apsw.googlecode.com/hg/shell.html

One of the things it does is to colour output which means you could tell
the difference between the null value (shown in red) and a string (shown
in yellowish).  You can also tell the difference between the integer 3 and
the string "3" due to the colour.  Makes life a lot easier.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9ZQ1kACgkQmOOfHg372QQbdACg1CrjbyGXu0UaZX+DzFDD+mCY
/ssAoIct31OJ5JDZRilBuPtN9+6x9cro
=066G
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users