question about checking joins

[colinricardo]

Suppose we want to check the following code:

SELECT c.code
FROM countries c
INNER JOIN economies e
ON c.code = e.code;

Question 1: is the following a reasonable way to do so?

sel = check_node('SelectStmt')
from_clause = sel.check_field('from_clause')
table = from_clause.check_field('left').has_equal_ast('Are you joining to the `countries` table?')
table_alias = from_clause.check_field('left', 1).has_equal_ast('Are you aliasing the `countries` table as `c`?')
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('Are you joining the `economies` table correctly?')
join_table_alias = from_clause.check_field('right', 1).has_equal_ast('Did you alias the `economies` table as `e`?')
cond = from_clause.check_field('cond').has_equal_ast(sql='c.code = e.code', start='expression', exact=False, msg='Is your join condition correct?')

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

Changing ON c.code = e.code; to e.g. ON c.code != e.code throws an appropriate error about the join condition being wrong.

However ON c.name = e.code causes a session crash with: list index out of range.

Question 2: What is the cause of this error and how can it be avoided?

cc @machow @ismayc

[machow]

Can you cut the example down to the smallest(ish) amount of code to produce the error?

[colinricardo]

I think a minimal reproducible example would be:

SELECT c.code
FROM countries c
JOIN economies e
ON c.code = e.code;

with SCT:

sel = check_node('SelectStmt')
from_clause = sel.check_field('from_clause')
cond = from_clause.check_field('cond').has_equal_ast(sql='c.code = e.code', start='expression', exact=False, msg='Is your join condition correct?')

Ex().test_correct(check_result(), [
  cond,
  test_has_columns(),
  test_ncols(),
  test_error()
])

[machow]

Hmm.. I'm not sure what's going on. I tried to replicate but couldn't. It's the last exercise below

https://www.datacamp.com/teach/repositories/905/branches/master

Can you point me to the exercise? I can take a look at it.

[colinricardo]

https://campus.datacamp.com/courses/joining-data-in-postgresql/15083?ex=4

It seems that doing c.whatever works as expected, but using any real column from the countries table seems to break.

[machow]

Ah, okay. Likely this is a duplicate of https://github.com/datacamp/sqlbackend/issues/82.

The error happens on the backend when there are no rows returned.

[machow]

should be fixed on prod, see details on https://github.com/datacamp/sqlbackend/issues/82