Pagination fails with distinct (Grammer: MS SQL SERVER)

[srconklin]

When drafting a QB query with distinct() it constructs an invalid query select list:

`

Message | stringIncorrect syntax near '*'. | string | Incorrect syntax near '*'. -- | -- | -- | -- string | Incorrect syntax near '*'. NativeErrorCode | number102 | number | 102 number | 102 queryError | stringSELECT COUNT(DISTINCT *) AS "aggregate" FROM [batches] INNER JOIN [projects] ON [projects].[project_id] = [batches].[project_id] INNER JOIN [users] ON [users].[user_id] = [batches].[owner_user_id] INNER JOIN [statuses] ON [statuses].[statusID] = [batches].[status] INNER JOIN [labels] ON [labels].[label_key] = [batches].[department] INNER JOIN [assignments] ON [assignments].[batch_id] = [batches].[batch_id] WHERE [labels].[type] = 'dept_code' AND [batches].[status] = '1' AND [batches].[department] = '1' | string | SELECT COUNT(DISTINCT *) AS "aggregate" FROM [batches] INNER JOIN [projects] ON [projects].[project_id] = [batches].[project_id] INNER JOIN [users] ON [users].[user_id] = [batches].[owner_user_id] INNER JOIN [statuses] ON [statuses].[statusID] = [batches].[status] INNER JOIN [labels] ON [labels].[label_key] = [batches].[department] INNER JOIN [assignments] ON [assignments].[batch_id] = [batches].[batch_id] WHERE [labels].[type] = 'dept_code' AND [batches].[status] = '1' AND [batches].[department] = '1' string | SELECT COUNT(DISTINCT *) AS "aggregate" FROM [batches] INNER JOIN [projects] ON [projects].[project_id] = [batches].[project_id] INNER JOIN [users] ON [users].[user_id] = [batches].[owner_user_id] INNER JOIN [statuses] ON [statuses].[statusID] = [batches].[status] INNER JOIN [labels] ON [labels].[label_key] = [batches].[department] INNER JOIN [assignments] ON [assignments].[batch_id] = [batches].[batch_id] WHERE [labels].[type] = 'dept_code' AND [batches].[status] = '1' AND [batches].[department] = '1'

`

I found that by removing the reference to the DISTINCT keyword in the following function, the paginate works as expected: Wondering why this built to test if distinct is present?

` private string function compileAggregate( required QueryBuilder query, required struct aggregate ) { if ( aggregate.isEmpty() ) { return ""; } return "SELECT #uCase( aggregate.type )#(#query.getDistinct() ? "DISTINCT " : ""##wrapColumn( aggregate.column )#) AS ""aggregate"""; }

`

[srconklin]

This appears to be still happening in the latest version

[elpete]

Can you provide me with a query that is breaking?

[srconklin]

SELECT COUNT(DISTINCT *) AS "aggregate" FROM [batches] INNER JOIN [projects] ON [projects].[project_id] = [batches].[project_id] INNER JOIN [users] ON [users].[user_id] = [batches].[owner_user_id] INNER JOIN [statuses] ON [statuses].[statusID] = [batches].[status] INNER JOIN [labels] ON [labels].[label_key] = [batches].[department] INNER JOIN [assignments] ON [assignments].[batch_id] = [batches].[batch_id] WHERE [labels].[type] = 'dept_code' AND [batches].[status] = '1' AND [batches].[department] = '1'

[elpete]

Is that the correct SQL or incorrect? Also, can I get the qb code that is generating that?

[srconklin]

that is the incorrect sql. (distinct can't be in the aggregate function () )

[srconklin]

local.projects = _wirebox.getInstance( "QueryBuilder@qb" ) .select( [ "projects.project_id", "projects.project_number", "projects.project_name", "projects.project_description", "projects.budget", "batches.date_due", "batches.department", "batches.batch_number", "batches.batch_description", "batches.batch_id AS the_batch_id", "batches.owner_user_id", "users.full_name as owner", "statuses.status" ] ) .distinct() .selectRaw( "COALESCE(trim(batches.client_lastname), '') + case when len(COALESCE(trim(batches.client_firstname),'')) > 0 then ', ' else '' end + COALESCE(trim(batches.client_firstname),'') as client" ) .selectRaw( "'##' + cast(batches.batch_number as varchar(3)) + ' (' + labels.txt + ') ' + isnull(batches.batch_description, '') as batch" ) .selectRaw( "case when batches.status = 1 then batches.time_created else case when batches.status = 2 then batches.time_created else case when batches.status = 3 then batches.time_recharged else 'Unknown' end end end as statusDate " ) .subSelect( "actualCharge", function( q ){ q.selectRaw( "SUM(total)" ) .from( "entries" ) .whereIn( "batch_id", ( q ) => { q.select( "batch_id" ) .from( "batches" ) .whereColumn( "batches.project_id", "projects.project_id" ) }); } ) .subSelect( "total", function( q ){ q.selectRaw( "SUM(total)" ) .from( "entries" ) .whereColumn( "entries.batch_id", "batches.batch_id" ); } ) .from( "batches" ) .join( "projects", "projects.project_id", "batches.project_id" ) .join( "users", "users.user_id", "batches.owner_user_id" ) .join( "statuses", "statuses.statusID", "batches.status" ) .join( "labels", "labels.label_key", "=", "batches.department" ) .join( "assignments", "assignments.batch_id", "batches.batch_id" ) .where( "labels.type", [ "dept_code" ] ) // .where( "batches.status", arguments.status ) .when( len( arguments.status ), function( q ){ q.where( "batches.status", status ); } ) .when( len( arguments.projectID ), function( q ){ q.where( "batches.project_id", projectID ); } ) .when( len( arguments.qcrit ), function( q ){ searchableColumns.each( ( item ) => { q.wherelike( item, "%#qcrit#%", "or"); }); } );

[srconklin]

looks like the formatting is lost even using the code formatting option.

[srconklin]

FYI. In case I did not clarify. This error occurs when invoking the paginate method. The qb code is quite long but the issue can be seen in a much simpler example by simply adding distinct() to the qb configuration