diciembre 28, 2005

Nulos e Integridad Referencial

Trabajando en un proyecto reciente me encontré con este detalle que parece tener Firebird (al menos en las versiones 1.5.2 y hasta el 1.5.3 RC3 que es la que tengo en mi laptop). Resultó ser ese tipo de cosas que te encuentras y que te pueden tomar muchas horas en dilucidar en donde es que radica el problema y resulta ser algo risorio y que se le ha escapado a alguien en el desarrollo de Firebird.

Para empezar he de explicar el escenario donde me ocurrió esto, tengo una tabla llamada GESTORES donde a cada gestor se le puede o no asociar un usuario del sistema, entonces hay registros que tienen un valor en este campo y otros en los que simplemente se queda en nulo. Dado esto, en teoría esta consulta me debería dar todos los usuarios que no estan asociados a algún gestor:


Sin embargo, no fué así, esta consulta no me devolvía ni un solo valor aunque yo sabía que si habian por lo menos 15 usuarios que no estaban asociados a algún gestor.

De repente algun Dios olimpico iluminó mi mente y me llego la idea de que tal vez el hecho de que hubiera valores nulos en la tabla de gestores influiría, así que le agregué una pequeña condición para ignorar los valores nulos de la relación:


Con esto ya funcionó sin ningún problema el programa, ahora si me devolvía los 15 registros de los usuarios que no estaban asociados a gestores.

Pero la duda me aquejaba acerca de si esto era una constante en las relaciones WHERE NOT IN asi que me leí el magnífico artículo de Firebird Nulls Guide del sitio de Firebird, en el cual se explica mucho acerca del comportamiento de los valores nulos en las comparaciones y operaciones con Firebird, pero nada que me diera un Norte sobre porque mi consulta especificamente tenia un comportamiento tan errático. Probé con otras tablas de la misma base de datos y solo con estas dos me devolvia un conjunto vacío. Al rebuscar más meticulosamente cai en la cuenta de que la diferencia radicaba en que no había definido una regla de integridad referencial entre los campos de ambas relaciones, asi que raudo y veloz procedí a crearla:


Y una vez hecho esto ya la primer consulta funciona perfectamente. Osea que al final de cuentas el problema es que la combinación de comparación del tipo WHERE [NOT] IN SELECT falla cuando en el subselect existen valores nulos y no hay una relación de integridad entre ambas relaciones, esto pareciera trivial, pero cuando en un sistema tratas de explotar al máximo los datos que tienes las consultas de este tipo suelen ser muy comunes y si tienes un sistema de consultas dinámicas o algun experto programado que haga estas consultas puede caer muy fácilmente en estos casos y no ser validado.

Entonces como conclusión de todo esto podemos emitir la siguiente sentencia:

¡Cuidado con Firebird! porque la combinación de comparación del tipo WHERE [NOT] IN SELECT falla cuando en el subselect existen valores nulos y no hay una relación de integridad entre ambas relaciones.


Pueden hacer sus pruebas en sus propias configuraciones y ver que otras implicaciones tiene este detalle, esperemos que esto se arregle pronto.

8 comentarios:

  1. Interesante, aunque sospecho que no falta en realidad una relación (foreign key) entre las tablas. Quizas lo que haga falta es un índice por el campo que se está relacionando.

    En todo caso, efectivamente parece un bug.

    ¿Lo has reportado ya?

    Saludos.

    ResponderEliminar
  2. Cierto Juan Antonio, despues de haber publicado este artículo me di cuenta de lo que dices que con solo definir un índice también funciona la primera consulta.

    Lo publiqué en una lista de correos pero ahora lo haré ya mas detallado en la lista de soporte de Firebird, esperemos que se le dé seguimiento

    Saludos y felíz año

    ResponderEliminar
  3. Anónimo11:11 a. m.

    Muy interesante y muy bien explicado, creo que a mas de alguien le va a ser de utilidad.

    ResponderEliminar
  4. Hola.
    Resulta que en MS SQL Server tambien sucede lo mismo.
    La diferencia es que en este caso el problema no se soluciona con un indice
    ni con la definicion de una clave foranea .

    Saludos.... Sigo investigando

    ResponderEliminar
  5. Saludos Kalimero,

    Me pareció curioso que publicaras que ese detalle tambien se presenta en SQL Server y lo probé en la versión de MySQL que utilizo para probar algunos sistemas hechos en PHP y encontre que en el caso de MySQL 4.1.12 la consulta que no funciona es la misma situación que describí en el articulo pero solo cuando se utiliza un WHERE .. NOT IN SELECT .. , cuando no usas el NOT funciona perfectamente; esta fué la consulta que utilicé en MySQL con el mismo comportamiento errático que Firebird:

    select *
    from clientes c
    where c.codcliente not in
    (select codcliente from facturas)

    ¿Podrías comentarnos que versión de SQL Server evaluaste??... ¿Alguien más podría evaluar estos casos en otros motores diferentes, Oracle, PostgreSQL, Informix, etc?

    ResponderEliminar
  6. Pues concretamente las pruebas las realice sobre
    Microsoft SQL Server 2000 versión 8.00.194

    Saludos

    ResponderEliminar
  7. Pues concretamente las pruebas las realice sobre
    Microsoft SQL Server 2000 versión 8.00.194

    Saludos

    ResponderEliminar
  8. Anónimo1:15 p. m.

    Un poco pasado de fecha, pero...
    La clave está en la frase "NULL means UNKNOWN". Por lo tanto estás quedandote con los usuarios que no estén en un conjunto de valores, de los cuales algunos son no conocidos. Como no se conocen, no se puede decir que no sea uno de ellos. Por ende, la consulta te debe dar vacía.
    De hecho, el bug se encuentra cuando se indexa la columna y devuelve datos. La Firebird Nulls Guide menciona que este bug se corrige en la version 2.0 así que ojo!!

    ResponderEliminar