`QueryBuilder` with `dialect` set performs differently than dialect specific builder

[matteo-zanoni]

When using a QueryBuilder with a specific dialect set I expected the same result as using the dialect-specific query builder.

For example by using the MSSQL dialect and the limit clause we can check for dialect specific behaviour (since LIMIT is not supported in Microsoft SQL Server).

from pypika.queries import QueryBuilder
from pypika.enums import Dialects

print(QueryBuilder(dialect=Dialects.MSSQL).from_("table").select("column").limit(1))

from pypika.dialects import MSSQLQueryBuilder

print(MSSQLQueryBuilder().from_("table").select("column").limit(1))

I expected those two code snippets to produce the same output, instead the first outputs

SELECT "column" FROM "table" LIMIT 1

And the second

SELECT "column" FROM "table" OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

The correct query for the MSSQL dialect is the second one.

[wd60622]

My understanding is that you'd want to use MSSQLQuery instead of MSSQLQueryBuilder directly. Just as Query is used instead of QueryBuilder directly

from pypika.dialects import MSSQLQuery

MSSQLQuery.from_("table").select("column").limit(1)
# SELECT "column" FROM "table" OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

Has the correct query

[matteo-zanoni]

@wd60622 thanks for the explanation! What would be the purpose of QueryBuilder and MSSQLQuerybuilder? When are they meant to be used?

[wd60622]

@wd60622 thanks for the explanation! What would be the purpose of QueryBuilder and MSSQLQuerybuilder? When are they meant to be used?

They are used internally of their respective Query class to my understanding. The Query class has "primary entrypoint" noted in its docstring.

I do agree what you noticed is confusing behavior