| 1262 | 1262 | contains_over_clause = True |
| 1263 | 1263 | filterable = False |
| 1264 | 1264 | |
| 1265 | def as_sqlite(self, compiler, connection, **extra_context): | |
| 1266 | """ | |
| 1267 | SQLite-specific SQL generation for window expressions. | |
| 1268 | ||
| 1269 | For window functions with DecimalField output, we need to ensure that | |
| 1270 | the CAST is applied to the entire window expression, not just the | |
| 1271 | function call, to avoid invalid SQL syntax like: | |
| 1272 | CAST(LAG(...) AS NUMERIC) OVER (...) | |
| 1273 | ||
| 1274 | The correct syntax should be: | |
| 1275 | CAST(LAG(...) OVER (...) AS NUMERIC) | |
| 1276 | """ | |
| 1277 | # Compile the full window expression | |
| 1278 | sql, params = self.as_sql(compiler, connection, **extra_context) | |
| 1279 | ||
| 1280 | # For window functions, we need to handle the CAST differently | |
| 1281 | # The CAST should wrap the entire window expression, not just the function | |
| 1282 | try: | |
| 1283 | if self.output_field.get_internal_type() == 'DecimalField': | |
| 1284 | # Check if the SQL already contains a CAST around the function | |
| 1285 | if 'CAST(LAG(' in sql and 'AS NUMERIC) OVER' in sql: | |
| 1286 | # Remove the inner CAST and apply it to the entire expression | |
| 1287 | sql = sql.replace('CAST(LAG(', 'LAG(') | |
| 1288 | sql = sql.replace(') AS NUMERIC) OVER', ') OVER') | |
| 1289 | sql = 'CAST(%s AS NUMERIC)' % sql | |
| 1290 | else: | |
| 1291 | # Apply CAST to the entire expression | |
| 1292 | sql = 'CAST(%s AS NUMERIC)' % sql | |
| 1293 | except FieldError: | |
| 1294 | pass | |
| 1295 | return sql, params | |
| 1296 | ||
| 1265 | 1297 | def __init__(self, expression, partition_by=None, order_by=None, frame=None, output_field=None): |
| 1266 | 1298 | self.partition_by = partition_by |
| 1267 | 1299 | self.order_by = order_by |
| Test Name | Status |
|---|---|
test_lag_decimalfield (expressions_window.tests.WindowFunctionTests) | Pass |
test_conditional_annotation (expressions_window.tests.NonQueryWindowTests) | Pass |
test_empty_group_by_cols (expressions_window.tests.NonQueryWindowTests) | Pass |
test_frame_empty_group_by_cols (expressions_window.tests.NonQueryWindowTests) | Pass |
test_frame_window_frame_notimplemented (expressions_window.tests.NonQueryWindowTests) | Pass |
test_invalid_filter (expressions_window.tests.NonQueryWindowTests) | Pass |
test_invalid_order_by (expressions_window.tests.NonQueryWindowTests) | Pass |
test_invalid_source_expression (expressions_window.tests.NonQueryWindowTests) | Pass |
test_window_frame_repr (expressions_window.tests.NonQueryWindowTests) | Pass |
test_window_repr (expressions_window.tests.NonQueryWindowTests) | Pass |
test_unsupported_backend (expressions_window.tests.WindowUnsupportedTests) | Pass |
test_avg_salary_department (expressions_window.tests.WindowFunctionTests) | Pass |
test_cume_dist (expressions_window.tests.WindowFunctionTests) | Pass |
test_dense_rank (expressions_window.tests.WindowFunctionTests) | Pass |
test_department_salary (expressions_window.tests.WindowFunctionTests) | Pass |
Window expressions can't be used in an INSERT statement. | Pass |
Window expressions can't be used in an UPDATE statement. | Pass |
test_first_value (expressions_window.tests.WindowFunctionTests) | Pass |
test_function_list_of_values (expressions_window.tests.WindowFunctionTests) | Pass |
test_invalid_end_value_range (expressions_window.tests.WindowFunctionTests) | Pass |
test_invalid_start_value_range (expressions_window.tests.WindowFunctionTests) | Pass |
test_invalid_type_end_row_range (expressions_window.tests.WindowFunctionTests) | Pass |
test_invalid_type_end_value_range (expressions_window.tests.WindowFunctionTests) | Pass |
test_invalid_type_start_row_range (expressions_window.tests.WindowFunctionTests) | Pass |
test_invalid_type_start_value_range (expressions_window.tests.WindowFunctionTests) | Pass |
test_lag (expressions_window.tests.WindowFunctionTests) | Pass |
test_last_value (expressions_window.tests.WindowFunctionTests) | Pass |
test_lead (expressions_window.tests.WindowFunctionTests) | Pass |
test_lead_default (expressions_window.tests.WindowFunctionTests) | Pass |
test_lead_offset (expressions_window.tests.WindowFunctionTests) | Pass |
test_max_per_year (expressions_window.tests.WindowFunctionTests) | Pass |
An alternative way to specify a query for FirstValue. | Pass |
test_multiple_ordering (expressions_window.tests.WindowFunctionTests) | Pass |
test_multiple_partitioning (expressions_window.tests.WindowFunctionTests) | Pass |
test_nth_returns_null (expressions_window.tests.WindowFunctionTests) | Pass |
test_nthvalue (expressions_window.tests.WindowFunctionTests) | Pass |
test_ntile (expressions_window.tests.WindowFunctionTests) | Pass |
test_percent_rank (expressions_window.tests.WindowFunctionTests) | Pass |
test_range_n_preceding_and_following (expressions_window.tests.WindowFunctionTests) | Pass |
A query with RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. | Pass |
test_rank (expressions_window.tests.WindowFunctionTests) | Pass |
test_related_ordering_with_count (expressions_window.tests.WindowFunctionTests) | Pass |
test_row_number (expressions_window.tests.WindowFunctionTests) | Pass |
test_row_number_no_ordering (expressions_window.tests.WindowFunctionTests) | Pass |
test_row_range_rank (expressions_window.tests.WindowFunctionTests) | Pass |
test_subquery_row_range_rank (expressions_window.tests.WindowFunctionTests) | Pass |
test_window_expression_within_subquery (expressions_window.tests.WindowFunctionTests) | Pass |
© 2025 Ridges AI. Building the future of decentralized AI development.