def test__parser__grammar__base__bracket_sensitive_look_ahead_match(
bracket_seg_list, fresh_ansi_dialect
):
"""Test the _bracket_sensitive_look_ahead_match method of the BaseGrammar."""
bs = StringParser("bar", KeywordSegment)
fs = StringParser("foo", KeywordSegment)
# We need a dialect here to do bracket matching
with RootParseContext(dialect=fresh_ansi_dialect) as ctx:
# Basic version, we should find bar first
pre_section, match, matcher = BaseGrammar._bracket_sensitive_look_ahead_match(
bracket_seg_list, [fs, bs], ctx
)
assert pre_section == ()
assert matcher == bs
# NB the middle element is a match object
assert match.matched_segments == (
KeywordSegment("bar", bracket_seg_list[0].pos_marker),
)
# Look ahead for foo, we should find the one AFTER the brackets, not the
# on IN the brackets.
pre_section, match, matcher = BaseGrammar._bracket_sensitive_look_ahead_match(
bracket_seg_list, [fs], ctx
)
# NB: The bracket segments will have been mutated, so we can't directly compare
assert len(pre_section) == 8
assert matcher == fs
# We shouldn't match the whitespace with the keyword
assert match.matched_segments == (
KeywordSegment("foo", bracket_seg_list[8].pos_marker),
)
def test__parser__grammar_sequence(seg_list, caplog):
"""Test the Sequence grammar."""
bs = StringParser("bar", KeywordSegment)
fs = StringParser("foo", KeywordSegment)
g = Sequence(bs, fs)
# If running in the test environment, assert that Sequence recognises this
if getenv("SQLFLUFF_TESTENV", ""):
assert g.test_env
gc = Sequence(bs, fs, allow_gaps=False)
with RootParseContext(dialect=None) as ctx:
with caplog.at_level(logging.DEBUG, logger="sqlfluff.parser"):
# Should be able to match the list using the normal matcher
logging.info("#### TEST 1")
m = g.match(seg_list, parse_context=ctx)
assert m
assert len(m) == 3
assert m.matched_segments == (
KeywordSegment("bar", seg_list[0].pos_marker),
seg_list[1], # This will be the whitespace segment
KeywordSegment("foo", seg_list[2].pos_marker),
)
# Shouldn't with the allow_gaps matcher
logging.info("#### TEST 2")
assert not gc.match(seg_list, parse_context=ctx)
# Shouldn't match even on the normal one if we don't start at the beginning
logging.info("#### TEST 2")
assert not g.match(seg_list[1:], parse_context=ctx)
def _eval(self, segment, raw_stack, **kwargs):
"""Look for UNION keyword not immediately followed by DISTINCT or ALL.
Note that UNION DISTINCT is valid, rule only applies to bare UNION.
The function does this by looking for a segment of type set_operator
which has a UNION but no DISTINCT or ALL.
"""
if segment.is_type("set_operator"):
if "UNION" in segment.raw.upper() and not (
"ALL" in segment.raw.upper() or "DISTINCT" in segment.raw.upper()
):
return LintResult(
anchor=segment,
fixes=[
LintFix(
"edit",
segment.segments[0],
[
KeywordSegment("UNION"),
WhitespaceSegment(),
KeywordSegment("DISTINCT"),
],
)
],
)
return LintResult()
def _coalesce_fix_list(
context: RuleContext,
coalesce_arg_1: BaseSegment,
coalesce_arg_2: BaseSegment,
preceding_not: bool = False,
) -> List[LintFix]:
"""Generate list of fixes to convert CASE statement to COALESCE function."""
# Add coalesce and opening parenthesis.
edits = [
KeywordSegment("coalesce"),
SymbolSegment("(", type="start_bracket"),
coalesce_arg_1,
SymbolSegment(",", type="comma"),
WhitespaceSegment(),
coalesce_arg_2,
SymbolSegment(")", type="end_bracket"),
]
if preceding_not:
not_edits: List[BaseSegment] = [
KeywordSegment("not"),
WhitespaceSegment(),
]
edits = not_edits + edits
fixes = [LintFix.replace(
context.segment,
edits,
)]
return fixes
def _eval(self, context: RuleContext) -> LintResult:
"""Look for UNION keyword not immediately followed by DISTINCT or ALL.
Note that UNION DISTINCT is valid, rule only applies to bare UNION.
The function does this by looking for a segment of type set_operator
which has a UNION but no DISTINCT or ALL.
Note only some dialects have concept of UNION DISTINCT, so rule is only
applied to dialects that are known to support this syntax.
"""
if context.dialect.name not in [
"ansi",
"bigquery",
"hive",
"mysql",
"redshift",
]:
return LintResult()
if context.segment.is_type("set_operator"):
if "union" in context.segment.raw and not (
"ALL" in context.segment.raw.upper()
or "DISTINCT" in context.segment.raw.upper()):
return LintResult(
anchor=context.segment,
fixes=[
LintFix.replace(
context.segment.segments[0],
[
KeywordSegment("union"),
WhitespaceSegment(),
KeywordSegment("distinct"),
],
)
],
)
elif "UNION" in context.segment.raw.upper() and not (
"ALL" in context.segment.raw.upper()
or "DISTINCT" in context.segment.raw.upper()):
return LintResult(
anchor=context.segment,
fixes=[
LintFix.replace(
context.segment.segments[0],
[
KeywordSegment("UNION"),
WhitespaceSegment(),
KeywordSegment("DISTINCT"),
],
)
],
)
return LintResult()
def _create_base_is_null_sequence(
is_upper: bool,
operator_raw: str,
) -> CorrectionListType:
is_seg = KeywordSegment("IS" if is_upper else "is")
not_seg = KeywordSegment("NOT" if is_upper else "not")
if operator_raw == "=":
return [is_seg]
return [
is_seg,
WhitespaceSegment(),
not_seg,
]
def test__parser__grammar_oneof_take_first(seg_list):
"""Test that the OneOf grammar takes first match in case they are of same length."""
fooRegex = RegexParser(r"fo{2}", KeywordSegment)
foo = StringParser("foo", KeywordSegment)
# Both segments would match "foo"
# so we test that order matters
g1 = OneOf(fooRegex, foo)
g2 = OneOf(foo, fooRegex)
with RootParseContext(dialect=None) as ctx:
assert g1.match(seg_list[2:], parse_context=ctx).matched_segments == (
KeywordSegment("foo", seg_list[2].pos_marker),
)
assert g2.match(seg_list[2:], parse_context=ctx).matched_segments == (
KeywordSegment("foo", seg_list[2].pos_marker),
)
def _eval(self, segment, parent_stack, raw_stack, **kwargs):
"""Implicit aliasing of table/column not allowed. Use explicit `AS` clause.
We look for the alias segment, and then evaluate its parent and whether
it contains an AS keyword. This is the _eval function for both L011 and L012.
The use of `raw_stack` is just for working out how much whitespace to add.
"""
if segment.is_type("alias_expression"):
if parent_stack[-1].is_type(*self._target_elems):
if not any(e.name.lower() == "as" for e in segment.segments):
insert_buff = []
insert_str = ""
# Add initial whitespace if we need to...
if raw_stack[-1].name not in ["whitespace", "newline"]:
insert_buff.append(WhitespaceSegment())
insert_str += " "
# Add an AS (Uppercase for now, but could be corrected later)
insert_buff.append(KeywordSegment("AS"))
insert_str += "AS"
# Add a trailing whitespace if we need to
if segment.segments[0].name not in ["whitespace", "newline"]:
insert_buff.append(WhitespaceSegment())
insert_str += " "
return LintResult(
anchor=segment,
fixes=[LintFix("create", segment.segments[0], insert_buff)],
)
return None
def test__parser__grammar_anysetof(generate_test_segments):
"""Test the AnySetOf grammar."""
token_list = ["bar", " \t ", "foo", " \t ", "bar"]
seg_list = generate_test_segments(token_list)
bs = StringParser("bar", KeywordSegment)
fs = StringParser("foo", KeywordSegment)
g = AnySetOf(fs, bs)
with RootParseContext(dialect=None) as ctx:
# Check directly
assert g.match(seg_list, parse_context=ctx).matched_segments == (
KeywordSegment("bar", seg_list[0].pos_marker),
WhitespaceSegment(" \t ", seg_list[1].pos_marker),
KeywordSegment("foo", seg_list[2].pos_marker),
)
# Check with a bit of whitespace
assert not g.match(seg_list[1:], parse_context=ctx)
def make_result_tuple(result_slice, matcher_keywords, seg_list):
"""Make a comparison tuple for test matching."""
# No result slice means no match.
if not result_slice:
return ()
return tuple(
KeywordSegment(elem.raw, pos_marker=elem.pos_marker) if elem.raw in
matcher_keywords else elem for elem in seg_list[result_slice])
def _eval(self, segment, parent_stack, raw_stack, **kwargs):
"""Implicit aliasing of table/column not allowed. Use explicit `AS` clause.
We look for the alias segment, and then evaluate its parent and whether
it contains an AS keyword. This is the _eval function for both L011 and L012.
The use of `raw_stack` is just for working out how much whitespace to add.
"""
fixes = []
if segment.is_type("alias_expression"):
if parent_stack[-1].is_type(*self._target_elems):
if any(e.name.lower() == "as" for e in segment.segments):
if self.aliasing == "implicit":
if segment.segments[0].name.lower() == "as":
# Remove the AS as we're using implict aliasing
fixes.append(LintFix("delete",
segment.segments[0]))
anchor = raw_stack[-1]
# Remove whitespace before (if exists) or after (if not)
if (len(raw_stack) > 0
and raw_stack[-1].type == "whitespace"):
fixes.append(LintFix("delete", raw_stack[-1]))
elif (len(segment.segments) > 0 and
segment.segments[1].type == "whitespace"):
fixes.append(
LintFix("delete", segment.segments[1]))
return LintResult(anchor=anchor, fixes=fixes)
else:
insert_buff = []
# Add initial whitespace if we need to...
if raw_stack[-1].name not in ["whitespace", "newline"]:
insert_buff.append(WhitespaceSegment())
# Add an AS (Uppercase for now, but could be corrected later)
insert_buff.append(KeywordSegment("AS"))
# Add a trailing whitespace if we need to
if segment.segments[0].name not in [
"whitespace", "newline"
]:
insert_buff.append(WhitespaceSegment())
return LintResult(
anchor=segment,
fixes=[
LintFix("create", segment.segments[0], insert_buff)
],
)
return None
def test__parser__grammar_sequence_nested(seg_list, caplog):
"""Test the Sequence grammar when nested."""
bs = StringParser("bar", KeywordSegment)
fs = StringParser("foo", KeywordSegment)
bas = StringParser("baar", KeywordSegment)
g = Sequence(Sequence(bs, fs), bas)
with RootParseContext(dialect=None) as ctx:
with caplog.at_level(logging.DEBUG, logger="sqlfluff.parser"):
# Matching the start of the list shouldn't work
logging.info("#### TEST 1")
assert not g.match(seg_list[:2], parse_context=ctx)
# Matching the whole list should, and the result should be flat
logging.info("#### TEST 2")
assert g.match(seg_list, parse_context=ctx).matched_segments == (
KeywordSegment("bar", seg_list[0].pos_marker),
seg_list[1], # This will be the whitespace segment
KeywordSegment("foo", seg_list[2].pos_marker),
KeywordSegment("baar", seg_list[3].pos_marker)
# NB: No whitespace at the end, this shouldn't be consumed.
)
def test__parser__grammar_oneof_take_longest_match(seg_list):
"""Test that the OneOf grammar takes the longest match."""
fooRegex = RegexParser(r"fo{2}", KeywordSegment)
baar = StringParser("baar", KeywordSegment)
foo = StringParser("foo", KeywordSegment)
fooBaar = Sequence(
foo,
baar,
)
# Even if fooRegex comes first, fooBaar
# is a longer match and should be taken
g = OneOf(fooRegex, fooBaar)
with RootParseContext(dialect=None) as ctx:
assert fooRegex.match(seg_list[2:], parse_context=ctx).matched_segments == (
KeywordSegment("foo", seg_list[2].pos_marker),
)
assert g.match(seg_list[2:], parse_context=ctx).matched_segments == (
KeywordSegment("foo", seg_list[2].pos_marker),
KeywordSegment("baar", seg_list[3].pos_marker),
)
def test__parser__multistringparser__match(generate_test_segments):
"""Test the MultiStringParser matchable."""
parser = MultiStringParser(["foo", "bar"], KeywordSegment)
with RootParseContext(dialect=None) as ctx:
# Check directly
seg_list = generate_test_segments(["foo", "fo"])
# Matches when it should
assert parser.match(
seg_list[:1],
parse_context=ctx).matched_segments == (KeywordSegment(
"foo", seg_list[0].pos_marker), )
# Doesn't match when it shouldn't
assert parser.match(seg_list[1:],
parse_context=ctx).matched_segments == tuple()
def test__parser__grammar_oneof(seg_list, allow_gaps):
"""Test the OneOf grammar.
NB: Should behave the same regardless of code_only.
"""
bs = StringParser("bar", KeywordSegment)
fs = StringParser("foo", KeywordSegment)
g = OneOf(fs, bs, allow_gaps=allow_gaps)
with RootParseContext(dialect=None) as ctx:
# Check directly
assert g.match(seg_list, parse_context=ctx).matched_segments == (
KeywordSegment("bar", seg_list[0].pos_marker),
)
# Check with a bit of whitespace
assert not g.match(seg_list[1:], parse_context=ctx)
def _eval(self, segment, parent_stack, **kwargs):
"""Ambiguous ordering directions for columns in order by clause.
This rule checks if some ORDER BY columns explicitly specify ASC or
DESC and some don't.
"""
# We only trigger on orderby_clause
if segment.is_type("orderby_clause"):
lint_fixes = []
orderby_spec = self._get_orderby_info(segment)
order_types = {o.order for o in orderby_spec}
# If ALL columns or NO columns explicitly specify ASC/DESC, all is
# well.
if None not in order_types or order_types == {None}:
return None
# There's a mix of explicit and default sort order. Make everything
# explicit.
for col_info in orderby_spec:
if not col_info.order:
# Since ASC is default in SQL, add in ASC for fix
lint_fixes.append(
LintFix(
"create",
col_info.separator,
[WhitespaceSegment(),
KeywordSegment("ASC")],
))
return [
LintResult(
anchor=segment,
fixes=lint_fixes,
description=
("Ambiguous order by clause. Order by "
"clauses should specify order direction for ALL columns or NO columns."
),
)
]
return None
def _eval(self, context: RuleContext) -> Optional[LintResult]:
"""Unnecessary CASE statement."""
# Look for CASE expression.
if context.segment.segments[0].raw_upper == "CASE":
# Find all 'WHEN' clauses and the optional 'ELSE' clause.
children = FunctionalContext(context).segment.children()
when_clauses = children.select(sp.is_type("when_clause"))
else_clauses = children.select(sp.is_type("else_clause"))
# Can't fix if multiple WHEN clauses.
if len(when_clauses) > 1:
return None
# Find condition and then expressions.
condition_expression = when_clauses.children(
sp.is_type("expression"))[0]
then_expression = when_clauses.children(
sp.is_type("expression"))[1]
# Method 1: Check if THEN/ELSE expressions are both Boolean and can
# therefore be reduced.
if else_clauses:
else_expression = else_clauses.children(
sp.is_type("expression"))[0]
upper_bools = ["TRUE", "FALSE"]
if ((then_expression.raw_upper in upper_bools)
and (else_expression.raw_upper in upper_bools) and
(then_expression.raw_upper != else_expression.raw_upper)):
coalesce_arg_1: BaseSegment = condition_expression
coalesce_arg_2: BaseSegment = KeywordSegment("false")
preceding_not = then_expression.raw_upper == "FALSE"
fixes = self._coalesce_fix_list(
context,
coalesce_arg_1,
coalesce_arg_2,
preceding_not,
)
return LintResult(
anchor=condition_expression,
fixes=fixes,
description="Unnecessary CASE statement. "
"Use COALESCE function instead.",
)
# Method 2: Check if the condition expression is comparing a column
# reference to NULL and whether that column reference is also in either the
# THEN/ELSE expression. We can only apply this method when there is only
# one condition in the condition expression.
condition_expression_segments_raw = {
segment.raw_upper
for segment in condition_expression.segments
}
if {"IS", "NULL"}.issubset(condition_expression_segments_raw) and (
not condition_expression_segments_raw.intersection(
{"AND", "OR"})):
# Check if the comparison is to NULL or NOT NULL.
is_not_prefix = "NOT" in condition_expression_segments_raw
# Locate column reference in condition expression.
column_reference_segment = (
Segments(condition_expression).children(
sp.is_type("column_reference")).get())
# Return None if none found (this condition does not apply to functions)
if not column_reference_segment:
return None
if else_clauses:
else_expression = else_clauses.children(
sp.is_type("expression"))[0]
# Check if we can reduce the CASE expression to a single coalesce
# function.
if (not is_not_prefix
and column_reference_segment.raw_upper
== else_expression.raw_upper):
coalesce_arg_1 = else_expression
coalesce_arg_2 = then_expression
elif (is_not_prefix and column_reference_segment.raw_upper
== then_expression.raw_upper):
coalesce_arg_1 = then_expression
coalesce_arg_2 = else_expression
else:
return None
if coalesce_arg_2.raw_upper == "NULL":
# Can just specify the column on it's own
# rather than using a COALESCE function.
return LintResult(
anchor=condition_expression,
fixes=self._column_only_fix_list(
context,
column_reference_segment,
),
description="Unnecessary CASE statement. "
f"Just use column '{column_reference_segment.raw}'.",
)
return LintResult(
anchor=condition_expression,
fixes=self._coalesce_fix_list(
context,
coalesce_arg_1,
coalesce_arg_2,
),
description="Unnecessary CASE statement. "
"Use COALESCE function instead.",
)
elif column_reference_segment.raw_upper == then_expression.raw_upper:
# Can just specify the column on it's own
# rather than using a COALESCE function.
# In this case no ELSE statement is equivalent to ELSE NULL.
return LintResult(
anchor=condition_expression,
fixes=self._column_only_fix_list(
context,
column_reference_segment,
),
description="Unnecessary CASE statement. "
f"Just use column '{column_reference_segment.raw}'.",
)
return None
def _eval(self, segment, **kwargs):
"""Find rule violations and provide fixes.
0. Look for a case expression
1. Find the first expression and "then"
2. Determine if the "then" is followed by a boolean
3. If so, determine if the first then-bool is followed by an else-bool
4. If so, delete everything but the first expression
5a. If then-true-else-false
* return deletions
* wrap with coalesce
5b. If then-false-else-true
* return deletions
* add a not condition
* wrap with parenthesis and coalesce
"""
# Look for a case expression
if segment.is_type("case_expression") and segment.segments[0].name == "case":
# Find the first expression and "then"
idx = 0
while segment.segments[idx].name != "then":
if segment.segments[idx].is_type("expression"):
expression_idx = idx
idx += 1
# Determine if "then" is followed by a boolean
then_bool_type = None
while segment.segments[idx].name not in ["when", "else", "end"]:
if segment.segments[idx].raw_upper in ["TRUE", "FALSE"]:
then_bool_type = segment.segments[idx].raw_upper
idx += 1
if then_bool_type:
# Determine if the first then-bool is followed by an else-bool
while segment.segments[idx].name != "else":
# If the first then-bool is followed by a "WHEN" or "END", exit
if segment.segments[idx].name in ["when", "end"]:
return None
idx += 1 # pragma: no cover
# Determine if "else" is followed by a boolean
else_bool_type = None
while segment.segments[idx].name != "end":
if segment.segments[idx].raw_upper in ["TRUE", "FALSE"]:
else_bool_type = segment.segments[idx].raw_upper
idx += 1
# If then-bool-else-bool, return fixes
if (
then_bool_type is not None
and else_bool_type is not None
and then_bool_type != else_bool_type
):
# Generate list of segments to delete -- everything but the
# first expression.
delete_segments = []
for s in segment.segments:
if s != segment.segments[expression_idx]:
delete_segments.append(s)
# If then-false, add "not" and space
edits = []
if then_bool_type == "FALSE":
edits.extend(
[
KeywordSegment("not"),
WhitespaceSegment(),
]
)
# Add coalesce and parenthesis
edits.extend(
[
KeywordSegment("coalesce"),
SymbolSegment("(", name="start_bracket", type="start_bracket"),
]
)
edit_coalesce_target = segment.segments[0]
fixes = []
fixes.append(
LintFix(
"edit",
edit_coalesce_target,
edits,
)
)
# Add comma, bool, closing parenthesis
expression = segment.segments[expression_idx + 1]
closing_parenthesis = [
SymbolSegment(",", name="comma", type="comma"),
WhitespaceSegment(),
KeywordSegment("false"),
SymbolSegment(")", name="end_bracket", type="end_bracket"),
]
fixes.append(
LintFix(
"edit",
expression,
closing_parenthesis,
)
)
# Generate a "delete" action for each segment in
# delete_segments EXCEPT the one being edited to become a call
# to "coalesce(". Deleting and editing the same segment has
# unpredictable behavior.
fixes += [
LintFix("delete", s)
for s in delete_segments
if s is not edit_coalesce_target
]
return LintResult(
anchor=segment.segments[expression_idx],
fixes=fixes,
description="Case when returns booleans.",
)
def _eval(self, context: RuleContext) -> Optional[LintResult]:
"""Implicit aliasing of table/column not allowed. Use explicit `AS` clause.
We look for the alias segment, and then evaluate its parent and whether
it contains an AS keyword. This is the _eval function for both L011 and L012.
"""
# Config type hints
self.aliasing: str
fixes = []
raw_segment_pre = context.raw_stack[-1] if context.raw_stack else None
assert context.segment.is_type("alias_expression")
if self.matches_target_tuples(context.parent_stack[-1],
self._target_elems):
if any(e.name.lower() == "as" for e in context.segment.segments):
if self.aliasing == "implicit":
if context.segment.segments[0].name.lower() == "as":
# Remove the AS as we're using implict aliasing
fixes.append(
LintFix.delete(context.segment.segments[0]))
anchor = raw_segment_pre
# Remove whitespace before (if exists) or after (if not)
if (raw_segment_pre is not None
and raw_segment_pre.type == "whitespace"):
fixes.append(LintFix.delete(raw_segment_pre))
elif (len(context.segment.segments) > 0
and context.segment.segments[1].type
== "whitespace"):
fixes.append(
LintFix.delete(context.segment.segments[1]))
return LintResult(anchor=anchor, fixes=fixes)
elif self.aliasing != "implicit":
insert_buff: List[Union[WhitespaceSegment,
KeywordSegment]] = []
# Add initial whitespace if we need to...
assert raw_segment_pre
if not raw_segment_pre.is_type("whitespace", "newline"):
insert_buff.append(WhitespaceSegment())
# Add an AS (Uppercase for now, but could be corrected later)
insert_buff.append(KeywordSegment("AS"))
# Add a trailing whitespace if we need to
if not context.segment.segments[0].is_type(
"whitespace",
"newline",
):
insert_buff.append(WhitespaceSegment())
return LintResult(
anchor=context.segment,
fixes=[
LintFix.create_before(
context.segment.segments[0],
insert_buff,
)
],
)
return None