Issue when using union(), orderBy() and paginate()

[jtomlinson]

I've run into an issue with the way the SingleStore driver handles the following code block.

$orders = Order::select(['orders.make', 'orders.part_no'])
  ->where('orders.dealer_id', 1407)
  ->whereDate('orders.order_date', '>', now()->subWeek());

$shipments = Shipment::select(['shipments.make', 'shipments.part_no'])
  ->where('shipments.dealer_id', 1407)
  ->whereDate('shipments.shipped_date', '>', now()->subWeek());

$all = $orders->union($shipments);

$all->orderBy('part_no');

$all->paginate();

SingleStore driver generates the query as follows:

SELECT
FROM (select count() as aggregate
      from (SELECT
            FROM (SELECT *
                  FROM (select orders.make, orders.part_no
                        from scanitparts.orders
                        where orders.dealer_id = 1407
                          and date(orders.order_date) > 2023 - 12 - 04)
                  union
                  (select shipments.make, shipments.part_no
                   from scanitparts.shipments
                   where shipments.dealer_id = 1407
                     and date(shipments.shipped_date) > 2023 - 12 - 04))
            order by part_no asc) as temp_table)
order by part_no asc

When using the mysql driver the same code block generates the following query:

select count(*) as aggregate
from ((select `orders`.`make`, `orders`.`part_no`
       from `scanitparts`.`orders`
       where `orders`.`dealer_id` = 1407
         and date(`orders`.`order_date`) > '2023-12-04')
      union
      (select `shipments`.`make`, `shipments`.`part_no`
       from `scanitparts`.`shipments`
       where `shipments`.`dealer_id` = 1407
         and date(`shipments`.`shipped_date`) > '2023-12-04')
      order by `part_no` asc) as `temp_table`

The SingleStore driver is adding an extra Select From ... order by on the count() result, which then creates the error Unknown column 'part_no' in 'order clause'. I assume the SingleStore driver should function the same way the mysql driver does in this example?

[rzv-me]

I created a PR with a fix for this issue. The problem is that when having an aggregate query with unions or having the query can just be returned, no need to wrap it and add orders, limit or offset