Open tbantle22 opened 5 days ago
A query in our hostedapi database (dolt version 1.35.10) sometimes returns an empty set when it shouldn't.
mysql> SELECT email_addresses.* FROM email_addresses JOIN `users` ON email_addresses.id = users.primary_email_fk WHERE users.id = '2a1e3627-6b0d-4788-b3d9-27bb83f059e7'; Empty set (0.01 sec) mysql> SELECT email_addresses.* FROM email_addresses JOIN `users` ON email_addresses.id = users.primary_email_fk WHERE users.id = '2a1e3627-6b0d-4788-b3d9-27bb83f059e7'; +--------------------------------------+--------------------------------------+-----------------+-------------+---------------------+---------------------+ | id | user_id_fk | address | is_verified | updated_at | created_at | +--------------------------------------+--------------------------------------+-----------------+-------------+---------------------+---------------------+ | 0e85068d-9afd-448a-be20-48854ebb8baf | 2a1e3627-6b0d-4788-b3d9-27bb83f059e7 | max@dolthub.com | 1 | 2024-02-16 22:32:03 | 2024-02-16 22:32:03 | +--------------------------------------+--------------------------------------+-----------------+-------------+---------------------+---------------------+ 1 row in set (0.00 sec)
These are the schemas of the users and email_addresses tables:
users
email_addresses
mysql> show create table users; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | users | CREATE TABLE `users` ( `id` varchar(36) NOT NULL, `name` varchar(32) NOT NULL, `display_name` varchar(128), `company` varchar(128), `updated_at` timestamp(6), `created_at` timestamp(6), `primary_email_fk` varchar(36), `saml_provider_id_fk` varchar(36), PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE KEY `name` (`name`), KEY `primary_email_fk` (`primary_email_fk`), KEY `saml_provider_id_fk` (`saml_provider_id_fk`), CONSTRAINT `2gv1aord` FOREIGN KEY (`primary_email_fk`) REFERENCES `email_addresses` (`id`) ON DELETE RESTRICT, CONSTRAINT `users_saml_provider_id_fk_constraint` FOREIGN KEY (`saml_provider_id_fk`) REFERENCES `saml_identity_providers` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table email_addresses; +-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | email_addresses | CREATE TABLE `email_addresses` ( `id` varchar(36) NOT NULL, `user_id_fk` varchar(36), `address` varchar(320) NOT NULL, `is_verified` tinyint NOT NULL, `updated_at` timestamp(6), `created_at` timestamp(6), PRIMARY KEY (`id`), UNIQUE KEY `address` (`address`), UNIQUE KEY `id` (`id`), KEY `user_id_fk` (`user_id_fk`), CONSTRAINT `etjvpu9s` FOREIGN KEY (`user_id_fk`) REFERENCES `users` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin | +-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
The user and email address IDs both exist:
mysql> select * from users where id = '2a1e3627-6b0d-4788-b3d9-27bb83f059e7'; +--------------------------------------+-------------+--------------+---------+---------------------+---------------------+--------------------------------------+---------------------+ | id | name | display_name | company | updated_at | created_at | primary_email_fk | saml_provider_id_fk | +--------------------------------------+-------------+--------------+---------+---------------------+---------------------+--------------------------------------+---------------------+ | 2a1e3627-6b0d-4788-b3d9-27bb83f059e7 | max-hoffman | | | 2024-02-16 22:32:03 | 2024-02-16 22:32:03 | 0e85068d-9afd-448a-be20-48854ebb8baf | NULL | +--------------------------------------+-------------+--------------+---------+---------------------+---------------------+--------------------------------------+---------------------+ 1 row in set (0.00 sec) mysql> select * from email_addresses where id='0e85068d-9afd-448a-be20-48854ebb8baf'; +--------------------------------------+--------------------------------------+-----------------+-------------+---------------------+---------------------+ | id | user_id_fk | address | is_verified | updated_at | created_at | +--------------------------------------+--------------------------------------+-----------------+-------------+---------------------+---------------------+ | 0e85068d-9afd-448a-be20-48854ebb8baf | 2a1e3627-6b0d-4788-b3d9-27bb83f059e7 | max@dolthub.com | 1 | 2024-02-16 22:32:03 | 2024-02-16 22:32:03 | +--------------------------------------+--------------------------------------+-----------------+-------------+---------------------+---------------------+ 1 row in set (0.00 sec)
A query in our hostedapi database (dolt version 1.35.10) sometimes returns an empty set when it shouldn't.
These are the schemas of the
users
andemail_addresses
tables:The user and email address IDs both exist: