Foreign Key UUIDs in SQLite are Queried as RFC4122 Values iso Binary Values #7338

New Issue

admin · 2026-01-22T15:50:16+01:00

admin commented

2026-01-22 15:50:16 +01:00

Originally created by @gnito-org on GitHub (Mar 5, 2024).

Bug Report

Q	A
BC Break	no
Version	3.1.0

Summary

When using UUIDs as keys in SQLite, retrieving records by primary key works fine, but retrieving records by foreign key does not work.

Current behavior

Table User:

CREATE TABLE user (id BLOB NOT NULL --(DC2Type:uuid) username VARCHAR(255) NOT NULL , PRIMARY KEY(id))

Table AccessToken:

CREATE TABLE access_token (id BLOB NOT NULL --(DC2Type:uuid) user_id BLOB NOT NULL --(DC2Type:uuid), token VARCHAR(1000) NOT NULL , PRIMARY KEY(id), CONSTRAINT FK_B6A2DD68A76ED395 FOREIGN KEY (user_id) REFERENCES user (id) NOT DEFERRABLE INITIALLY IMMEDIATE)

The SQL generated by ORM to retrieve a record by primary key is:

SELECT t0.id AS id_1, t0.username AS username_2 FROM user t0 WHERE t0.id = 0x018E0FD5D750753E8B34249AD3C28335;

That works because the id is queried with a binary value.

The SQL generated by ORM to retrieve records by foreign key is:

SELECT DISTINCT a0_.id AS id_0 FROM access_token a0_ WHERE a0_.user_id = '018e0fd5-d750-753e-8b34-249ad3c28335';

That does not find any records because the foreign key is queried with an RFC4122 representation of the UUID.

Expected behavior

The foreign key should also be queried with the binary representation of the UUID because it is stored in SQLite as a BLOB.

SELECT DISTINCT a0_.id AS id_0 FROM access_token a0_ WHERE a0_.user_id = 0x018E0FD5D750753E8B34249AD3C28335;

Screenshots

The id in the Entity:

The foreign key:

The QueryBuilder:

The Symfony Profiler Doctrine tab:

Originally created by @gnito-org on GitHub (Mar 5, 2024). ### Bug Report  | Q | A |------------ | ------ | BC Break | no | Version | 3.1.0 #### Summary When using UUIDs as keys in SQLite, retrieving records by primary key works fine, but retrieving records by foreign key does not work. #### Current behavior Table User: ``` CREATE TABLE user (id BLOB NOT NULL --(DC2Type:uuid) username VARCHAR(255) NOT NULL , PRIMARY KEY(id)) ``` Table AccessToken: ``` CREATE TABLE access_token (id BLOB NOT NULL --(DC2Type:uuid) user_id BLOB NOT NULL --(DC2Type:uuid), token VARCHAR(1000) NOT NULL , PRIMARY KEY(id), CONSTRAINT FK_B6A2DD68A76ED395 FOREIGN KEY (user_id) REFERENCES user (id) NOT DEFERRABLE INITIALLY IMMEDIATE) ``` The SQL generated by ORM to retrieve a record by primary key is: ``` SELECT t0.id AS id_1, t0.username AS username_2 FROM user t0 WHERE t0.id = 0x018E0FD5D750753E8B34249AD3C28335; ``` That works because the id is queried with a binary value. The SQL generated by ORM to retrieve records by foreign key is: ``` SELECT DISTINCT a0_.id AS id_0 FROM access_token a0_ WHERE a0_.user_id = '018e0fd5-d750-753e-8b34-249ad3c28335'; ``` That does not find any records because the foreign key is queried with an RFC4122 representation of the UUID. #### Expected behavior The foreign key should also be queried with the binary representation of the UUID because it is stored in SQLite as a BLOB. ``` SELECT DISTINCT a0_.id AS id_0 FROM access_token a0_ WHERE a0_.user_id = 0x018E0FD5D750753E8B34249AD3C28335; ``` ### Screenshots The `id` in the Entity: ![Screenshot from 2024-03-06 14-54-57](https://github.com/doctrine/orm/assets/70450336/5d8f4b2d-05ce-472a-8c15-30ffa48dd66f) The foreign key: ![Screenshot from 2024-03-06 14-54-32](https://github.com/doctrine/orm/assets/70450336/ef43a115-b552-4a7a-a4e0-b0e9f6ec5fbc) The QueryBuilder: ![Screenshot from 2024-03-06 14-53-41](https://github.com/doctrine/orm/assets/70450336/16eadfe0-37ae-47b9-bc39-d2d68b493cbd) The Symfony Profiler Doctrine tab: ![Screenshot from 2024-03-06 14-52-03](https://github.com/doctrine/orm/assets/70450336/96360ae5-bb52-45e4-84b9-15a731b13292)

admin closed this issue

2026-01-22 15:50:16 +01:00

admin commented

2026-01-22 15:50:17 +01:00

@gnito-org commented on GitHub (Mar 16, 2024):

Incorrect diagnosis. Please refer to https://github.com/doctrine/orm/issues/11358.

@gnito-org commented on GitHub (Mar 16, 2024): Incorrect diagnosis. Please refer to https://github.com/doctrine/orm/issues/11358.

Sign in to join this conversation.