Problem
I had a custom running random row validations on SQL Server. Random row values are bound into a query using query parameters. If there are multiple columns in a primary key then the 2100 query parameter limit can quickly be reached. I suggested the customer use the formula:
random row batch size = (2100 / num pk columns)
However this was not adequate because, in hash validation, DVT constructs a coalesce(cast__<col>, 'DEFAULT_REPLACEMENT_STRING') expression for every nullable column and SQLAlchemy compiles every column's 'DEFAULT_REPLACEMENT_STRING' as a separate bound ? parameter. Therefore, if a table had 1000 nullable columns the maximum random row batch size was cut in half.
Proposed Solution
Inline DEFAULT_REPLACEMENT_STRING as a SQL literal:
Render the constant replacement string inline in SQL (e.g., via sa.literal_column("'DEFAULT_REPLACEMENT_STRING'") or literal_binds in the fillna compilation path) rather than binding a ? parameter per column. This removes hashed_column_count from the parameter count across all SQLAlchemy-backed engines.
We should also validate the DEFAULT_REPLACEMENT_STRING token (which I believe can be overridden in a YAML config file) to protect against SQL injection.
Problem
I had a custom running random row validations on SQL Server. Random row values are bound into a query using query parameters. If there are multiple columns in a primary key then the 2100 query parameter limit can quickly be reached. I suggested the customer use the formula:
However this was not adequate because, in hash validation, DVT constructs a
coalesce(cast__<col>, 'DEFAULT_REPLACEMENT_STRING')expression for every nullable column and SQLAlchemy compiles every column's'DEFAULT_REPLACEMENT_STRING'as a separate bound?parameter. Therefore, if a table had 1000 nullable columns the maximum random row batch size was cut in half.Proposed Solution
Inline
DEFAULT_REPLACEMENT_STRINGas a SQL literal:Render the constant replacement string inline in SQL (e.g., via
sa.literal_column("'DEFAULT_REPLACEMENT_STRING'")orliteral_bindsin thefillnacompilation path) rather than binding a?parameter per column. This removeshashed_column_countfrom the parameter count across all SQLAlchemy-backed engines.We should also validate the DEFAULT_REPLACEMENT_STRING token (which I believe can be overridden in a YAML config file) to protect against SQL injection.