goldset

Whole file

David-Wobrock/sqlvalidator

The author described this change as “Fix handling Any return type as invalid WHERE/HAVING condition”. It counts as a record because the checks below fail on the code as it stood at 00eab4924 and pass on 3e7b2f87b, with nothing else changed between the two runs.

ProjectDavid-Wobrock/sqlvalidator
Fix saved2021-11-15
Sharing licenceMIT · LICENSE
Change size+1204 −1204

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

Fix handling Any return type as invalid WHERE/HAVING condition

The change

252252 def validate(self, known_fields: Set[str]) -> list:
253253 errors = super().validate(known_fields)
254254 errors += self.value.validate(known_fields)
255- if self.value.return_type != bool:
256- errors.append(
257- "The argument of WHERE must be type boolean, not type {}".format(
258- self.value.return_type
259- )
260- )
261- return errors
262-
263- def __eq__(self, other):
264- return type(self) == type(other) and self.value == other.value
265-
266-
267-class GroupByClause(Expression):
268- def __init__(self, *args, rollup=False):
269- self.args = args
270- self.rollup = rollup
271- self.group_each_by = False
272-
273- def __str__(self):
274- if len(self.args) > 1:
275- group_by_str = "\n{}".format(",\n".join(map(str, self.args))).replace(
276- "\n", "\n "
277- )
278- else:
279- group_by_str = " " + transform(self.args[0])
280- if self.rollup:
281- group_by_str = " ROLLUP{}".format(group_by_str)
282- return group_by_str
283-
284- def __repr__(self):
285- return "<GroupByClause: {} - rollup={}>".format(
286- ", ".join(map(repr, self.args)), self.rollup
287- )
288-
289- def __eq__(self, other):
290- return (
291- type(self) == type(other)
292- and len(self.args) == len(other.args)
293- and all(a == o for a, o in zip(self.args, other.args))
294- and self.rollup == other.rollup
295- and self.group_each_by == other.group_each_by
296- )
297-
298- def validate(self, known_fields, select_expressions):
299- errors = super().validate(known_fields)
300- for arg in self.args:
301- while isinstance(arg, Parenthesis):
302- arg = arg.args[0]
303- if isinstance(arg, Integer) and (
304- arg.value <= 0 or arg.value > len(select_expressions)
305- ):
306- errors.append(
307- "GROUP BY position {} is not in select list".format(arg.value)
308- )
309- elif (
310- isinstance(arg, (Column, String))
311- and (
312- arg.value not in known_fields
313- and arg.value
314- not in [e.alias for e in select_expressions if isinstance(e, Alias)]
315- )
316- and "*" not in known_fields
317- ):
318- errors.append('column "{}" does not exist'.format(arg.value))
319-
320- return errors
321-
322-
323-class HavingClause(Expression):
324- def __str__(self):
325- transformed_value = transform(self.value)
326- if isinstance(self.value, Parenthesis) and "\n" in transformed_value:
327- return " (\n " + transform(self.value.args[0]).replace("\n", "\n ") + "\n)"
328- if "\n" in transformed_value:
329- return "\n " + transformed_value.replace("\n", "\n ")
330- return " " + transformed_value
331-
332- def validate(self, known_fields):
333- errors = super().validate(known_fields)
334- errors += self.value.validate(known_fields)
335- if self.value.return_type != bool:
336- errors.append(
337- "The argument of WHERE must be type boolean, not type {}".format(
338- self.value.return_type
339- )
340- )
341- return errors
342-
343-
344-class OrderByClause(Expression):
345- def __init__(self, *args):
346- self.args = args
347-
348- def transform(self, allow_linebreak=True):
349- if len(self.args) > 1:
350- if allow_linebreak:
351- order_by_str = "\n" + ",\n".join(map(str, self.args))
352- order_by_str = order_by_str.replace("\n", "\n ")
353- else:
354- order_by_str = " " + ", ".join(map(str, self.args))
355- else:
356- order_by_str = " " + transform(self.args[0])
357- return order_by_str
358-
359- def __repr__(self):
360- return "<OrderByClause: {}>".format(
361- ", ".join(map(repr, self.args)),
362- )
363-
364- def __eq__(self, other):
365- return (
366- type(self) == type(other)
367- and len(self.args) == len(other.args)
368- and all(a == o for a, o in zip(self.args, other.args))
369- )
370-
371- def validate(self, known_fields, select_expressions):
372- errors = super().validate(known_fields)
373- for arg in self.args:
374- errors += arg.validate(known_fields, select_expressions)
375- return errors
376-
377-
378-class OrderByItem(Expression):
379- def __init__(self, expression, has_asc=False, has_desc=False):
380- super().__init__(expression)
381- self.has_asc = has_asc
382- self.has_desc = has_desc
383-
384- def __str__(self):
385- order_by_item_str = str(self.value)
386- if self.has_asc:
387- order_by_item_str += " ASC"
388- elif self.has_desc:
389- order_by_item_str += " DESC"
390- return order_by_item_str
391-
392- def __repr__(self):
393- return "<OrderByItem: {} has_asc={} has_desc={}>".format(
394- repr(self.value), self.has_asc, self.has_desc
395- )
396-
397- def __eq__(self, other):
398- return (
399- super().__eq__(other)
400- and self.has_asc == other.has_asc
401- and self.has_desc == other.has_desc
402- )
403-
404- def validate(self, known_fields, select_expressions):
405- errors = super().validate(known_fields)
406- value = self.value
407- while isinstance(value, Parenthesis):
408- value = value.value
409-
410- if isinstance(value, Integer):
411- if value.value <= 0 or value.value > len(select_expressions):
412- errors.append(
413- "ORDER BY position {} is not in select list".format(value.value)
414- )
415- else:
416- errors += self.value.validate(known_fields)
417- return errors
418-
419-
420-class LimitClause(Expression):
421- def __init__(self, limit_all, expression):
422- super().__init__(expression)
423- self.limit_all = limit_all
424-
425- def __str__(self):
426- if self.limit_all:
427- limit_str = "ALL"
428- else:
429- limit_str = str(self.value)
430- return limit_str
431-
432- def __repr__(self):
433- return "<LimitClause: {} limit_all={}>".format(repr(self.value), self.limit_all)
434-
435- def __eq__(self, other):
436- return super().__eq__(other) and self.limit_all == other.limit_all
437-
438- def validate(self, known_fields):
439- errors = super().validate(known_fields)
440- value = self.value
441- while isinstance(value, Parenthesis):
442- value = value.value
443- if value.return_type != int or not isinstance(value, Integer):
444- errors.append("argument of LIMIT must not contain variables")
445- else:
446- if isinstance(value, Integer) and value.value < 0:
447- errors.append("LIMIT must not be negative")
448- return errors
449-
450-
451-class OffsetClause(Expression):
452- def validate(self, known_fields):
453- errors = super().validate(known_fields)
454- value = self.value
455- while isinstance(value, Parenthesis):
456- value = value.value
457- if value.return_type != int or not isinstance(value, Integer):
458- errors.append("argument of OFFSET must be integer")
459- else:
460- if isinstance(value, Integer) and value.value < 0:
461- errors.append("OFFSET must not be negative")
462- return errors
463-
464-
465-class WithQuery(Expression):
466- def __init__(self, name: str, statement: SelectStatement):
467- self.name = name
468- # todo: column name, for recursivity
469- self.statement = statement
470-
471- def __str__(self):
472- return "{} AS (\n{}\n)".format(
473- self.name, self.statement.transform(is_subquery=True)
474- )
475-
476- def __eq__(self, other):
477- return (
478- type(self) == type(other)
479- and self.name == other.name
480- and self.statement == other.statement
481- )
482-
483-
484-class WithStatement(Expression):
485- def __init__(self, with_queries: List[WithQuery], select_statement):
486- # todo: recursive
487- self.with_queries = with_queries
488- self.select_statement = select_statement
489-
490- def transform(self):
491- return "WITH {}\n{}".format(
492- ",\n".join(map(transform, self.with_queries)),
493- transform(self.select_statement),
494- )
495-
496- def __eq__(self, other):
497- return (
498- type(self) == type(other)
499- and len(self.with_queries) == len(other.with_queries)
500- and all(a == o for a, o in zip(self.with_queries, other.with_queries))
501- and self.select_statement == other.select_statement
502- )
503-
504-
505-class FunctionCall(Expression):
506- def __init__(self, function_name, *args):
507- self.function_name = function_name
508- self.args = args
509-
510- def __str__(self):
511- transformed_args = [transform(arg) for arg in self.args]
512- with_newlines = (
513- any("\n" in arg for arg in transformed_args)
514- or len(", ".join(transformed_args)) > DEFAULT_LINE_LENGTH
515- )
516-
517- function_str = self.function_name.upper() + "("
518- if with_newlines:
519- function_str += "\n "
520- function_str += (
521- ",\n ".join(arg.replace("\n", "\n ") for arg in transformed_args)
522- + "\n)"
523- )
524- else:
525- function_str += ", ".join(transformed_args) + ")"
526- return function_str
527-
528- def validate(self, known_fields):
529- errors = super().validate(known_fields)
530- for a in self.args:
531- errors += a.validate(known_fields)
532- return errors
533-
534- def __repr__(self):
535- return "<FunctionCall {} - {}>".format(
536- self.function_name, ", ".join(map(repr, self.args))
537- )
538-
539- def __eq__(self, other):
540- return (
541- type(self) == type(other)
542- and self.function_name == other.function_name
543- and len(self.args) == len(other.args)
544- and all(a == o for a, o in zip(self.args, other.args))
545- )
546-
547-
548-class CastFunctionCall(FunctionCall):
549- def __init__(self, column, cast_type):
550- super().__init__("cast", column, "AS", cast_type)
551-
552- def __str__(self):
553- return "{}({} AS {})".format(
554- self.function_name.upper(),
555- transform(self.args[0]),
556- transform(self.args[2]),
557- )
558-
559-
560-class CountFunctionCall(FunctionCall):
561- def __init__(self, *args, distinct=False):
562- super().__init__("count", *args)
563- self.distinct = distinct
564-
565- def __str__(self):
566- return "{}({}{})".format(
567- self.function_name.upper(),
568- "DISTINCT " if self.distinct else "",
569- ", ".join(map(transform, self.args)),
570- )
571-
572- def __eq__(self, other):
573- return super().__eq__(other) and self.distinct == other.distinct
574-
575-
576-class ArrayAggFunctionCall(FunctionCall):
577- def __init__(
578- self,
579- column,
580- distinct=False,
581- ignore_nulls=False,
582- respect_nulls=False,
583- order_bys=None,
584- limit=None,
585- ):
586- super().__init__("array_agg", column)
587- self.distinct = distinct
588- assert not (ignore_nulls and respect_nulls)
589- self.ignore_nulls = ignore_nulls
590- self.respect_nulls = respect_nulls
591- self.order_bys = order_bys
592- self.limit = limit
593-
594- def __str__(self):
595- array_agg_str = "{}(".format(self.function_name.upper())
596- if self.distinct:
597- array_agg_str += "DISTINCT "
598-
599- array_agg_str += transform(self.args[0])
600-
601- if self.ignore_nulls:
602- array_agg_str += " IGNORE NULLS"
603- elif self.respect_nulls:
604- array_agg_str += " RESPECT NULLS"
605-
606- if self.order_bys:
607- array_agg_str += " ORDER BY{}".format(
608- self.order_bys.transform(allow_linebreak=False)
609- )
610-
611- if self.limit:
612- array_agg_str += " LIMIT {}".format(self.limit)
613-
614- return array_agg_str + ")"
615-
616-
617-class FilteredFunctionCall(Expression):
618- def __init__(self, function_call: FunctionCall, filter_condition):
619- self.function_call = function_call
620- self.filter_condition = filter_condition
621-
622- def __str__(self):
623- return "{} FILTER (WHERE {})".format(
624- transform(self.function_call), transform(self.filter_condition)
625- )
626-
627- def __eq__(self, other):
628- return (
629- type(self) == type(other)
630- and self.function_call == other.function_call
631- and self.filter_condition == other.filter_condition
632- )
633-
634- def validate(self, known_fields):
635- errors = super().validate(known_fields)
636- errors += self.function_call.validate(known_fields)
637- errors += self.filter_condition.validate(known_fields)
638- return errors
639-
640-
641-class AnalyticsClause(Expression):
642- def __init__(self, function, partition_by, order_by, frame_clause):
643- self.function = function
644- self.partition_by = partition_by
645- self.order_by = order_by
646- self.frame_clause = frame_clause
647-
648- def __str__(self):
649- analytics_str = "{} OVER (".format(transform(self.function))
650- if self.partition_by:
651- analytics_str += "\n PARTITION BY"
2014 further changed lines not shown

The check that tells the two apart

fail→pass·tests/integration/test_validation.py::test_subquery_field_is_boolean_and_can_where
fail→pass·tests/integration/test_validation.py::test_unknown_type_subquery_field_and_allow_where

Check file tests/integration/test_validation.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 fix3e7b2f87b4f9984240b8e338803b15b2f2ddc3f1
The code before it00eab4924c698269ac162db216753350c766d2d2
Broken version dated2021-11-14
Modulesqlvalidator.grammar.sql
Units changedBooleanCondition, ChainedColumns, HavingClause, WhereClause
Fingerprintf65ca72b9ad58deb
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