Don’t Use the String Concatenation “Trick” in SQL Predicates

In SQL, quite often, we want to compare several values with each other. For instance, when we’re looking for a specific user by their first and last names, we’ll write a query like this one:

SELECT *
FROM customer
WHERE first_name = 'SUSAN'
AND last_name = 'WILSON';

We’re getting:

CUSTOMER_ID   FIRST_NAME   LAST_NAME
------------------------------------
          8   SUSAN        WILSON

Surely, everyone agrees that this is correct and perfectly fine as we probably have an index on these two columns (or on at least one of them) to speed up such queries:

CREATE INDEX idx_customer_name ON customer (last_name, first_name);

The execution plan is thus optimal, e.g. with Oracle:

-------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |       |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER          |     1 |
|*  2 |   INDEX RANGE SCAN                  | IDX_CUSTOMER_NAME |     1 |
-------------------------------------------------------------------------

But sometimes, we cannot use AND to connect two predicates. In particular, that’s not possible with an IN predicate, so people sometimes resort to using string concatenation, because that seems to work and make sense.

For instance, let’s find all customers whose first and last names matches those of an actor (as always, using the Sakila database)

SELECT *
FROM customer
WHERE first_name || last_name IN (
  SELECT first_name || last_name
  FROM actor
)

And yes indeed, what we’re getting here is the correct answer:

CUSTOMER_ID   FIRST_NAME   LAST_NAME
------------------------------------
          6   JENNIFER     DAVIS

But that answer is only accidentally correct!

Because we weren’t looking for customers called

first_name = 'JENNIFER' AND last_name = 'DAVIS'

We were looking for customers called

first_name || last_name = 'JENNIFERDAVIS'

Want proof? Let’s add a new customer:

INSERT INTO customer (customer_id, first_name, last_name )
VALUES               (600        , 'JENNI'   , 'FERDAVIS');

Yeah right? No one is called FERDAVIS. Or are they? As good programmers, we closely observe Murphy’s Law (i.e. always look both left and right when crossing a street).

In any case, let’s run our query again:

SELECT *
FROM customer
WHERE first_name || last_name IN (
  SELECT first_name || last_name
  FROM actor
)

And observe the result!

CUSTOMER_ID   FIRST_NAME   LAST_NAME
------------------------------------
          6   JENNIFER     DAVIS
        600   JENNI        FERDAVIS

Of course, because our predicate was really looking for customers called

first_name || last_name = 'JENNIFERDAVIS'

Which matches in both cases:

-- What we expected
first_name || last_name = 'JENNIFER' || 'DAVIS'

-- What we got
first_name || last_name = 'JENNI' || 'FERDAVIS'

Notice that I only added this customer to the customer table, not to the actor table. There’s no actor by the name FERDAVIS, so the result is clearly wrong.

AHA! Let’s use an “impossible” separator

So, we might proceed to fixing this as such:

SELECT *
FROM customer
WHERE first_name || '###' || last_name IN (
  SELECT first_name || '###' || last_name
  FROM actor
)

And now, the result is again correct. We get only JENNIFER DAVIS because we were looking for:

first_name || '###' || last_name = 'JENNIFER###DAVIS'

This works quite well for a while, as the separator is quite “impossible” (i.e. improbable) to be encountered in actual data. But we shouldn’t trust our judgement, because… Murphy’s Law. So you might think: better use a more rare separator, e.g. (if your database supports proper character sets)

SELECT *
FROM customer
WHERE first_name || '🙈🙉🙊' || last_name IN (
  SELECT first_name || '🙈🙉🙊' || last_name
  FROM actor
)

The use of emojis should indicate what my opinion of this approach is.

Too bad for performance, though

Remember that index we’ve created? Fact is, we also have such an index on the ACTOR table:

CREATE INDEX idx_actor_name ON actor (last_name, first_name);

And now, let’s assume our query is a bit different. We’ll be looking only for customers whose address_id is 10:

SELECT *
FROM customer
WHERE address_id = 10
AND first_name || '🙈🙉🙊' || last_name IN (
  SELECT first_name || '🙈🙉🙊' || last_name
  FROM actor
)

Now, our querymoji is using the index indeed, but for an INDEX FULL SCAN, so it’s only slightly faster than scanning the entire actor table:

-----------------------------------------------------------------------------------
| Id  | Operation                            | Name                       | Rows  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                            |       |
|*  1 |  HASH JOIN SEMI                      |                            |     1 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER                   |     1 |
|*  3 |    INDEX RANGE SCAN                  | IDX_CUSTOMER_FK_ADDRESS_ID |     1 |
|   4 |   INDEX FULL SCAN                    | IDX_ACTOR_NAME             |     2 |
-----------------------------------------------------------------------------------

And what’s worse, even if all the cardinality estimates correctly indicate only 1-2 rows, we’ll perform a HASH JOIN and load the full index for it! We should be running a NESTED LOOP instead.

Is there a better way? Yes! Use row constructors to compare several values at once:

SELECT *
FROM customer
WHERE address_id = 10
AND (first_name, last_name) IN (
  SELECT first_name, last_name
  FROM actor
);

Or, if your database doesn’t support this syntax (luckily, Oracle and PostgreSQL do, for instance), then you can resort to an equivalent EXISTS predicate

SELECT *
FROM customer c
WHERE address_id = 10
AND EXISTS (
  SELECT 1
  FROM actor a
  WHERE c.first_name = a.first_name
  AND c.last_name = a.last_name
);

Both of these queries are exactly equivalent and result in a nested loop semi join, rather than the previous hash join, which is perfectly reasonable for these small tables. We can now use the IDX_ACTOR_NAME for a quick INDEX RANGE SCAN operation:

-----------------------------------------------------------------------------------
| Id  | Operation                            | Name                       | Rows  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                            |       |
|   1 |  NESTED LOOPS SEMI                   |                            |     1 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER                   |     1 |
|*  3 |    INDEX RANGE SCAN                  | IDX_CUSTOMER_FK_ADDRESS_ID |     1 |
|*  4 |   INDEX RANGE SCAN                   | IDX_ACTOR_NAME             |     1 |
-----------------------------------------------------------------------------------

But let’s not trust the estimated plans. Let’s benchmark (more info about benchmarking SQL here)

SET SERVEROUTPUT ON
DECLARE
  v_ts TIMESTAMP WITH TIME ZONE;
  v_repeat CONSTANT NUMBER := 2500;
BEGIN

  -- Repeat benchmark several times to avoid warmup penalty
  FOR r IN 1..5 LOOP
    v_ts := SYSTIMESTAMP;
      
    FOR i IN 1..v_repeat LOOP
      FOR rec IN (
        SELECT first_name, last_name
        FROM customer
        WHERE address_id = 10
        AND first_name || '###' || last_name IN (
          SELECT first_name || '###' || last_name
          FROM actor
        )
      ) LOOP
        NULL;
      END LOOP;
    END LOOP;
      
    dbms_output.put_line('Run ' || r ||', Statement 1 : ' 
      || (SYSTIMESTAMP - v_ts));
    v_ts := SYSTIMESTAMP;
      
    FOR i IN 1..v_repeat LOOP
      FOR rec IN (
        SELECT first_name, last_name
        FROM customer
        WHERE address_id = 10
        AND (first_name, last_name) IN (
          SELECT first_name, last_name
          FROM actor
        )
      ) LOOP
        NULL;
      END LOOP;
    END LOOP;
      
    dbms_output.put_line('Run ' || r ||', Statement 2 : '
      || (SYSTIMESTAMP - v_ts));
  END LOOP;
END;
/

As can be seen here, the benchmark shows that the query using the row constructor is drastically faster as it can properly use the index as it should:

Run 1, Statement 1 : +000000000 00:00:00.374471000
Run 1, Statement 2 : +000000000 00:00:00.062830000
Run 2, Statement 1 : +000000000 00:00:00.364168000
Run 2, Statement 2 : +000000000 00:00:00.066252000
Run 3, Statement 1 : +000000000 00:00:00.359559000
Run 3, Statement 2 : +000000000 00:00:00.063898000
Run 4, Statement 1 : +000000000 00:00:00.344775000
Run 4, Statement 2 : +000000000 00:00:00.086060000
Run 5, Statement 1 : +000000000 00:00:00.394163000
Run 5, Statement 2 : +000000000 00:00:00.063176000

Now, imagine we were running this against some much more impressive data sets than the Sakila database

Conclusion

If you’re ever thinking about concatenating two fields for a comparison, try again. There are two major caveats that should indicate you’re about to do something silly:

  • There’s a major risk of your query being subtly wrong (accidental matches between JENNIFER DAVIS and JENNI FERDAVIS)
  • There’s a major risk of your query being quite slow

So, as a rule of thumb, don’t use concatenation in predicates. There’s (almost) always a better way.

Read also: Why You Should (Sometimes) Avoid Expressions in SQL Predicates

Why PostgreSQL is so Awesome

Just recently, I’ve blogged about PostgreSQL 9.3 having been released, which is awesome enough as PostgreSQL finally supports materialised views and updatable views. I have then blogged about PostgreSQL’s syntax being a mystery only exceeded by its power, as it allows for treating INSERT and UPDATE statements as table references, when used with the RETURNING clause. This is quite fancy, even if not very useful in everyday SQL.

But what I’m writing about today is colossal:

PostgreSQL predicates are just ordinary expressions

Let this sink in. To PostgreSQL, predicates are just ordinary expressions evaluating to the boolean type. This is documented in the SELECT syntax reference, here:
http://www.postgresql.org/docs/9.3/static/sql-select.html

Citing:

The optional WHERE clause has the general form

WHERE condition

where condition is any expression that evaluates to a result of type boolean […]

It struck me like lightning when I read this Stack Overflow question. You can put a predicate everywhere! Although I knew this before, I had never thought about how awesome this is! You can use predicates in the SELECT clause:

SELECT a, b, c = d, e IN (SELECT x FROM y)
FROM t

You can use predicates in the GROUP BY clause:

SELECT count(*)
FROM t
GROUP BY c = d, e IN (SELECT x FROM y)

You can use predicates in the ORDER BY clause:

SELECT *
FROM t
ORDER BY c = d, e IN (SELECT x FROM y)

You can aggregate predicates using the EVERY aggregate function.

Don’t believe it? See for yourself in this SQLFiddle!

“Ordinary” SQL

In “ordinary” SQL (i.e. standards-compliant), predicates have to be transformed into ordinary value expressions using the CASE clause. Repeating the above examples:

SELECT clause:

SELECT a, b,
       CASE WHEN c = d THEN true ELSE false END,
       CASE WHEN e IN (SELECT x FROM y)
            THEN true ELSE false END
FROM t

GROUP BY clause:

SELECT count(*)
FROM t
GROUP BY CASE WHEN c = d THEN true ELSE false END,
         CASE WHEN e IN (SELECT x FROM y)
              THEN true ELSE false END

ORDER BY clause:

SELECT *
FROM t
ORDER BY CASE WHEN c = d THEN true ELSE false END,
         CASE WHEN e IN (SELECT x FROM y)
              THEN true ELSE false END

Impact for jOOQ

The answer given in the above Stack Overflow question shows how jOOQ can standardise this behaviour by rendering predicates / conditions directly as column expressions where this is supported, while emulating this behaviour using an equivalent CASE expression, otherwise.

Take predicates in the SELECT clause, for instance:

DSL.using(configuration)
   .select(
       T.A, T.B,
       // Transform a jOOQ Condition into a Field:
       field(T.C.eq(T.D)),
       field(T.E.in(select(Y.X).from(Y)))
   )
   .from(T);

Further thoughts

From jOOQ integration test experience, I can say that Derby, H2, HSQLDB, MariaDB, MySQL, PostgreSQL, and SQLite will work this way. While this is awesome from a syntax perspective, do keep an eye on your execution plans to verify that this isn’t going to produce lots of very expensive nested loops…

SQL Query Transformation Fun: Predicates with Row Value Expressions

Recently, I’ve blogged about how well jOOQ’s supported databases implement row value expressions and predicates formed from them. Some sample articles:

Row value expressions (or records, tuples) are useful to express more complex predicates, such as this one:

SELECT *
FROM t
WHERE (t.t1, t.t2) IN (
    SELECT u.u1, u.u2 FROM u
)

The above statement semi-joins u to t based on a tuple comparison, not just a single column comparison. This is useful, for example, when you want to select those users from a table whose first AND last name are also contained in another table (without any formal foreign key relationship, of course):

SELECT *
FROM users u
WHERE (u.first_name, u.last_name) IN (
    SELECT a.first_name, a.last_name FROM addresses a
)

Now, not all databases really support row value expression predicates. In fact, only very few really do. Here is a non-exhaustive list of databases, that will support some form of the above:

  • DB2
  • HSQLDB
  • MySQL
  • Oracle
  • Postgres

And these databases pretend they implement row value expression predicates, but get it wrong:

  • CUBRID (confusing them with sets)
  • H2 (confusing them with arrays)

A feature comparison matrix was listed here:
https://blog.jooq.org/2012/12/26/row-value-expressions-and-the-between-predicate

Can the above query be simulated?

Yes, it can! And it will be with jOOQ 3.1. Here’s how to transform the above query into an equivalent one, which doesn’t use row value expressions. So, here’s the original query, again:

SELECT *
FROM t
WHERE (t.t1, t.t2) IN (
    SELECT u.u1, u.u2 FROM u
)

The above can be transformed into the following query, using an EXISTS predicate

SELECT *
FROM t
WHERE EXISTS (
    SELECT * FROM u
    WHERE t.t1 = u.u1 AND t.t2 = u.u2
)

Now, in the above simple transformation, we have modified the subselect by changing the projection and by adding a predicate. This can be difficult for more complex subselects, so lets avoid touching it, by introducing another derived table:

SELECT *
FROM t
WHERE EXISTS (
    SELECT *
    FROM (
        SELECT u.u1, u.u2 FROM u -- untouched
    ) v(v1, v2)                  -- derived table
    WHERE t.t1 = v.v1 AND t.t2 = v.v2
)

That’s better. Many databases require renaming derived tables, which is why a derived column list v(v1, v2) was introduced. Not all databases support derived column lists, though, as can be seen in a previous blog post. So lets go on transforming the above into an equivalent query:

SELECT *
FROM t
WHERE EXISTS (
    SELECT *
    FROM (
        SELECT null v1, null v2  -- renaming
        FROM dual                -- if necessary
        WHERE 1 = 0              -- without new rows
        UNION ALL
        SELECT u.u1, u.u2 FROM u -- untouched
    ) v                          -- derived table
    WHERE t.t1 = v.v1 AND t.t2 = v.v2
)

Now, we’ve reached our goal. The above query will run on all databases, retaining the original semantics of a row value expression predicate using a subselect! Note, you possibly have to replace the dual with something more appropriate, of course.

Does this apply to all comparison predicates?

In principle, yes. Let’s look at a few examples.

NOT IN

SELECT *
FROM t
WHERE (t.t1, t.t2) NOT IN (
    SELECT u.u1, u.u2 FROM u
)

-- transforms into
SELECT *
FROM t
WHERE NOT EXISTS (
    SELECT *
    FROM (
        SELECT null v1, null v2
        FROM dual
        WHERE 1 = 0
        UNION ALL
        SELECT u.u1, u.u2 FROM u
    ) v
    WHERE t.t1 = v.v1 AND t.t2 = v.v2
)

Equality and non-equality

Equality and non-equality work the same way as IN and NOT IN IFF you are operating on scalar subselects. While actual comparison predicates will raise an error if subselects return more than one row, the EXISTS predicate will not. Beware!

Ordering

Just as with equality and non-equality, beware of non-scalar subselects!

SELECT *
FROM t
WHERE (t.t1, t.t2) > (
    SELECT u.u1, u.u2 FROM u
)

-- transforms into
SELECT *
FROM t
-- EXISTS is not formally correct,
-- if the subselect is a non-scalar one
WHERE EXISTS (
    SELECT *
    FROM (
        SELECT null v1, null v2
        FROM dual
        WHERE 1 = 0
        UNION ALL
        SELECT u.u1, u.u2 FROM u
    ) v
    WHERE (t.t1 > v.v1)
    OR    (t.t1 = v.v1 AND t.t2 > v.v2)
)

See the previously cited blog post about the BETWEEN predicate to learn how to simulate “ordering” comparison predicates with row value expressions.

Quantified comparison predicates

Quantifiers now become quite useful. The ANY quantifier removes the need for having scalar subselects, as in the previous example:

SELECT *
FROM t
WHERE (t.t1, t.t2) > ANY (
    SELECT u.u1, u.u2 FROM u
)

-- transforms into
SELECT *
FROM t
-- EXISTS is now formally correct
WHERE EXISTS (
    SELECT *
    FROM (
        SELECT null v1, null v2
        FROM dual
        WHERE 1 = 0
        UNION ALL
        SELECT u.u1, u.u2 FROM u
    ) v
    WHERE (t.t1 > v.v1)
    OR    (t.t1 = v.v1 AND t.t2 > v.v2)
)

The ALL quantifier, on the other hand, can be expressed with its inverse ANY quantifier, i.e.

SELECT *
FROM t
WHERE (t.t1, t.t2) > ALL (
    SELECT u.u1, u.u2 FROM u
)

-- first transforms into
SELECT *
FROM t
WHERE (t.t1, t.t2) <= ANY (
    SELECT u.u1, u.u2 FROM u
)

-- and then transforms into
SELECT *
FROM t
-- EXISTS is now formally correct
WHERE EXISTS (
    SELECT *
    FROM (
        SELECT null v1, null v2
        FROM dual
        WHERE 1 = 0
        UNION ALL
        SELECT u.u1, u.u2 FROM u
    ) v
    WHERE (t.t1 < v.v1)
    OR    (t.t1 = v.v1 AND t.t2 <= v.v2)
)

Conclusion

Happy transforming, and keep an eye out for jOOQ 3.1, conveniently implementing all of the above behind a type-safe Java API!

Disclaimer

Yes, NULLs. The above transformation deliberately left out edge cases where NULLs are involved.