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.