GetField error for query with recursive CTE

[andy-wm-arthur]

error unable to find field with index 21 in row of 5 columns

repro:

WITH RECURSIVE __tree(tree_depth, tree_path, tree_ordering, tree_pk) 
AS ( 
	SELECT 
		0, -- Limit to max. 50 levels... 
		CAST(CONCAT(0x1f, id, 0x1f) AS char(1000)), 
		CAST(CONCAT(0x1f, CONCAT(_name, 0x1f)) AS char(1000)), 
		T.id 
	FROM dcim_location T 
	WHERE T.parent_id IS NULL 
	UNION ALL 
	SELECT 
		__tree.tree_depth + 1, 
		CONCAT(__tree.tree_path, T2.id, 0x1f), 
		CONCAT(__tree.tree_ordering, CONCAT(T2._name, 0x1f)), 
		T2.id 
	FROM __tree, dcim_location T2 
	WHERE __tree.tree_pk = T2.parent_id 
) 
SELECT 
	(__tree.tree_depth) AS tree_depth, 
	(__tree.tree_path) AS tree_path, 
	(__tree.tree_ordering) AS tree_ordering, 
	dcim_location.id, 
	dcim_location.created, 
	dcim_location.last_updated, 
	dcim_location._custom_field_data, 
	dcim_location.status_id, 
	dcim_location.parent_id, 
	dcim_location.name, 
	dcim_location._name, 
	dcim_location.slug, 
	dcim_location.location_type_id, 
	dcim_location.site_id, 
	dcim_location.tenant_id, 
	dcim_location.description 
FROM 
	dcim_location ,
	__tree 
WHERE (
	dcim_location.description = '' AND 
	dcim_location.id IN ( 
		WITH RECURSIVE 
			__tree(tree_depth, tree_path, tree_ordering, tree_pk) 
		AS ( 
			SELECT 0, -- Limit to max. 50 levels... 
				CAST(CONCAT(0x1f, id, 0x1f) AS char(1000)), 
				CAST(CONCAT(0x1f, CONCAT(_name, 0x1f)) AS char(1000)), 
				T.id 
			FROM dcim_location T 
			WHERE T.parent_id IS NULL 
			UNION ALL 
			SELECT 
				__tree.tree_depth + 1, 
				CONCAT(__tree.tree_path, T2.id, 0x1f), 
				CONCAT(__tree.tree_ordering, 
				CONCAT(T2._name, 0x1f)), 
				T2.id 
			FROM __tree, dcim_location T2 
			WHERE __tree.tree_pk = T2.parent_id 
		) 
		SELECT 
			U0.id 
		FROM dcim_location U0 , __tree 
		WHERE (
			U0.id IN ('88325cda24804decbdd51f65811d4beb', 'cb5fe329fe3846efb8ff35dfb63cf7d3', 'd5ebb9035b194066a6b9a86ac74667b1') AND
			(__tree.tree_pk = dcim_location.id)
		)
	) AND 
	(__tree.tree_pk = dcim_location.id)
) 
ORDER BY (__tree.tree_ordering) ASC;

setup:

CREATE TABLE dcim_location (
  id char(32) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  created date,
  last_updated datetime,
  _custom_field_data json NOT NULL,
  name varchar(100) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  _name varchar(100) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  slug varchar(100) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  description varchar(200) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  location_type_id char(32) COLLATE utf8mb4_0900_ai_ci NOT NULL,
  parent_id char(32) COLLATE utf8mb4_0900_ai_ci,
  site_id char(32) COLLATE utf8mb4_0900_ai_ci,
  status_id char(32) COLLATE utf8mb4_0900_ai_ci,
  tenant_id char(32) COLLATE utf8mb4_0900_ai_ci,
  PRIMARY KEY (id),
  KEY dcim_location__name_09ea12a5 (_name),
  KEY dcim_location_location_type_id_511a9421 (location_type_id),
  KEY dcim_location_name_da208275 (name),
  KEY dcim_location_parent_id_d77f3318 (parent_id),
  KEY dcim_location_site_id_b55e975f (site_id),
  KEY dcim_location_status_id_3d74f10a (status_id),
  KEY dcim_location_tenant_id_2c4df974 (tenant_id),
  UNIQUE KEY parent_idname (parent_id,name),
  UNIQUE KEY slug (slug)
  -- CONSTRAINT dcim_location_location_type_id_511a9421_fk_dcim_locationtype_id FOREIGN KEY (location_type_id) REFERENCES dcim_locationtype (id),
  -- CONSTRAINT dcim_location_parent_id_d77f3318_fk_dcim_location_id FOREIGN KEY (parent_id) REFERENCES dcim_location (id),
  -- CONSTRAINT dcim_location_site_id_b55e975f_fk_dcim_site_id FOREIGN KEY (site_id) REFERENCES dcim_site (id),
  -- CONSTRAINT dcim_location_status_id_3d74f10a_fk_extras_status_id FOREIGN KEY (status_id) REFERENCES extras_status (id),
  -- CONSTRAINT dcim_location_tenant_id_2c4df974_fk_tenancy_tenant_id FOREIGN KEY (tenant_id) REFERENCES tenancy_tenant (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `dcim_location` (`id`, `created`, `last_updated`, `_custom_field_data`, `status_id`, `parent_id`, `name`, `_name`, `slug`, `location_type_id`, `site_id`, `tenant_id`, `description`) VALUES ('88325cda24804decbdd51f65811d4beb', '2022-12-02', '2022-12-02 21:59:52.260756', '{}', '57542551ab7e4345bd7d655a362c063b', NULL, 'Root 1', 'Root 00000001', 'root-1', '2f256bbd27c247ebb7f1571667911971', '323a307618e949a69dda02c8bcec976d', NULL, '');
INSERT INTO `dcim_location` (`id`, `created`, `last_updated`, `_custom_field_data`, `status_id`, `parent_id`, `name`, `_name`, `slug`, `location_type_id`, `site_id`, `tenant_id`, `description`) VALUES ('cb5fe329fe3846efb8ff35dfb63cf7d3', '2022-12-02', '2022-12-02 21:59:52.318852', '{}', '57542551ab7e4345bd7d655a362c063b', 'd5ebb9035b194066a6b9a86ac74667b1', 'Intermediate 1', 'Intermediate 00000001', 'root-2-intermediate-1', '0e0ef2ee1651496b93aa765d50fe1e4b', NULL, NULL, '');
INSERT INTO `dcim_location` (`id`, `created`, `last_updated`, `_custom_field_data`, `status_id`, `parent_id`, `name`, `_name`, `slug`, `location_type_id`, `site_id`, `tenant_id`, `description`) VALUES ('d5ebb9035b194066a6b9a86ac74667b1', '2022-12-02', '2022-12-02 21:59:52.289486', '{}', '57542551ab7e4345bd7d655a362c063b', NULL, 'Root 2', 'Root 00000002', 'root-2', '2f256bbd27c247ebb7f1571667911971', '323a307618e949a69dda02c8bcec976d', '0e4b3cb6d3324074be27248ec8bc0dd1', '');
INSERT INTO `dcim_location` (`id`, `created`, `last_updated`, `_custom_field_data`, `status_id`, `parent_id`, `name`, `_name`, `slug`, `location_type_id`, `site_id`, `tenant_id`, `description`) VALUES ('efec34e999df47e7aa2174cc390dc63f', '2022-12-02', '2022-12-02 21:59:52.348176', '{}', '57542551ab7e4345bd7d655a362c063b', 'cb5fe329fe3846efb8ff35dfb63cf7d3', 'Leaf 1', 'Leaf 00000001', 'root-2-intermediate-1-leaf-1', 'd8895e7565ab4d96baf08d530bb05846', NULL, NULL, 'Hi!');
UPDATE `dcim_location` SET `created` = '2022-12-02', `last_updated` = '2022-12-02 21:59:52.402276', `_custom_field_data` = '{}', `status_id` = '57542551ab7e4345bd7d655a362c063b', `parent_id` = NULL, `name` = 'Root 1', `_name` = 'Root 00000001', `slug` = 'root-1', `location_type_id` = '2f256bbd27c247ebb7f1571667911971', `site_id` = '323a307618e949a69dda02c8bcec976d', `tenant_id` = NULL, `description` = '' WHERE `dcim_location`.`id` = '88325cda24804decbdd51f65811d4beb';
UPDATE `dcim_location` SET `created` = '2022-12-02', `last_updated` = '2022-12-02 21:59:52.729240', `_custom_field_data` = '{}', `status_id` = '57542551ab7e4345bd7d655a362c063b', `parent_id` = NULL, `name` = 'Root 2', `_name` = 'Root 00000002', `slug` = 'root-2', `location_type_id` = '2f256bbd27c247ebb7f1571667911971', `site_id` = '323a307618e949a69dda02c8bcec976d', `tenant_id` = '0e4b3cb6d3324074be27248ec8bc0dd1', `description` = '' WHERE `dcim_location`.`id` = 'd5ebb9035b194066a6b9a86ac74667b1';
UPDATE `dcim_location` SET `created` = '2022-12-02', `last_updated` = '2022-12-02 21:59:53.051581', `_custom_field_data` = '{}', `status_id` = '57542551ab7e4345bd7d655a362c063b', `parent_id` = 'd5ebb9035b194066a6b9a86ac74667b1', `name` = 'Intermediate 1', `_name` = 'Intermediate 00000001', `slug` = 'root-2-intermediate-1', `location_type_id` = '0e0ef2ee1651496b93aa765d50fe1e4b', `site_id` = NULL, `tenant_id` = NULL, `description` = '' WHERE `dcim_location`.`id` = 'cb5fe329fe3846efb8ff35dfb63cf7d3';
UPDATE `dcim_location` SET `created` = '2022-12-02', `last_updated` = '2022-12-02 21:59:53.360886', `_custom_field_data` = '{}', `status_id` = '57542551ab7e4345bd7d655a362c063b', `parent_id` = 'cb5fe329fe3846efb8ff35dfb63cf7d3', `name` = 'Leaf 1', `_name` = 'Leaf 00000001', `slug` = 'root-2-intermediate-1-leaf-1', `location_type_id` = 'd8895e7565ab4d96baf08d530bb05846', `site_id` = NULL, `tenant_id` = NULL, `description` = 'Hi!' WHERE `dcim_location`.`id` = 'efec34e999df47e7aa2174cc390dc63f';
UPDATE `dcim_location` SET `parent_id` = NULL WHERE `dcim_location`.`id` IN ('efec34e999df47e7aa2174cc390dc63f', 'cb5fe329fe3846efb8ff35dfb63cf7d3');
UPDATE `dcim_location` SET `parent_id` = NULL WHERE `dcim_location`.`id` IN ('efec34e999df47e7aa2174cc390dc63f', 'cb5fe329fe3846efb8ff35dfb63cf7d3');
INSERT INTO `dcim_location` (`id`, `created`, `last_updated`, `_custom_field_data`, `status_id`, `parent_id`, `name`, `_name`, `slug`, `location_type_id`, `site_id`, `tenant_id`, `description`) VALUES ('60efbb4bc6d744a3959ac1a488bc252e', '2022-12-02', '2022-12-02 21:59:58.846308', '{}', '57542551ab7e4345bd7d655a362c063b', 'cb5fe329fe3846efb8ff35dfb63cf7d3', 'Leaf 2', 'Leaf 00000002', 'leaf-2', 'd8895e7565ab4d96baf08d530bb05846', NULL, '0e4b3cb6d3324074be27248ec8bc0dd1', '');
INSERT INTO `dcim_location` (`id`, `created`, `last_updated`, `_custom_field_data`, `status_id`, `parent_id`, `name`, `_name`, `slug`, `location_type_id`, `site_id`, `tenant_id`, `description`) VALUES ('740bac3b12394a97930fc36fec7d8fb6', '2022-12-02', '2022-12-02 21:59:58.582866', '{}', '57542551ab7e4345bd7d655a362c063b', NULL, 'Root 3', 'Root 00000003', 'root-3', '2f256bbd27c247ebb7f1571667911971', '323a307618e949a69dda02c8bcec976d', NULL, '');
INSERT INTO `dcim_location` (`id`, `created`, `last_updated`, `_custom_field_data`, `status_id`, `parent_id`, `name`, `_name`, `slug`, `location_type_id`, `site_id`, `tenant_id`, `description`) VALUES ('e6e21cd255fa4232b279a3da800a0a47', '2022-12-02', '2022-12-02 21:59:58.712083', '{}', '57542551ab7e4345bd7d655a362c063b', 'd5ebb9035b194066a6b9a86ac74667b1', 'Intermediate 2', 'Intermediate 00000002', 'intermediate-2', '0e0ef2ee1651496b93aa765d50fe1e4b', NULL, '0e4b3cb6d3324074be27248ec8bc0dd1', 'Hello world!');

[fulghum]

I made some progress on this one today, but am still debugging through it...

I simplified this query and found a similar error where CrossJoins can't be executed in subquery expressions because they don't receive the correct outer row context. I've got a local patch that seems to fix this issue, but it needs more testing. The original query does have a CrossJoin and with this change, it is no longer erroring out on an out of range GetField expression, but it is not yet returning the correct results.

I'll keep digging deeper and should be able to have a PR out for the fix tomorrow.

[fulghum]

I got a PR opened for the CrossJoin scope visibility bug (dolthub/go-mysql-server#1459) and after merging the latest fixes on GMS main into my dev branch, the query above is now returning the correct results. 🎉

[fulghum]

The fix in GMS is merged to main and I'll get it into Dolt today; after that, I'll ping @PavelSafronov so he can rerun the customer's test and see how much farther we get.