Database Reference
In-Depth Information
DIVIDE
(SELECT a
FROM T1, T2
WHERE ((T1.a = T2.a) AND (T1.b < 0)) AND d IN
(SELECT d, c
FROM T3, T4
WHERE (T3.d = T4.d))
GROUP BY a
HAVING COUNT(a) =
(SELECT COUNT(*)
FROM T3, T4
WHERE (T3.d = T4.d))) ON {a}
The final rewrite would then proceed exactly as before for the last divide
keyword.
Limitations
The logic of the rewrite algorithm is sound. The experimental parser and rewriter, however, have some
limitations. Notably, the target database's metadata is not polled to make sure tables exist, attributes exist,
and attributes are on domains that make sense. As this is a pure character string rewriter, it is not expected
to do this. These have been left out because the DBMS, of course, will check the final rewritten query for
correctness so that these limitations are relegated to the DBMS. Actually, this is not a limitation even if
it is seen because our target is to turn into easy task the coding of queries that involve explicit division.
The target is assumed achieved one a query explicit division is translated into an equivalent SQL query.
Then it is the duty of the SQL parser within the DBMS to check the syntax of the query for correctness.
The Zql parser used in this experiment cannot properly parse complicated
select
statements. A new
light-weight general SQL parser is needed for exhaustive query handling.
Also, extracting aggregate functions,
having
and
group by
clauses inside the dividend or divisor
were not included in this experiment so they would not interfere with the count method template. This
limited nested query rewriting abilities.
EXPERIMENTS
A suite of JUnit tests were created to test varying complexities of divide queries. For each division
query, a count-method rewrite and a not-exists/not-exists method rewrite were performed. As long as
the dividend and divisor each were valid with
select
attributes that could be extracted with Zql, the
rewrites succeeded.
The implemented front end has been integrated into a complete system that communicates with
MySQL. The integrated system takes any SQL statement, rewrite the query if it contains explicit division
(expressed using DIVIDE), and then executes the query to return the results. All the translated queries
were successful run. Finally, the verbatim translation process has been tested by coding queries that
does not refer to existing tables/attributes. The queries were correctly translated by the front end into
