I write SQL load testing tool where user could just specify the number of conditions in WHERE section (and some more functionality) using sliders, then press button “Start” for starting load testing of database.
The problem is: If I use OR logical operator for joining clauses, the DBMS would stop checking of WHERE section once it encounter predicate that return TRUE. With AND logical operator is similar situation: once DBMS encounter predicate that return FALSE, the the DBMS will stop checking WHERE section. How to make DBMS check all clauses in WHERE section independently of clauses TRUE/FALSE values?
Advertisement
Answer
You can’t.
SQL is a declarative language, not an imperative one. That means the database engine is absolutely free to use any and all kinds of optimizations (and dirty tricks) to get the correct result according to your specification.
Moreover, the strategy the engine may choose today may change in the future without notice, so long it returns the correct result. The optimizer logic is typically very simple (and predictable) in low end databases, while it’s very sophisticated in high end ones (more operations, better histograms, smarter logic, etc.). In short the strategy is constantly adapting the specific method to the existing conditions: data present on each table, hardware and software conditions, etc.