L026 and BigQuery record fields

[hx-markterry]

Expected Behaviour

The linter only recognises tables from the before the first. in the column name when using the bigquery dialect.

Observed Behaviour

The linter sees names in nested fields which are separated by dots as table names which are not referenced in the query. Maybe it is assuming that the first name in the column string is a bigquery project?

== [/example.sql] FAIL
L:   5 | P:   9 | L026 | Reference 'et2.transaction.amount' refers to table/view
                       | 'transaction' not found in the FROM clause or found in
                       | parent subquery.

Steps to Reproduce

SQL:

CREATE TABLE `example_dataset1.example_table1`
AS
SELECT
    et2._partitiontime AS partitiontime,
    et2.transaction.amount AS amount
FROM `example_dataset2.example_table2` AS et2

example_table2 has the following structure:

{
  name: 'transaction',
  type: 'RECORD',
  mode: 'REQUIRED',
  fields: [
    {
      name: 'amount',
      type: 'INTEGER',
      mode: 'REQUIRED'
    }
  ]
}

(date partitioned)

Version

% python --version 
Python 3.8.8

% sqlfluff --version
sqlfluff, version 0.5.3

Configuration

[sqlfluff]
verbose = 0
nocolor = False
dialect = bigquery
templater = jinja
rules = None

exclude_rules = L031
# L031 - Aliases in joins 

recurse = 0
output_line_length = 80
runaway_limit = 10
ignore_templated_areas = True
# Comma separated list of file extensions to lint.

# NB: This config will only apply in the root folder.
sql_file_exts = .sql,.sql.j2,.dml,.ddl

[sqlfluff:indentation]
indented_joins = False
template_blocks_indent = True

[sqlfluff:templater]
unwrap_wrapped_queries = True

[sqlfluff:templater:jinja:context]
ds=2020-01-01

# Some rules can be configured directly from the config common to other rules.
[sqlfluff:rules]
tab_space_size = 4
max_line_length = 120
indent_unit = space
comma_style = trailing
allow_scalar = True
single_table_references = consistent
unquoted_identifiers_policy = all

# Some rules have their own specific config.
[sqlfluff:rules:L003]
lint_templated_tokens = True

[sqlfluff:rules:L010]  # Keywords
capitalisation_policy = consistent

[sqlfluff:rules:L014]  # Unquoted identifiers
extended_capitalisation_policy = consistent

[sqlfluff:rules:L016]
ignore_comment_lines = False

[sqlfluff:rules:L029]  # Keyword identifiers
unquoted_identifiers_policy = aliases

[sqlfluff:rules:L030]  # Function names
capitalisation_policy = consistent

[sqlfluff:rules:L038]
select_clause_trailing_comma = forbid

[sqlfluff:rules:L040]  # Null & Boolean Literals
capitalisation_policy = consistent

[sqlfluff:rules:L042]
# By default, allow subqueries in from clauses, but not join clauses.
forbid_subquery_in = join

[sqlfluff:rules:L047]  # Consistent syntax to count all rows
prefer_count_1 = False

[amardeep]

I am still seeing this issue in sqlfluff, version 0.6.6

[barrywhart]

Can you please create a new issue with details on how to reproduce the problem?

[amardeep]

Created #1503