Android Question Equivalence of 2 SQL Commands without physically running them on SAME table and comparing results

[beelze69]

Hi,

Is there a logic to check if two Sql Queries would yield same result.

For example:

We know that

Query1: Select Name, Cadre, Age, Salary FROM myTable WHERE Name ='Alice' AND Cadre in ('A','B','C','D') and AGE <=35 and Salary >=40000

Query2: SELECT Name,Cadre, Age, Salary FROM myTable WHERE Name='Alice' AND (Cadre ='A' or Cadre ='B' or Cadre = 'C' or Cadre='D') AND AGE<=35 AND SALARY>=40000

- would yield SAME result if both are run on the SAME table.

Now is there a way to find this WITHOUT ACTUALLY RUNNING THE QUERIES ON THE TABLES and comparing their results ?

Meaning, I want to develop a function which would be used like:

if isEquivalent(Query1,Query2) then
Msgbox("Both the functions would return same result")
else
Msgbox("They are not equivalent queries")
end if

Note:
The above function isEquivalent must NOT make use of any table to check this since we (humans) do not need a table to check that they will yield the same result ! Looking at the query itself we know that it will give the SAME result when run on the SAME table...


Ps. help !

Thanks.

 

[mangojack]

Is this of any use .. It appears the might be an API you could use . http://cosette.cs.washington.edu/ .

ps: I'm scratching my head wondering why you need this .. but obviously theres a reason

 

[RB Smissaert]

Hi,

Is there a logic to check if two Sql Queries would yield same result.

For example:

We know that

Query1: Select Name, Cadre, Age, Salary FROM myTable WHERE Name ='Alice' AND Cadre in ('A','B','C','D') and AGE <=35 and Salary >=40000

Query2: SELECT Name,Cadre, Age, Salary FROM myTable WHERE Name='Alice' AND (Cadre ='A' or Cadre ='B' or Cadre = 'C' or Cadre='D') AND AGE<=35 AND SALARY>=40000

- would yield SAME result if both are run on the SAME table.

Now is there a way to find this WITHOUT ACTUALLY RUNNING THE QUERIES ON THE TABLES and comparing their results ?

Meaning, I want to develop a function which would be used like:

if isEquivalent(Query1,Query2) then
Msgbox("Both the functions would return same result")
else
Msgbox("They are not equivalent queries")
end if

Note:
The above function isEquivalent must NOT make use of any table to check this since we (humans) do not need a table to check that they will yield the same result ! Looking at the query itself we know that it will give the SAME result when run on the SAME table...


Ps. help !

Thanks.

Possibly the result could be the same if the output of Explain of both statements is exactly the same.
Look at the SQLite documentation here:
https://sqlite.org/lang_explain.html
I have a feeling though that the output of Explain could be different but with the same data produced by
both queries.
You will need to do some testing or ask the experts in the SQLite group:
sqlite-users@mailinglists.sqlite.org

RBS

RBS

 

[beelze69]

Is this of any use .. It appears the might be an API you could use . http://cosette.cs.washington.edu/ .

ps: I'm scratching my head wondering why you need this .. but obviously theres a reason

Hi,

I have an implementation in which there are 100s of SQL queries constructed on a daily basis with each query given an ID. These queries are constructed by different personnel in the organization via a UI.

They are applied to a group of records which come 'in a queue' 24 x 7. Now we need a way to weed out those queries which would yield same result, which we feel would result in a better utilization of the Server.

Presently we have no control on the UI level over the personnel who will construct such queries on the system via the UI.

Thanks for the cosette link.. Will see how this can be used....