Empty result set from query with INNER JOIN + WHERE

[glennmatthews]

Appears to have been introduced in or around 1.42.10, as this was working for us previously.

We have a Django query (looking at a ManyToManyField's records) that's unexpectedly returning no results.

Working query 1 (SELECT with INNER JOIN):

SELECT
`users_objectpermission_object_types`.`objectpermission_id`,
`users_objectpermission_object_types`.`contenttype_id`,
`django_content_type`.`id`,
`django_content_type`.`app_label`,
`django_content_type`.`model`
FROM `django_content_type` INNER JOIN `users_objectpermission_object_types`
ON (`django_content_type`.`id` = `users_objectpermission_object_types`.`contenttype_id`);
+----------------------------------+----------------+-----+--------------------------+---------+
| objectpermission_id              | contenttype_id | id  | app_label                | model   |
+----------------------------------+----------------+-----+--------------------------+---------+
| 2c098bb37c3c4ad9a38946f34b728c91 |            156 | 156 | nautobot_version_control | branch  |
| 2c098bb37c3c4ad9a38946f34b728c91 |              1 |   1 | circuits                 | circuit |
| 2c098bb37c3c4ad9a38946f34b728c91 |             30 |  30 | auth                     | group   |
+----------------------------------+----------------+-----+--------------------------+---------+
3 rows in set (0.002 sec)

Working query 2 (SELECT with WHERE):

SELECT
`users_objectpermission_object_types`.`objectpermission_id`,
`users_objectpermission_object_types`.`contenttype_id`
FROM `users_objectpermission_object_types`
WHERE `users_objectpermission_object_types`.`objectpermission_id` = '2c098bb37c3c4ad9a38946f34b728c91';
+----------------------------------+----------------+
| objectpermission_id              | contenttype_id |
+----------------------------------+----------------+
| 2c098bb37c3c4ad9a38946f34b728c91 |              1 |
| 2c098bb37c3c4ad9a38946f34b728c91 |             30 |
| 2c098bb37c3c4ad9a38946f34b728c91 |            156 |
+----------------------------------+----------------+
3 rows in set (0.001 sec)

Failing query (SELECT with INNER JOIN and WHERE):

SELECT
`users_objectpermission_object_types`.`objectpermission_id`,
`users_objectpermission_object_types`.`contenttype_id`,
`django_content_type`.`id`,
`django_content_type`.`app_label`,
`django_content_type`.`model`
FROM `django_content_type` INNER JOIN `users_objectpermission_object_types`
ON (`django_content_type`.`id` = `users_objectpermission_object_types`.`contenttype_id`)
WHERE `users_objectpermission_object_types`.`objectpermission_id` = '2c098bb37c3c4ad9a38946f34b728c91';
Empty set (0.002 sec)

[timsehn]

We'll get this fixed today. Seems related to recent analyzer optimizations.

[max-hoffman]

Hi @glennmatthews, thanks for the repro. Would you mind helping me a bit with the schemas of django_content_type and users_objectpermission_object_types? (show create table django_content_type; and show create table users_objectpermission_object_types;)

[glennmatthews]

| django_content_type | CREATE TABLE `django_content_type` (
  `id` int NOT NULL AUTO_INCREMENT,
  `app_label` varchar(100) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `model` varchar(100) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `django_content_type_app_label_model_76bd3d3b_uniq` (`app_label`,`model`)
) ENGINE=InnoDB AUTO_INCREMENT=164 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |

| users_objectpermission_object_types | CREATE TABLE `users_objectpermission_object_types` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `objectpermission_id` char(32) NOT NULL,
  `contenttype_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `users_objectpermissi_contenttype_id_594b1cc7_fk_django_co` (`contenttype_id`),
  UNIQUE KEY `users_objectpermission_o_objectpermission_id_cont_7c40d31a_uniq` (`objectpermission_id`,`contenttype_id`),
  CONSTRAINT `users_objectpermissi_contenttype_id_594b1cc7_fk_django_co` FOREIGN KEY (`contenttype_id`) REFERENCES `django_content_type` (`id`),
  CONSTRAINT `users_objectpermissi_objectpermission_id_38c7d8f5_fk_users_obj` FOREIGN KEY (`objectpermission_id`) REFERENCES `users_objectpermission` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

[max-hoffman]

Thanks @glennmatthews, I have a repro now. I should have either a fix or a related update for you in a couple hours.

[max-hoffman]

This one was a little more difficult than most of the bugs that come through, but I'm still trying to get a fix by EOD if I can.

[max-hoffman]

This looks like it restores expected behavior https://github.com/dolthub/dolt/pull/8311. Apologies for the regression, we appreciate you taking the time to file a bug and help us repro.

[max-hoffman]

1.41.17 should include the fix. Let me know if you see any other suspicious join behavior.