We’ve just added support for the `EVERY()`

aggregate function (#1391) to jOOQ, and would like to take the opportunity of letting you know of this true SQL gem that can come in handy `EVERY(now and then)`

(pun intended).

Let’s assume we have four books in our table:

INSERT INTO book VALUES (1, 1, '1984'); INSERT INTO book VALUES (2, 1, 'Animal Farm'); INSERT INTO book VALUES (3, 2, 'O Alquimista'); INSERT INTO book VALUES (4, 2, 'Brida');

Now the question is:

Is

`EVERY()`

ID lower than 10?

We’ll ask:

SELECT EVERY(id < 10) FROM book

And the answer is:

every ----- true

Does

`EVERY()`

book for each author end with the letter ‘a’?

We’ll ask:

SELECT author_id, EVERY(title LIKE '%a') FROM book GROUP BY author_id

And the answer is:

author_id every ----------------- 1 false 2 true

Wonderful!

As with all aggregate functions, we can even use them as a window function!

SELECT book.*, EVERY(title LIKE '%a') OVER (PARTITION BY author_id) FROM book

Which will produce

id author_id title every ------------------------------------ 1 1 1984 false 2 1 Animal Farm false 3 2 O Alquimista true 4 2 Brida true

## Who supports `EVERY()`

Well, the SQL standard has it:

10.9 <aggregate function> <aggregate function> ::= COUNT <left paren> <asterisk> <right paren> [ <filter clause> ] | <general set function> [ <filter clause> ] | <binary set function> [ <filter clause> ] | <ordered set function> [ <filter clause> ] | <array aggregate function> [ <filter clause> ] <general set function> ::= <set function type> <left paren> [ <set quantifier> ] <value expression> <right paren> <set function type> ::= <computational operation> <computational operation> ::= AVG | MAX | MIN | SUM| EVERY <-- yes, here! EVERY!| ANY | SOME | COUNT | STDDEV_POP | STDDEV_SAMP | VAR_SAMP | VAR_POP | COLLECT | FUSION | INTERSECTION

But if your database is not PostgreSQL, don’t worry. `EVERY()`

can be emulated on `EVERY()`

database using `SUM()`

and `CASE`

expressions. Here’s how to emulate the first query:

-- SELECT EVERY(id < 10) -- FROM book SELECT MIN(CASE WHEN id < 10 THEN 1 ELSE 0 END) FROM book;

Or as window functions

-- SELECT -- book.*, -- EVERY(title LIKE '%a') OVER (PARTITION BY author_id) -- FROM book SELECT book.*, MIN(CASE WHEN title LIKE '%a' THEN 1 ELSE 0 END) OVER(PARTITION BY author_id) FROM book;

**For a more concise emulation, see Stew Ashton’s comments**

And, as always on this blog, we’re happy to conclude that the upcoming jOOQ 3.6 will now handle `EVERY(emulation)`

for you, so you can write:

DSL.using(configuration) .select(BOOK.fields()) .select(every(BOOK.TITLE.like("%a")) .over(partitionBy(BOOK.AUTHOR_ID))) .from(BOOK) .fetch();

Have fun with this new function!

A more concise emulation:

Sorry, I meant the following:

That’s a cool idea, thanks for sharing, Stew!

I ended up implementing this as an improvement:

I’ll also fix the article, because the original solution was not correctly aggregating empty sets to NULL

curious about what those COLLECT and FUSION are…

The standard says

“COLLECT (X) is equivalent to FUSION (MULTISET [X]).”.Unfortunately, this is the definition of FUSION:

If FUSION is specified, then the result is the multiset M such that for each value V in the element type of DT, including the null value, the number of elements of M that are identical to V is the sum of the number of identical copies of V in the multisets that are the values of the column in each row of TXA.So, we aren’t really any smarter ;-)

On a more serious note. These functions aggregate groups into nested collections (“multisets”), similar to what ARRAY_AGG does with arrays.

So, we can forgive those who didn’t take the time to implement them ;-)

There are much more forgivable omissions from the SQL standard by the implementations… ;)

If the DB allows MIN and MAX on BOOLEAN values (with FALSE < TRUE), then EVERY is equivalent to MIN, and SOME/ANY are equivalent to MAX.

There’s no need for a CASE expression to convert BOOLEAN to INTEGER.

SELECT

min(id < 10) AS bool_and, — a.k.a. EVERY()

max(id < 10) AS bool_or

FROM book;

This works on hsqldb and Calcite. I haven’t tried any other DBs.

That’s clever. And I’ll be damned, I was going to point out that no way this is SQL standards compliant, but it is. 8.2 <comparison predicate> General Rules 8)

Can’t say I really like this, but it does make sense.