una - La consulta externa SQLite está devolviendo resultados que no se encuentran en la consulta interna

sqlite android tutorial español

Question

Me pregunto si alguien se ha topado con un caso en SQLite (3.7.4) donde una consulta devolvería un conjunto de resultados, y cuando se convierte en una subconsulta, los resultados son completamente diferentes. Encontré el problema en una consulta más compleja, pero aquí hay un ejemplo más simple que demuestra el mismo comportamiento:

Configuración de la base de datos

CREATE TABLE "test" ("letter" VARCHAR(1) PRIMARY KEY, "number" INTEGER NOT NULL);
INSERT INTO "test" ("letter", "number") VALUES(''b'', 1);
INSERT INTO "test" ("letter", "number") VALUES(''a'', 2);
INSERT INTO "test" ("letter", "number") VALUES(''c'', 2);

Consulta inicial:

SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT 1;

Esto devuelve a|2 , la segunda fila de los resultados como era de esperar dado que estamos ordenando la letra y luego el número. Sin embargo, esto es lo que no esperaba:

Consulta inicial como una subconsulta:

SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT 1) AS "test";

Esto devuelve 1 , que no es en absoluto lo que esperaba. Lo que esperaba ver es 2 . Mi comprensión de cómo funciona una subconsulta es que debería devolver los mismos resultados que si la consulta interna se materializara y la consulta externa se aplicara contra esos resultados (aunque me doy cuenta de que las bases de datos llegan a longitudes extremas para no materializar los resultados hasta que sea necesario) .

¿Mi suposición es incorrecta? Probé la misma consulta en PostgreSQL y MySQL y funcionó como esperaba (es decir, devolvió 2 ). Lo que me parece es que he encontrado un error en cómo SQLite colapsa las subconsultas, pero no estoy seguro.

Solo para reiterar, el ejemplo anterior se simplifica de lo que estoy haciendo en realidad. No solo uso DISTINCT en una subconsulta que devuelve una sola fila, sino que devuelve muchas filas, algunas de las cuales tienen el mismo valor para una columna, de ahí mi necesidad de DISTINCT. El ejemplo anterior es la manera más simple que se me ocurrió para demostrar lo que está sucediendo.

EDITAR: pude desactivar el plegado incorrecto de la subconsulta al agregar OFFSET 0 a la consulta interna, por ej.

SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT 1 OFFSET 0) AS "test";

Voy a informar esto como un error a través de la lista de correo SQLite, y esto como una solución alternativa.

Answer 1

También puedo verificar que ocurra con el complemento SQLite para Firefox.

Si te sirve de consuelo, esta forma funciona:

SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test"
ORDER BY "letter", "number") AS "test" ORDER BY "letter" LIMIT 1;

Creo que la especificación SQLite ignora la cláusula LIMIT en consultas internas y la migra fuera. Sin el límite:

SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test"
ORDER BY "letter", "number") AS "test";

Vuelve

1
2
(2 rows)

Es interesante notar que esto también devuelve los resultados correctos

SELECT number FROM (SELECT letter, number FROM test
ORDER BY letter, number LIMIT 1) AS test;

Los dos planes se pueden comparar usando EXPLAIN.
DESCRIBE agrega muchas operaciones, alinea y optimiza la consulta interna (incorrectamente).