0

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. my app

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?

teXI
  • 51
  • 9
  • 1
    please tag your DBMS first, also I don't get your problem here, DBMS do CHECK EVERYTHING IN `WHERE` as long as that syntax is valid... is not like it will be lazy and somehow decide not to do this... – T. Peter Feb 08 '21 at 03:22
  • also you should include [MCVE](https://stackoverflow.com/help/minimal-reproducible-example), sample data, expect output, but I doubt this question is even related to `sql` this looks more like a client problem. – T. Peter Feb 08 '21 at 03:24
  • @T.Peter, in SQL the logical operators AND and OR acts like && and || in C++, aren't? – teXI Feb 08 '21 at 03:25
  • 5
    Actually, you don't/won't really know how the `WHERE` clause gets executed. Keep in mind that, unlike in application languages such as Java and C#, a `WHERE` clause in SQL will generally _not_ short circuit as you write it. Instead, the database will choose what order to use for evaluation. – Tim Biegeleisen Feb 08 '21 at 03:30
  • I think this relate to my case https://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated – teXI Feb 08 '21 at 03:35
  • 1
    @T.Peter It depends, there is no hard and fast rule as you might expect with an app language. To the OP: Adding some sample data to this question to make it more concrete might be helpful. – Tim Biegeleisen Feb 08 '21 at 03:36
  • Are there be useful some tricks like WHERE NOT (...) to prevent short circuit evaluation? – teXI Feb 08 '21 at 03:43

2 Answers2

2

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.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • You're right. I can't make DBMS use or not short-circuit evaluation in "WHERE cond1 OR cond2". I really thought that "AND" and "OR" in SQL acts exactly like "&&" and "||" in C++ or Java. But for my application only one thing matter: number of clauses to check in WHERE section. So I think, I found good solution to this problem - "WHERE cond1 = (cond2 = (cond3 = cond4))". Any way, thank you for your answer! – teXI Feb 08 '21 at 05:25
  • 1
    @teXI Just a note. The query does not have join predicates; that will produce a cross product of all rows (somewhat filtered), a query that is difficult to optimize by the engine. I'm not sure if the example is representative of a real life load/stress test (it can be, though). I would strongly suggest you should use modern join syntax instead. – The Impaler Feb 08 '21 at 14:39
  • yeah, I planing to add tuning joins functionality (INNER and OUTER, LEFT and RIGHT). But the question is how to automate this tuning. May be it will be just random choose between INNER/OUTER and LEFT/RIGHT joins. – teXI Feb 09 '21 at 09:50
0

I decided to add "Short-circuit protection mode" to my app and build WHERE section like "WHERE ((cond1 = cond2) = cond3) = cond4)" or "WHERE cond1 = (cond2 = (cond3 = cond4))", the last one would be easier to implement.

UPDATE: protection added

teXI
  • 51
  • 9