Data output error using specific scenario in SQL Join

Question

Going though something very strange while running the SQL Query.

External Table: dl.tableA has a varchar value "EmployeeSequenceNumber" in column EmployeeSequenceNumber , while doing the inner join with dim.tableB, the query is getting succeeded, with no cast error which is as expected when done inner join with tableB, however as soon as left join with dim.tableC is added to the query it is failing with error message "Conversion failed when converting the varchar value 'EmployeeSequenceNumber' to data type smallint."

Now the question is as per understanding, first tableA would do inner join with tableB and then the output of the same to be joined with tableC.

If that's the case output of join between tableA and tableB never gave error of cast but how an error comes when it is joined with tableC.

Looks quite straight forward, but seems something I am missing across.

Query:

select cast(dl.EmployeeSequenceNumber as smallint) as EmployeeSequenceNumber

from 

	dl.tableA dl

	inner join dim.tableB h on h.EmployeeID = dl.EmployeeID 

	left join dim.tableC pc on pc.CourseID = dl.CourseID 

		and pc.EmployeeID= dl.EmployeeID

Execution Plan:

Answer 1

The error is the column EmployeeSequenceNumber contains a value that can not be converted to smallint, maybe a non-numeric or too many digits.

Answer 2

First a meta-discussion on how to ask queries. I see these labels: Your execution plan includes Shuffle operators. SQL Server does not have this operator, but it is used in Azure Synapse Analytics. I think for the particular question, the answer is the same, no matter you are using Synapse or SQL Server, but I need to add that I don't work with Synapse myself.

Now over to your question. SQL is a declarative language. This means that you state what result you want. The database engine you are using - be that Synapse, Oracle, SQL Server or something else - then applies an optimizer to find out which is the best way to compute the result. The join order you have written is just a logical way to express what you are looking for.

When it comes to a convert in the SELECT list, the optimizer can decide to do this before it performs the joins. This means that if there is data that does not convert, you could get an error - even if the bad row is logically filtered out by the JOIN and WHERE conditions.

The simplest way to avoid these accidents is to change cast to try_cast. try_cast returns NULL when the conversion fails rather than producing an error.