Concatenating text literals with NULL yields NULL

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

Concatenating text literals with NULL yields NULL

Dominique Devienne
I was just surprised by this behavior, see below.
Googling it, seems like SQL Server has a setting the change the behavior in
that case.
Is this standard SQL behavior, as implemented in SQLite?
Not complaining, just asking whether I can depend on it, or not.

Thanks, --DD

C:\Users\ddevienne>sqlite3
SQLite version 3.25.3 2018-11-05 20:37:38
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(v);
sqlite> insert into t values (null);
sqlite> select v from t;

sqlite> select '('||v||')' from t;

sqlite> select typeof('('||v||')') from t;
null
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Concatenating text literals with NULL yields NULL

Hick Gunter
Documented here: https://sqlite.org/opcode.html


Concat

Add the text in register P1 onto the end of the text in register P2 and store the result in register P3. If either the P1 or P2 text are NULL then store NULL in P3.
P3 = P2 || P1
It is illegal for P1 and P3 to be the same register. Sometimes, if P3 is the same register as P2, the implementation is able to avoid a memcpy().






-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Dominique Devienne
Gesendet: Freitag, 04. Jänner 2019 10:48
An: General Discussion of SQLite Database <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Concatenating text literals with NULL yields NULL



I was just surprised by this behavior, see below.

Googling it, seems like SQL Server has a setting the change the behavior in that case.

Is this standard SQL behavior, as implemented in SQLite?

Not complaining, just asking whether I can depend on it, or not.



Thanks, --DD



C:\Users\ddevienne>sqlite3

SQLite version 3.25.3 2018-11-05 20:37:38 Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table t(v);

sqlite> insert into t values (null);

sqlite> select v from t;



sqlite> select '('||v||')' from t;



sqlite> select typeof('('||v||')') from t;

null

_______________________________________________

sqlite-users mailing list

[hidden email]<mailto:[hidden email]>

http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___________________________________________
Gunter Hick | Software Engineer | Scientific Games International GmbH<http://www.scigames.at> | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Concatenating text literals with NULL yields NULL

Jean-Luc Hainaut
In reply to this post by Dominique Devienne
On 04/01/2019 10:48, Dominique Devienne wrote:
> I was just surprised by this behavior, see below.
> Googling it, seems like SQL Server has a setting the change the behavior in
> that case.
> Is this standard SQL behavior, as implemented in SQLite?
> Not complaining, just asking whether I can depend on it, or not.
>

It's standard SQL and plain logic: if you add something unknown to some
known stuff, the result is .. unknown, that is, "null". Same with the
other SQL scalar expressions (123.4 + null --> null).

JLH


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users