uso - Probar la equivalencia de consultas SQL

exist en sql

Question

¿Cómo podría probar que dos consultas son funcionalmente equivalentes, por ejemplo, siempre devolverán el mismo conjunto de resultados?

Como tenía una consulta específica en mente cuando estaba haciendo esto, terminé haciendo lo que @dougman sugirió, más del 10% de las filas en las tablas afectadas y la comparación de los resultados, asegurando que no había resultados fuera de lugar.

Answer 1

Esto me suena como un problema completo de NP. No estoy seguro de que haya una forma segura de probar este tipo de cosas

Answer 2

Los proveedores de DBMS han estado trabajando en esto durante mucho, mucho tiempo. Como dijo Rik, es probable que sea un problema difícil de resolver, pero no creo que se haya realizado ningún análisis formal sobre la compleción NP del espacio problemático.

Sin embargo, su mejor opción es aprovechar su DBMS tanto como sea posible. Todos los sistemas DBMS traducen SQL en algún tipo de plan de consulta. Puede utilizar este plan de consulta, que es una versión resumida de la consulta, como un buen punto de partida (el DBMS hará MUCHA optimización, aplanando las consultas en modelos más viables).

NOTA: el DBMS moderno usa un analizador "basado en costos" que no es determinista entre las actualizaciones de estadísticas, por lo que el planificador de consultas, con el tiempo, puede cambiar el plan de consultas para consultas idénticas.

En Oracle (dependiendo de su versión), puede decirle al optimizador que cambie del analizador basado en el costo al analizador basado en la regla determinista (esto simplificará el análisis del plan) con una sugerencia SQL, por ej.

SELECT /*+RULE*/ FROM yourtable

El optimizador basado en reglas ha quedado obsoleto desde 8i, pero todavía se mantiene alrededor de 10 g (no sé si 11). Sin embargo, el analizador basado en reglas es mucho menos sofisticado: la tasa de error es potencialmente mucho más alta.

Para una lectura adicional de una naturaleza más genérica, IBM ha sido bastante prolífico con sus patentes de optimización de consultas. Aquí, en un método para convertir SQL a un "plan abstracto", es un buen punto de partida: http://www.patentstorm.us/patents/7333981.html

Answer 3

Tu no

Si necesita un alto nivel de confianza de que un cambio en el rendimiento, por ejemplo, no ha cambiado el resultado de una consulta, entonces pruébelo.

Si necesita un nivel realmente alto de confianza ... entonces errrm, pruébelo aún más.

El nivel masivo de las pruebas no es tan difícil de improvisar para una consulta SQL. Escriba un proceso que iterará alrededor de un conjunto grande / completo de posibles parámetros, y llame a cada consulta con cada conjunto de parámetros, y escriba los resultados en las tablas respectivas. Compara las dos tablas y ahí lo tienes.

No es exactamente científico, supongo que fue la pregunta del OP, pero no conozco un método formal para probar la equivalencia.

Answer 4

Quizás podría dibujar (a mano) su consulta y los resultados usando Diagramas de Venn , y ver si producen el mismo diagrama. Los diagramas de Venn son buenos para representar conjuntos de datos, y las consultas SQL funcionan en conjuntos de datos. Dibujar un Diagrama de Venn puede ayudarlo a visualizar si 2 consultas son funcionalmente equivalentes.

Answer 5

Esto es bastante fácil de hacer.

Supongamos que sus consultas se llaman a y b

a menos b

debería darle un juego vacío. Si no es así a continuación, las consultas devuelven conjuntos diferentes, y el conjunto de resultados muestra las filas que son diferentes.

entonces hazlo

b menos a

eso debería darte un juego vacío. Si lo hace, las consultas devuelven los mismos conjuntos. si no está vacío, las consultas son diferentes en algún aspecto, y el conjunto de resultados muestra las filas que son diferentes.

Answer 6

Esto hará el truco. Si esta consulta devuelve cero filas, las dos consultas devuelven los mismos resultados. Como beneficio adicional, se ejecuta como una sola consulta, por lo que no tiene que preocuparse por establecer el nivel de aislamiento para que los datos no cambien entre dos consultas.

select * from ((<query 1> MINUS <query 2>) UNION ALL (<query 2> MINUS <query 1>))

Aquí hay una práctica secuencia de comandos de shell para hacer esto:

#!/bin/sh
CONNSTR=$1
echo query 1, no semicolon, eof to end:; Q1=`cat`
echo query 2, no semicolon, eof to end:; Q2=`cat`
T="(($Q1 MINUS $Q2) UNION ALL ($Q2 MINUS $Q1));"
echo select ''count(*)'' from $T | sqlplus -S -L $CONNSTR

Answer 7

¡CUIDADOSO! La "equivalencia" funcional a menudo se basa en los datos, y puede "probar" la equivalencia de 2 consultas al comparar los resultados para muchos casos y aún estar equivocados una vez que los datos cambian de cierta manera .

Por ejemplo:

SQL> create table test_tabA
(
col1 number
)
Table created.
SQL> create table test_tabB
(
col1 number
)
Table created.
SQL> -- insert 1 row
SQL> insert into test_tabA values (1)
1 row created.
SQL> commit
Commit complete.
SQL> -- Not exists query:
SQL> select * from test_tabA a
where not exists
(select ''x'' from test_tabB b
where b.col1 = a.col1)
COL1
----------
1
1 row selected.
SQL> -- Not IN query:
SQL> select * from test_tabA a
where col1 not in
(select col1
from test_tabB b)
COL1
----------
1
1 row selected.
-- THEY MUST BE THE SAME!!! (or maybe not...)
SQL> -- insert a NULL to test_tabB
SQL> insert into test_tabB values (null)
1 row created.
SQL> commit
Commit complete.
SQL> -- Not exists query:
SQL> select * from test_tabA a
where not exists
(select ''x'' from test_tabB b
where b.col1 = a.col1)
COL1
----------
1
1 row selected.
SQL> -- Not IN query:
SQL> select * from test_tabA a
where col1 not in
(select col1
from test_tabB b)
**no rows selected.**

Answer 8

Lo mejor que puede hacer es comparar las 2 salidas de consulta basadas en un conjunto dado de entradas buscando cualquier diferencia. Decir que siempre devolverán los mismos resultados para todas las entradas realmente depende de los datos.

Para Oracle, uno de los mejores, si no los mejores enfoques (muy eficiente) está aquí ( Ctrl + F Comparando el contenido de dos tablas):
http://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15asktom-084959.html

Lo que se reduce a:

select c1,c2,c3,
count(src1) CNT1,
count(src2) CNT2
from (select a.*,
1 src1,
to_number(null) src2
from a
union all
select b.*,
to_number(null) src1,
2 src2
from b
)
group by c1,c2,c3
having count(src1) <> count(src2);

Answer 9

Verifique realmente Cosette: verifica (con una prueba) si 2 consultas SQL son equivalentes y ejemplos de contador cuando no son equivalentes. Es la única manera de estar absolutamente seguro, bueno casi;) Incluso puede lanzar 2 consultas en su sitio web y verificar la equivalencia (formal) de inmediato.

Enlace a Cosette: http://cosette.cs.washington.edu/

Enlace al artículo que da una buena explicación de cómo funciona Cosette: https://medium.com/@uwdb/introducing-cosette-527898504bd6

Si todo lo que desea es solo una solución práctica rápida, también puede verificar esta respuesta de : [sql - verifique si dos selecciones son iguales]