Question about error when incorrect field alias given in INNER JOIN

[ismayc]

Solution code is

SELECT cities.name AS city, countries.name AS country, region
FROM cities
INNER JOIN countries
ON cities.country_code = countries.code;

So we'd expect it to report that city2 was used instead of city for the field alias.

SCT code is

sel = check_node('SelectStmt')

from_clause = sel.check_field('from_clause')

city = test_column('city', match='exact', msg='Did you alias `cities.name` as `city`?')

country = test_column('country', match='exact', msg='Did you alias `countries.name` as `country`?')

table = from_clause.check_field('left').has_equal_ast('Are you joining to the `cities` table?')

join_type = from_clause.check_field('join_type').has_equal_ast('Are you using the specified type of join?')

join_table  = from_clause.check_field('right').has_equal_ast('countries', 'Did you join the `countries` table?')

cond = from_clause.check_field('cond').has_equal_ast(sql='cities.country_code = countries.code', start='expression', exact=False, msg='Is your join condition correct?')

Ex().test_correct(check_result(), [
  cond,
  join_type,
  join_table,
  table,
  city,
  country,
  test_has_columns(),
  test_ncols(),
  test_error()
])

On Teach: https://www.datacamp.com/teach/edit/repositories/1069/branches/master On GitHub: https://github.com/datacamp/courses-joining-data-in-postgresql/blob/master/chapter1.md On campus: https://campus.datacamp.com/courses/joining-data-in-postgresql/15083?ex=3 (Task 3/3)

cc @colinricardo @machow

[machow]

I think the has_equal_ast call below...

join_table  = from_clause.check_field('right').has_equal_ast('countries', 'Did you join the `countries` table?')

should be written as has_equal_ast('Did you join ... ' , 'countries', 'expression')

You can tell which SCT is failing by the code that is highlighted. Since the it's highlighting the right field of the join, it has to be an SCT that's checking that part of the code.

(looks like something screwed up sqlwhat docs, will fix tomorrow)