Short-Circuit Evaluation of WHERE Clause in SQL Statement

Skip to first unread message


Oct 27, 2017, 9:53:28 AM10/27/17
to OrientDB
I want to confirm that a behavior I'm observing in ODB 2.2.28 is actually intended to work the way it appears to be, and that this behavior will be maintained.

I'm doing an UPDATE that relies on "short-circuit evaluation" of the WHERE clause:
UPDATE Cache CONTENT {"instance":"XYZ", "key":"JJJ", "aaa":5454, "bbb":2323, "ccc":5353, "expires": 999} UPSERT WHERE (instance="XYZ" AND key="JJJ") OR expires <= 1000

There are indexes on (instance, key) and on (expires). This statement will do the following:
1) Update the existing record where instance=XYZ and key=JJJ.
2) Update the existing record where expires<=1000.
3) Insert a new record (if both "parts" of the WHERE clause fail).

As I said, this does work just as I described, but as you can see it relies on the order of the logic in the WHERE clause. If the SQL optimizer chose to look at the expires<=1000 first, then it might do #2 instead of #1 (although that would cause an index violation on (instance, key), assuming that record already exists.

Bottom line, my question is simply "does the SQL optimizer do short-circuit evaluation of the WHERE clause in the left-to-right order as presented" (or have I just gotten lucky in my testing)? And if not, is there a way to ensure that it does?

Reply all
Reply to author
0 new messages