PRIMARY KEY lookup with additional filters

[chaudum]

Use case:

The SQLAlchemy ORM loads expired session objects by their primary key identity:

session = Session()
o = Model(id=uuid.uuid4().hex, deleted=False)
session.add(o)
session.commit()  # will expire the object `o`

print(f"{o.id=}")  # will perform do a PK lookup of the object `o`

Since documents in CrateDB are immediately available through PK lookup, this works great. So far so good.

However, we have the case that a bunch of SQLAlchemy models have an deleted field, which defines whether the object is "visible". In order to transparently hide the deleted = FALSE filter from queries, we make use of the pattern described here: https://docs.sqlalchemy.org/en/13/orm/events.html#sqlalchemy.orm.events.QueryEvents.before_compile
Compiling the query for loading on primary key identity however, is also going through this event hook, causing the alleged primary key lookups to be converted to regular search queries. These search queries then of course do not find the object with the given primary key.

Example:

$ export PK=$(cat /proc/sys/kernel/random/uuid); cr8 run-crate 4.2.x -- @crash <<EOF <<<
CREATE TABLE t1 (id TEXT PRIMARY KEY, deleted BOOLEAN);
INSERT INTO t1 (id, deleted) values ('$PK', FALSE);
SELECT * FROM t1 WHERE id = '$PK';
SELECT * FROM t1 WHERE id = '$PK' AND deleted = FALSE;
EOF
# run-crate
===========

Skipping download, tarball alrady extracted at /home/christian/.cache/cr8/crates/crate-4.2.2
Starting Crate process
CrateDB launching:
    PID: 1503697
    Logs: /home/christian/.cache/cr8/crates/crate-4.2.2/logs/cr8-crate-run670707194.log
    Data: /tmp/tmp4d1k9jbh (removed on stop)

Psql      : 127.0.0.1:5432
Http      : 127.0.0.1:4200
Transport : 127.0.0.1:4300
Cluster ready to process requests


# @crash
========

CONNECT OK
CREATE OK, 1 row affected  (0.413 sec)
INSERT OK, 1 row affected  (0.097 sec)
+--------------------------------------+---------+
| id                                   | deleted |
+--------------------------------------+---------+
| 1c02ffaa-8f08-4106-9ea7-3f609ac99518 | FALSE   |
+--------------------------------------+---------+
SELECT 1 row in set (0.105 sec)
+----+---------+
| id | deleted |
+----+---------+
+----+---------+
SELECT 0 rows in set (0.044 sec)

Feature description:

Would it somehow be possible to make use of primary key lookups if a query contains the full primary key even if there are additional filters? CrateDB should be able to find the document immediately, right?

[mfussenegger]

Would it somehow be possible to make use of primary key lookups if a query contains the full primary key even if there are additional filters?

Yes, if there are no OR clauses but only AND it should be possible to do.

[chaudum]

Would it somehow be possible to make use of primary key lookups if a query contains the full primary key even if there are additional filters?

Yes, if there are no OR clauses but only AND it should be possible to do.

So WHERE id = ? AND (deleted = FALSE OR deleted IS NULL) would not work?

I guess that is ok, since we can rely on deleted being NOT NULL.

[mfussenegger]

So WHERE id = ? AND (deleted = FALSE OR deleted IS NULL) would not work?

I think that should also work, problematic is a OR on the same level as the primary key column. Something like id = ? OR x = 10

[seut]

We've implemented support for additional filters on primary-key SELECTqueries when combined with an AND operator on the same level by #10708.
It will be part of our next feature 4.4 feature release.
Thanks @chaudum for bringing this up!