Short-circuit logic evaluation operators

Question

Are there any short-circuit logic operators (specifically short-circuit AND and short-circuit OR) that I can use in a WHERE clause in MySQL 5.5? If there isn't, what are the alternatives?

An abstract view at my problem along with an explanation as to why I need this can be found at this fiddle:

http://sqlfiddle.com/#!2/97fd1/3

In reality we are looking at millions of books in millions of bookstores in thousands of cities in hundreds of countries, which is why we cannot accept the overhead of receiving the unneeded information with every query we dispatch and seriously need to find a way to make the evaluation stop as soon as we have all rows that satisfy the current condition, before moving on to the next OR.

Let me know if you need more information. Thanks in advance.

As requested, here is the schema used in the fiddle:

CREATE TABLE quantitycache (
  id INT AUTO_INCREMENT,
  quantity INT,
  book_id INT NOT NULL,
  bookstore_id INT NULL,
  city_id INT NULL,
  country_id INT NULL,
  PRIMARY KEY (id)
);

As well as some example data:

INSERT INTO quantitycache 
     (quantity, book_id, bookstore_id, city_id, country_id)
VALUES
     (5,        1,       1,            NULL,    NULL),
     (100,      2,       1,            NULL,    NULL),
     (7,        1,       2,            NULL,    NULL),
     (12,       1,       NULL,         1,       NULL),
     (12,       1,       NULL,         NULL,    1),
     (100,      2,       NULL,         1,       NULL),
     (100,      2,       NULL,         NULL,    1),
     (200,      3,       NULL,         1,       NULL),
     (250,      3,       NULL,         NULL,    1);

The only way you are going to be able to do this is using T-SQL me thinks. If you write a query that queries ALL stores then you will have a long wait BUT with T-SQL you can set variables and flags etc to break out of a loop that iteratively executes a smaller portion of the query (i.e. query store by store) once a match is found. — Paul Sullivan, Jun 06 '13 at 18:59
I'll bet that it will stop evaluating an `OR` expression as soon as one of the cases matches, as long as none of the expressions has side effects. — Barmar, Jun 06 '13 at 19:00
@Barmar: This is not true unfortunately, as you can see in the fiddle, the rows that satisfy the other OR conditions are returned as well. — Christian Kiewiet, Jun 06 '13 at 19:01
That's what `OR` means -- return rows that satisfy any of the conditions. — Barmar, Jun 06 '13 at 19:02
Indeed, which is why I am asking if there is a way to use short-circuit logic operators instead of eager operators. — Christian Kiewiet, Jun 06 '13 at 19:04
@Barmar - this may OR may not be true (lol) just because either side may or may not be true does not mean that SQL server will not evaluate both sides of the expression. I suggest the OP looks at CASE statement deterministic evaluation (google "T-SQL short circuit logic") — Paul Sullivan, Jun 06 '13 at 19:04
I think you want something involving `IF()`, but I can't understand your logic so I'm not sure what the full expression is. — Barmar, Jun 06 '13 at 19:05
Please copy the schema and attempted query to your question, and include the desired result. — Barmar, Jun 06 '13 at 19:06
see http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/cb536df3-f573-49eb-b886-7ca8e899a4dd — Paul Sullivan, Jun 06 '13 at 19:06
And I would hazard a guess this is pretty standard. The only way to short ciruit is by T-SQL and even that will be a bit of a minefield see http://blogs.msdn.com/b/bartd/archive/2011/03/03/don-t-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case.aspx — Paul Sullivan, Jun 06 '13 at 19:07
Also see this one: http://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated — Marcus Adams, May 09 '14 at 20:43
I know it's an old question, but..is it ever be possibile to separate the "big table" into smaller tables? For example, one with books-vs-stores relations, one for stores-vs-cities and another with cities-vs-countries. This way the queries could be simplified a little bit. — Erenor Paz, Feb 22 '15 at 14:29

Mitch · Accepted Answer · 2013-06-06T19:08:18.317

1

Keep in mind that a query does not execute imperatively. The query you wrote may run on multiple threads, and therefore a short-circuit operator in the where clause would not result in only one result.

Instead, use the LIMIT clause to only return the first row.

SELECT * FROM quantitycache
WHERE bookstore_id = 1 OR city_id = 1 OR country_id = 1
ORDER BY bookstore_id IS NULL ASC,
         city_id IS NULL ASC,
         country_id IS NULL ASC
LIMIT 1;

To get the best match for all books in a result set, save the results to a temp table, find the best result, then return interesting fields.

CREATE TEMPORARY TABLE results (id int, book_id int, match_rank int);

INSERT INTO results (id, book_id, match_rank)
SELECT id, book_id, 
    -- this assumes that lower numbers are better
    CASE WHEN Bookstore_ID is not null then 1 
         WHEN City_ID is not null then 2 
         ELSE 3 END as match_rank
FROM quantitycache
WHERE bookstore_id = 1 OR city_id = 1 OR country_id = 1;

Select * 
from (
    select book_id, MIN(match_rank) as best_rank 
    from results 
    group by book_id
) as r
inner join results as rid 
    on r.book_id = rid.book_id 
    and rid.match_rank = r.best_rank
inner join quantitycache as q on q.id = rid.id;

DROP TABLE results;

edited Jun 06 '13 at 19:08

answered Jun 06 '13 at 18:51

Mitch

21,223
6
63
86

Thank you for your answer, unfortunately this would only return a single book_id, whilst we actually need to get the results for all books. – Christian Kiewiet Jun 06 '13 at 18:52
... err but your question states to "stop as soon as we have a hit". If you need to get all results then you will have to traverse all rows @ChristianKiewiet – Paul Sullivan Jun 06 '13 at 18:54
1

I think he means "stop checking columns in the `OR` as soon as one is satisfied". – Barmar Jun 06 '13 at 18:59
Let me rephrase that to "as soon as we have all rows that satisfy the current condition" – Christian Kiewiet Jun 06 '13 at 19:00
You can't know whether you have all the rows that satisfy until you check all the rows. – Barmar Jun 06 '13 at 19:01
@ChristianKiewiet, see http://sqlfiddle.com/#!2/0e34a3/1 for an example of results. – Mitch Jun 06 '13 at 19:16
Thanks for the effort. I will check the performance your attempt when I'm in the office tomorrow; even if it should perform poorly, this looks like a nudge in the right direction. – Christian Kiewiet Jun 06 '13 at 20:28

Short-circuit logic evaluation operators

1 Answers1

Linked