[SqlServer] findCount + distinct query + Sql Server can produce PersistenceException: Query threw SQLException:The column x was specified multiple times for 'c'

[rbygrave]

Test case:

    Query<Customer> query = DB.find(Customer.class)
      .setDistinct(true)
      .select("id, anniversary, status")
      .fetch("billingAddress", "id, city")
      .fetch("shippingAddress", "id, city")
      .findCount();

For SQL Server this produces sql:

select count(*) from ( 
  select distinct t0.anniversary, t0.status, t1.id, t1.city, t2.id, t2.city 
  from o_customer t0 
  left join o_address t1 on t1.id = t0.billing_address_id 
  left join o_address t2 on t2.id = t0.shipping_address_id
) as c

The issue is that SQL Server specifically can't handle this SQL because it deems the columns id and city as not being unique in c [even though the columns come from different tables etc].

The Bug:

The SqlServerBasePlatform has selectCountWithColumnAlias set to true, and it should be used for this type of "count query" However, in CQueryBuilder it is NOT using this setting for a count query when the underlying query is a distinct [but instead only using it when the underlying query includes aggregation ].

The Fix

Fix in CQueryBuilder such that it uses the databasePlatform selectCountWithColumnAlias setting for count queries with distinct / or said differently, use the platform column alias for all count queries except for the case when the underlying query select can be simplified down to selectId()

With the fix in place the sql generated for SQL Server is:

select count(*) from ( 
  select distinct t0.anniversary c0, t0.status c1, t1.id c2, t1.city c3, t2.id c4, t2.city c5 
  from o_customer t0 
  left join o_address t1 on t1.id = t0.billing_address_id 
  left join o_address t2 on t2.id = t0.shipping_address_id
) as c

... note the use of the column alias of c0 c1 etc.

[rbygrave]

FYI @nedge