Manejo NULL en SQLite versus otros motores de base de datos

El objetivo es hacer que SQLite maneje NULL de una manera compatible con los estándares. Pero las descripciones en los estándares SQL sobre cómo manejar NULL parecen ambiguas. No está claro en los documentos de estándares exactamente cómo deben manejarse los NULL en todas las circunstancias.

Entonces, en lugar de seguir los documentos de estándares, se probaron varios motores SQL populares para ver cómo manejan los NULL. La idea era hacer que SQLite funcionara como todos los demás motores. Los voluntarios desarrollaron y ejecutaron un script de prueba SQL en varios RDBMS de SQL y los resultados de esas pruebas se utilizaron para deducir cómo procesaba cada motor los valores NULL. Las pruebas originales se ejecutaron en mayo de 2002. Al final de este documento se encuentra una copia del script de prueba.

SQLite se codificó originalmente de tal manera que la respuesta a todas las preguntas del cuadro siguiente sería “Sí”. Pero los experimentos realizados en otros motores SQL mostraron que ninguno de ellos funcionaba de esta manera. Entonces, SQLite se modificó para que funcione igual que Oracle, PostgreSQL y DB2. Esto implicó hacer que los NULL fueran indistintos para los propósitos de la instrucción SELECT DISTINCT y para el operador UNION en un SELECT. Los NULL siguen siendo distintos en una columna ÚNICA. Esto parece algo arbitrario, pero el deseo de ser compatible con otros motores superó esa objeción.

Es posible hacer que SQLite trate los NULL como distintos para los propósitos de SELECT DISTINCT y UNION. Para hacerlo, se debe cambiar el valor de NULL_ALWAYS_DISTINCT #define en el sqliteInt.h archivo de origen y recompilar.

Actualización 13-07-2003: Dado que este documento se redactó originalmente, algunos de los motores de base de datos probados se han actualizado y los usuarios han tenido la amabilidad de enviar correcciones al cuadro siguiente. Los datos originales mostraron una amplia variedad de comportamientos, pero con el tiempo el rango de comportamientos ha convergido hacia el modelo PostgreSQL / Oracle. La única diferencia significativa es que Informix y MS-SQL tratan los NULL como indistintos en una columna ÚNICA.

El hecho de que los valores NULL sean distintos para las columnas UNIQUE pero no sean distintos para SELECT DISTINCT y UNION sigue siendo desconcertante. Parece que los valores NULL deberían ser distintos en todas partes o en ninguna. Y los documentos de estándares SQL sugieren que los NULL deben ser distintos en todas partes. Sin embargo, al momento de escribir este artículo, ningún motor SQL probado trata los valores NULL como distintos en una instrucción SELECT DISTINCT o en una UNION.

La siguiente tabla muestra los resultados de los experimentos de manipulación NULL.

SQLite PostgreSQL Oráculo Informix DB2 MS-SQL OCELOTE
Añadiendo algo a null da null sí sí sí sí sí sí sí
Multiplicar null por cero da null sí sí sí sí sí sí sí
los nulos son distintos en una columna ÚNICA sí sí sí No (Nota 4) No sí
los nulos son distintos en SELECT DISTINCT No No No No No No No
los nulos son distintos en una UNION No No No No No No No
“CASO CUANDO null THEN 1 ELSE 0 END “es 0? sí sí sí sí sí sí sí
“null O true” es true sí sí sí sí sí sí sí
“no (null Y false)” es true sí sí sí sí sí sí sí
MySQL
3.23.41		 MySQL
4.0.16		 Pájaro de fuego SQL
En cualquier sitio		 Borland
Interbase
Añadiendo algo a null da null sí sí sí sí sí
Multiplicar null por cero da null sí sí sí sí sí
los nulos son distintos en una columna ÚNICA sí sí sí (Nota 4) (Nota 4)
los nulos son distintos en SELECT DISTINCT No No No (nota 1) No No
los nulos son distintos en una UNION (Nota 3) No No (nota 1) No No
“CASO CUANDO null THEN 1 ELSE 0 END “es 0? sí sí sí sí (Nota 5)
“null O true” es true sí sí sí sí sí
“no (null Y false)” es true No sí sí sí sí
Notas: 1. Las versiones anteriores de firebird omiten todos los NULL de SELECT DISTINCT y de UNION.
2. Datos de prueba no disponibles.
3. La versión 3.23.41 de MySQL no es compatible con UNION.
4. DB2, SQL Anywhere y Borland Interbase no permiten valores NULL en una columna ÚNICA.
5. Borland Interbase no admite expresiones CASE.


La siguiente secuencia de comandos se utilizó para recopilar información para la tabla anterior. 

-- I have about decided that SQL's treatment of NULLs is capricious and cannot be-- deduced by logic.  It must be discovered by experiment.  To that end, I have -- prepared the following script to test how various SQL databases deal with NULL.-- My aim is to use the information gather from this script to make SQLite as much-- like other databases as possible.---- If you could please run this script in your database engine and mail the results-- to me at [email protected], that will be a big help.  Please be sure to identify the-- database engine you use for this test.  Thanks.---- If you have to change anything to get this script to run with your database-- engine, please send your revised script together with your results.---- Create a test table with datacreatetable t1(a int, b int, c int);insertinto t1 values(1,0,0);insertinto t1 values(2,0,1);insertinto t1 values(3,1,0);insertinto t1 values(4,1,1);insertinto t1 values(5,null,0);insertinto t1 values(6,null,1);insertinto t1 values(7,null,null);-- Check to see what CASE does with NULLs in its test expressionsselect a,casewhen b<>0then1else0endfrom t1;select a+10,casewhennot b<>0then1else0endfrom t1;select a+20,casewhen b<>0and c<>0then1else0endfrom t1;select a+30,casewhennot(b<>0and c<>0)then1else0endfrom t1;select a+40,casewhen b<>0or c<>0then1else0endfrom t1;select a+50,casewhennot(b<>0or c<>0)then1else0endfrom t1;select a+60,case b when c then1else0endfrom t1;select a+70,case c when b then1else0endfrom t1;-- What happens when you multiple a NULL by zero?select a+80, b*0from t1;select a+90, b*c from t1;-- What happens to NULL for other operators?select a+100, b+c from t1;-- Test the treatment of aggregate operatorsselectcount(*),count(b),sum(b),avg(b),min(b),max(b)from t1;-- Check the behavior of NULLs in WHERE clausesselect a+110from t1 where b<10;select a+120from t1 wherenot b>10;select a+130from t1 where b<10OR c=1;select a+140from t1 where b<10AND c=1;select a+150from t1 wherenot(b<10AND c=1);select a+160from t1 wherenot(c=1AND b<10);-- Check the behavior of NULLs in a DISTINCT queryselectdistinct b from t1;-- Check the behavior of NULLs in a UNION queryselect b from t1 unionselect b from t1;-- Create a new table with a unique column.  Check to see if NULLs are considered-- to be distinct.createtable t2(a int, b intunique);insertinto t2 values(1,1);insertinto t2 values(2,null);insertinto t2 values(3,null);select*from t2;droptable t1;droptable t2;