Strange error with Large Join Query

[VinaiRachakonda]

Consider the following dump

SET FOREIGN_KEY_CHECKS = 0;
SET AUTOCOMMIT = 0;
DROP TABLE IF EXISTS `tweet`;
CREATE TABLE `tweet` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `content` text NOT NULL,
  `timestamp` bigint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tweet_user_id` (`user_id`),
  CONSTRAINT `0qpfesgd` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `tweet` (`id`,`user_id`,`content`,`timestamp`) VALUES (1,1,'meow',1647463727);
INSERT INTO `tweet` (`id`,`user_id`,`content`,`timestamp`) VALUES (2,1,'purr',1647463727);
INSERT INTO `tweet` (`id`,`user_id`,`content`,`timestamp`) VALUES (3,2,'hiss',1647463727);
INSERT INTO `tweet` (`id`,`user_id`,`content`,`timestamp`) VALUES (4,3,'woof',1647463727);
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `users` (`id`,`username`) VALUES (1,'huey');
INSERT INTO `users` (`id`,`username`) VALUES (2,'zaizee');
INSERT INTO `users` (`id`,`username`) VALUES (3,'mickey');

Consider the following query:

 SELECT `t1`.`username`, COUNT(`t1`.`id`) AS `ct` FROM ((SELECT `t2`.`id`, `t2`.`content`, `t3`.`username` FROM `tweet` AS `t2` INNER JOIN `users` AS `t3` ON (`t2`.`user_id` = `t3`.`id`) WHERE (`t3`.`username` = 'u3')) UNION (SELECT `t4`.`id`, `t4`.`content`, `t5`.`username` FROM `tweet` AS `t4` INNER JOIN `users` AS `t5` ON (`t4`.`user_id` = `t5`.`id`) WHERE (`t5`.`username` IN ('u2', 'u4')))) AS `t1` GROUP BY `t1`.`username` ORDER BY COUNT(`t1`.`id`) DESC;

Dolt returns the following error

unable to push plan.Sort node below *plan.Union

[VinaiRachakonda]

This blocks the Peewee ORM integration

[VinaiRachakonda]

Skipped enginetest here

[max-hoffman]

The union error is fixed, now blocked on an aliasing issue:

column "COUNT(t1.id)" could not be found in any table in scope

The error resolves if I manually convert COUNT(t1.id) -> ct

[timsehn]

@fulghum one for your test suite.

[fulghum]

I debugged through this one on Friday and have a prototype of a fix coded up. The fix is in pushdownSort – since the sort expression is the same as an expression projected by the child, we can leave the sort node where it is and not push it down, which lets the query execute correctly. I need to clean up my tests and think through the edge cases, and will get a PR up today for this one.