ads' corner

My idea wouldn't have been easy with every of the available options, but the final poll result of 16 made it relatively easy. This answer is the result if you ignore (forget, blame your math teacher) the order of operations in mathematics. But this blog posting and this answer would not be much fun. Instead I use PostgreSQL to bend my answer to the result of the poll.

How to approach this

There are a couple options, like working on the order of operations. But this is already some work. This specific example works also if you multiply 2 and 2, instead of adding them: 2 * 2 * 4 = 16. All I have to do is overload the + operator to multiply instead. After all, it's just bending the database to the expected result.

Adding the new operator

First I need to find the correct operator definition in the catalog. PostgreSQL interprets the 2 and 4 as integers, therefore I need the + operator for where the left and right argument is an integer:

CREATE OPERATOR pg_catalog.+ (
    FUNCTION = int4pl,
    LEFTARG = integer,
    RIGHTARG = integer,
    COMMUTATOR = OPERATOR(pg_catalog.+)
);

As we can see, the actual function doing this operation is int4pl. Let's look at the operator for *:

CREATE OPERATOR pg_catalog.* (
    FUNCTION = int4mul,
    LEFTARG = integer,
    RIGHTARG = integer,
    COMMUTATOR = OPERATOR(pg_catalog.*)
);

The function used here is int4mul.

So how about just defining a new operator for +, but using int4mul?

CREATE OPERATOR public.+ (
    FUNCTION = int4mul,
    LEFTARG = integer,
    RIGHTARG = integer,
    COMMUTATOR = OPERATOR(pg_catalog.*)
);

There are two ways to do this: I'm defining a new operator, which lives in the public schema (or any other schema I specify). I could also just update the operator in pg_catalog. And I'm using a separate database: in case I screw up the catalog, I can just drop everything and start over. Don't do this in your default or production database!

The search_path problem

By default, the functions in pg_catalog come first. If you change search_path, and don't include pg_catalog, then pg_catalog is always placed first. However if you also include pg_catalog in search_path, then the functions and operators are looked up in the order you have schemas specified in search_path. I explained this in more details in this blog post here.

What I have to do is:

SET search_path TO public, pg_catalog;

Then I can run my query:

math=# SELECT 2 + 2 * 4;
 ?column? 
----------
       16
(1 row)

Under the hood, PostgreSQL tries to find operators for each operation. The \* comes from pg_catalog (second in search_path), but + comes from public because that's the first hit in search_path.

Summary

It's easy to bend PostgreSQL to your expected results, even if they are mathematically wrong. My answer on the original Tweet caused some discussion and guessing.

The problem is a bit more real, because one can do this:

math=# ALTER DATABASE math SET search_path TO public, pg_catalog;
ALTER DATABASE

Now the operator I created (and any other operator in public) overloads the operators in pg_catalog. PostgreSQL fixes this problem with public by updating the permissions for the public schema in version 15 of the database. If people have write access to the DB, they can still overload even simple operators like + and * and then return unexpected results for everyone. Or return expected but slightly false results.