Derived Table access to Outer Query Scope

[andy-wm-arthur]

error: 'table not found: dcim_rackgroup, maybe you mean dcim_rackgroup?' for query:

SELECT COUNT(*) FROM (
	SELECT (
		SELECT count(*) 
		FROM (
			SELECT U0.`id` 
			FROM `dcim_rack` U0 
			INNER JOIN `dcim_rackgroup` U1 
			ON (U0.`group_id` = U1.`id`) 
			WHERE (
				U1.`lft` >= `dcim_rackgroup`.`lft` AND 
				U1.`lft` <= `dcim_rackgroup`.`rght` AND 
				U1.`tree_id` = `dcim_rackgroup`.`tree_id`
			)
		) _count
	) AS `rack_count` 
	FROM `dcim_rackgroup` 
	WHERE `dcim_rackgroup`.`id` 
	IN ('418dd0dd47504bb190f354cf23ded6a6', 'd6d30bef4def4b66bcd180d4252eca7d', '34a74e488171481b96b222bf56a55bb9', '289e27c03cee4c299a3fa10517b54c52')
) subquery

schema:

CREATE TABLE `dcim_rackgroup` (
  `id` char(32) NOT NULL,
  `lft` int unsigned NOT NULL,
  `rght` int unsigned NOT NULL,
  `tree_id` int unsigned NOT NULL,
  `level` int unsigned NOT NULL,
  `parent_id` char(32),
  PRIMARY KEY (`id`),
  KEY `dcim_rackgroup_tree_id_9c2ad6f4` (`tree_id`),
  CONSTRAINT `dcim_rackgroup_parent_id_cc315105_fk_dcim_rackgroup_id` FOREIGN KEY (`parent_id`) REFERENCES `dcim_rackgroup` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;

CREATE TABLE `dcim_rack` (
  `id` char(32) NOT NULL,
  `group_id` char(32),
  PRIMARY KEY (`id`),
  KEY `dcim_rack_group_id_44e90ea9` (`group_id`),
  CONSTRAINT `dcim_rack_group_id_44e90ea9_fk_dcim_rackgroup_id` FOREIGN KEY (`group_id`) REFERENCES `dcim_rackgroup` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;

[fulghum]

This appears to be a newly removed restriction in MySQL, starting in 8.0.14. From the MySQL Derived Table reference docs:

Prior to MySQL 8.0.14, a derived table cannot contain outer references. This is a MySQL restriction that is lifted in MySQL 8.0.14, not a restriction of the SQL standard.

Dolt currently matches the pre-8.0.14 behavior, where derived tables do not have visibility to any tables or expressions from outer scopes. Passing the scope information through should be fairly straightforward, but we'll also need to do some testing to make sure we understand the exact visibility rules so we can match MySQL's behavior, and then see if any changes are needed to execute the query.

[fulghum]

There is a nice MySQL blog post announcing and explaining this feature. It is similar to LATERAL derived table support (#4130), but different in that the LATERAL keyword allows a derived table to access the table(s) listed before it in the same from clause, and this feature allows a derived table to access tables in the outer scopes of the from clause where it was defined.

[fulghum]

Quick update on progress... I landed some alias changes yesterday that were also needed by this feature to help track subquery scopes better. I've got the visibility portion for this feature working, but I'm still debugging through some queries that aren't returning the same results as MySQL. I'll keep working on this one and expect to have this working by the end of this week.

[fulghum]

I've got support for this new feature coded up in the linked pull request (dolthub/go-mysql-server#1310) and ready for feedback from teammates. I'll update again as we progress towards releasing this.

[fulghum]

Sorry for the slow response on this one. Progress was steady, but slow, over the past week while I worked through adding caching back in for subquery aliases (previously we could always cache any subquery alias, but it now requires more analysis to tell if caching is safe). It also took some time to test against our large, deeply nested integration query plans. I've worked through the issues and have all GMS and Dolt tests passing. I'll get Max and Zach to take a look at the latest code today and Monday and see if they have any feedback before we merge it in and will post another update here.

[fulghum]

Just closing the loop on this one... the support for derived tables with outer scope visibility landed in go-mysql-server yesterday and is prepped to go out in a Dolt release later today.