The logical processing order specifies a way how the results should be achieved.

However, the query processor can choose any ordering it wants, as long as the end result is the same as the one of the logical processing order.

In some cases the order is different and SQL Server does something that leads to an error, which shouldn't be there if the logical order was followed. In this case it will execute the LEFT and SUBSTRING functions first, before the JOINS and the WHERE clauses, instead of at the end in the SELECT.

I had a similar issue like you once, and I believe I added an index to force some other order. Or you can rewrite your query a bit.