goldset

Whole file

David-Wobrock/sqlvalidator

The author described this change as “Implement union all and fix formatting”. It counts as a record because the check below fails on the code as it stood at 0e0640b17 and passes on 0a343e0af, with nothing else changed between the two runs.

ProjectDavid-Wobrock/sqlvalidator
Fix saved2020-12-31
Sharing licenceMIT · LICENSE
Change size+949 −945

What the code was meant to do, written into the code itself as a save note

Implement union all and fix formatting

The change

7272 from_statement = self.from_statement
7373
7474 if isinstance(from_statement, Parenthesis):
75- from_str = "(\n{}\n)".format(
76- from_statement.args[0].transform(is_subquery=True)
77- )
78- else:
79- from_str = transform(from_statement)
80-
81- if alias:
82- from_str = alias.transform(from_str)
83- statement_str += "\nFROM {}".format(from_str)
84-
85- if self.where_clause:
86- statement_str += "\nWHERE{}".format(transform(self.where_clause))
87-
88- if self.group_by_clause:
89- statement_str += "\nGROUP BY{}".format(transform(self.group_by_clause))
90-
91- if self.having_clause:
92- statement_str += "\nHAVING{}".format(transform(self.having_clause))
93-
94- if self.order_by_clause:
95- statement_str += "\nORDER BY{}".format(transform(self.order_by_clause))
96-
97- if self.limit_clause:
98- statement_str += "\nLIMIT {}".format(transform(self.limit_clause))
99-
100- if self.offset_clause:
101- statement_str += "\nOFFSET {}".format(transform(self.offset_clause))
102-
103- if is_subquery:
104- statement_str = " " + statement_str.replace("\n", "\n ")
105- elif self.semi_colon:
106- statement_str += ";"
107- return statement_str
108-
109- def validate(self):
110- errors = []
111- if isinstance(self.from_statement, Parenthesis) and isinstance(
112- self.from_statement.args[0], SelectStatement
113- ):
114- known_fields = self.from_statement.args[0].known_fields
115- elif isinstance(self.from_statement, Table):
116- known_fields = {"*"}
117- else:
118- known_fields = set()
119-
120- for e in self.expressions:
121- errors += e.validate(known_fields)
122- if self.where_clause:
123- errors += self.where_clause.validate(known_fields)
124- if self.group_by_clause:
125- errors += self.group_by_clause.validate(known_fields, self.expressions)
126- if self.having_clause:
127- errors += self.having_clause.validate(known_fields)
128- if self.order_by_clause:
129- errors += self.order_by_clause.validate(known_fields, self.expressions)
130- if self.limit_clause:
131- errors += self.limit_clause.validate(known_fields)
132- if self.offset_clause:
133- errors += self.offset_clause.validate(known_fields)
134- return errors
135-
136- @property
137- def known_fields(self):
138- fields = []
139- for e in self.expressions:
140- if isinstance(e, Column):
141- fields.append(e.value)
142- elif isinstance(e, Alias):
143- fields.append(e.alias)
144- return fields
145-
146- def __eq__(self, other):
147- return (
148- type(self) == type(other)
149- and self.select_all == other.select_all
150- and self.select_distinct == other.select_distinct
151- and (
152- (self.select_distinct_on is None and other.select_distinct_on is None)
153- or (
154- len(self.select_distinct_on) == len(other.select_distinct_on)
155- and all(
156- a == o
157- for a, o in zip(
158- self.select_distinct_on, other.select_distinct_on
159- )
160- )
161- )
162- )
163- and self.from_statement == other.from_statement
164- and len(self.expressions) == len(other.expressions)
165- and all(a == o for a, o in zip(self.expressions, other.expressions))
166- and self.where_clause == other.where_clause
167- and self.group_by_clause == other.group_by_clause
168- and self.having_clause == other.having_clause
169- and self.order_by_clause == other.order_by_clause
170- and self.limit_clause == other.limit_clause
171- and self.offset_clause == other.offset_clause
172- and self.semi_colon == other.semi_colon
173- )
174-
175- def __repr__(self):
176- return """<{}:
177- Expressions: {!r}
178- Select All: {!r} - Select Distinct: {!r} - Select Distinct On: {!r}
179- From: {!r}
180- Where: {!r}
181- Group By: {!r}
182- Having: {!r}
183- Order By: {!r}
184- Limit: {!r}
185- Offset: {!r}
186- Semi Colon: {!r}
187->
188- """.format(
189- self.__class__.__name__,
190- self.expressions,
191- self.select_all,
192- self.select_distinct,
193- self.select_distinct_on,
194- self.from_statement,
195- self.where_clause,
196- self.group_by_clause,
197- self.having_clause,
198- self.order_by_clause,
199- self.limit_clause,
200- self.offset_clause,
201- self.semi_colon,
202- )
203-
204-
205-class Expression:
206- def __init__(self, value):
207- self.value = value
208-
209- def __str__(self):
210- return str(self.value)
211-
212- def __repr__(self):
213- return "<{}: {!r}>".format(self.__class__.__name__, self.value)
214-
215- def __eq__(self, other):
216- return type(self) == type(other) and self.value == other.value
217-
218- def validate(self, known_fields):
219- return []
220-
221- @property
222- def return_type(self):
223- return Any
224-
225-
226-class WhereClause(Expression):
227- def transform(self):
228- transformed_value = transform(self.value)
229- if isinstance(self.value, Parenthesis) and "\n" in transformed_value:
230- return " (\n " + transform(self.value.args[0]).replace("\n", "\n ") + "\n)"
231- if "\n" in transformed_value:
232- return "\n " + transformed_value.replace("\n", "\n ")
233- return " " + transformed_value
234-
235- def validate(self, known_fields):
236- errors = super().validate(known_fields)
237- errors += self.value.validate(known_fields)
238- if self.value.return_type != bool:
239- errors.append(
240- "The argument of WHERE must be type boolean, not type {}".format(
241- self.value.return_type
242- )
243- )
244- return errors
245-
246-
247-class GroupByClause(Expression):
248- def __init__(self, *args, rollup=False):
249- self.args = args
250- self.rollup = rollup
251-
252- def __str__(self):
253- if len(self.args) > 1:
254- group_by_str = "\n{}".format(",\n".join(map(str, self.args))).replace(
255- "\n", "\n "
256- )
257- else:
258- group_by_str = " " + transform(self.args[0])
259- if self.rollup:
260- group_by_str = " ROLLUP{}".format(group_by_str)
261- return group_by_str
262-
263- def __repr__(self):
264- return "<GroupByClause: {} - rollup={}>".format(
265- ", ".join(map(repr, self.args)), self.rollup
266- )
267-
268- def __eq__(self, other):
269- return (
270- type(self) == type(other)
271- and len(self.args) == len(other.args)
272- and all(a == o for a, o in zip(self.args, other.args))
273- and self.rollup == other.rollup
274- )
275-
276- def validate(self, known_fields, select_expressions):
277- errors = super().validate(known_fields)
278- for arg in self.args:
279- while isinstance(arg, Parenthesis):
280- arg = arg.args[0]
281- if isinstance(arg, Integer) and (
282- arg.value <= 0 or arg.value > len(select_expressions)
283- ):
284- errors.append(
285- "GROUP BY position {} is not in select list".format(arg.value)
286- )
287- elif (
288- isinstance(arg, (Column, String))
289- and arg.value not in known_fields
290- and "*" not in known_fields
291- ):
292- errors.append('column "{}" does not exist'.format(arg.value))
293-
294- return errors
295-
296-
297-class HavingClause(Expression):
298- def __str__(self):
299- transformed_value = transform(self.value)
300- if isinstance(self.value, Parenthesis) and "\n" in transformed_value:
301- return " (\n " + transform(self.value.args[0]).replace("\n", "\n ") + "\n)"
302- if "\n" in transformed_value:
303- return "\n " + transformed_value.replace("\n", "\n ")
304- return " " + transformed_value
305-
306- def validate(self, known_fields):
307- errors = super().validate(known_fields)
308- errors += self.value.validate(known_fields)
309- if self.value.return_type != bool:
310- errors.append(
311- "The argument of WHERE must be type boolean, not type {}".format(
312- self.value.return_type
313- )
314- )
315- return errors
316-
317-
318-class OrderByClause(Expression):
319- def __init__(self, *args):
320- self.args = args
321-
322- def __str__(self):
323- if len(self.args) > 1:
324- order_by_str = "\n" + ",\n".join(map(str, self.args))
325- order_by_str = order_by_str.replace("\n", "\n ")
326- else:
327- order_by_str = " " + transform(self.args[0])
328- return order_by_str
329-
330- def __repr__(self):
331- return "<OrderByClause: {}>".format(
332- ", ".join(map(repr, self.args)),
333- )
334-
335- def __eq__(self, other):
336- return (
337- type(self) == type(other)
338- and len(self.args) == len(other.args)
339- and all(a == o for a, o in zip(self.args, other.args))
340- )
341-
342- def validate(self, known_fields, select_expressions):
343- errors = super().validate(known_fields)
344- for arg in self.args:
345- errors += arg.validate(known_fields, select_expressions)
346- return errors
347-
348-
349-class OrderByItem(Expression):
350- def __init__(self, expression, has_asc=False, has_desc=False):
351- super().__init__(expression)
352- self.has_asc = has_asc
353- self.has_desc = has_desc
354-
355- def __str__(self):
356- order_by_item_str = str(self.value)
357- if self.has_asc:
358- order_by_item_str += " ASC"
359- elif self.has_desc:
360- order_by_item_str += " DESC"
361- return order_by_item_str
362-
363- def __repr__(self):
364- return "<OrderByItem: {} has_asc={} has_desc={}>".format(
365- repr(self.value), self.has_asc, self.has_desc
366- )
367-
368- def __eq__(self, other):
369- return (
370- super().__eq__(other)
371- and self.has_asc == other.has_asc
372- and self.has_desc == other.has_desc
373- )
374-
375- def validate(self, known_fields, select_expressions):
376- errors = super().validate(known_fields)
377- value = self.value
378- while isinstance(value, Parenthesis):
379- value = value.value
380-
381- if isinstance(value, Integer):
382- if value.value <= 0 or value.value > len(select_expressions):
383- errors.append(
384- "ORDER BY position {} is not in select list".format(value.value)
385- )
386- else:
387- errors += self.value.validate(known_fields)
388- return errors
389-
390-
391-class LimitClause(Expression):
392- def __init__(self, limit_all, expression):
393- super().__init__(expression)
394- self.limit_all = limit_all
395-
396- def __str__(self):
397- if self.limit_all:
398- limit_str = "ALL"
399- else:
400- limit_str = str(self.value)
401- return limit_str
402-
403- def __repr__(self):
404- return "<LimitClause: {} limit_all={}>".format(repr(self.value), self.limit_all)
405-
406- def __eq__(self, other):
407- return super().__eq__(other) and self.limit_all == other.limit_all
408-
409- def validate(self, known_fields):
410- errors = super().validate(known_fields)
411- value = self.value
412- while isinstance(value, Parenthesis):
413- value = value.value
414- if self.value.return_type != int:
415- errors.append("argument of OFFSET must not contain variables")
416- else:
417- if isinstance(value, Integer) and value.value < 0:
418- errors.append("OFFSET must not be negative")
419- return errors
420-
421-
422-class OffsetClause(Expression):
423- def validate(self, known_fields):
424- errors = super().validate(known_fields)
425- value = self.value
426- while isinstance(value, Parenthesis):
427- value = value.value
428- if self.value.return_type != int:
429- errors.append("argument of LIMIT must be integer")
430- else:
431- if isinstance(value, Integer) and value.value < 0:
432- errors.append("LIMIT must not be negative")
433- return errors
434-
435-
436-class FunctionCall(Expression):
437- def __init__(self, function_name, *args):
438- self.function_name = function_name
439- self.args = args
440-
441- def __str__(self):
442- transformed_args = [transform(arg) for arg in self.args]
443- with_newlines = (
444- any("\n" in arg for arg in transformed_args)
445- or len(", ".join(transformed_args)) > DEFAULT_LINE_LENGTH
446- )
447-
448- function_str = self.function_name.upper() + "("
449- if with_newlines:
450- function_str += "\n "
451- function_str += (
452- ",\n ".join(arg.replace("\n", "\n ") for arg in transformed_args)
453- + "\n)"
454- )
455- else:
456- function_str += ", ".join(transformed_args) + ")"
457- return function_str
458-
459- def __repr__(self):
460- return "<FunctionCall {} - {}>".format(
461- self.function_name, ", ".join(map(repr, self.args))
462- )
463-
464- def __eq__(self, other):
465- return (
466- type(self) == type(other)
467- and self.function_name == other.function_name
468- and len(self.args) == len(other.args)
469- and all(a == o for a, o in zip(self.args, other.args))
470- )
471-
1500 further changed lines not shown

The check that tells the two apart

fail→pass·tests/integration/test_formatting.py::test_union_all_nested_query

Check file tests/integration/test_formatting.py, taken without changes from the fix and copied onto the older code, so the exact same check runs against both versions.

Origin and history

The fix0a343e0afdf388bf0eb9aeab4a4cb3d3c18a8b0f
The code before it0e0640b1736e9425f703f6688f7947764efe64a8
Broken version dated2020-12-31
Modulesqlvalidator.grammar.sql
Units changedCombinedQueries, SelectStatement
Fingerprint60b3dc81fb3a310b
Checked2026-08-18 by goldset/0.1

Every field above is generated by our program. None of it is written by hand.

Other bugs found in David-Wobrock/sqlvalidator