strange optimization of query results in phantom value conversions
As expected.

SQL is declarative and there is no implicit ordering of execution. You will see that the CAST is happening before the filter, so you get the error. That is, you have assumed that only int values will get through the CAST. Y

You should assume that all values in the column will be converted to int. The value here is "hist" which has been parsed.

There are a few ways to ensure that only CASTable values will be presented to the CAST


Materialise the JOIN so it has to run before the CAST
Use CASE to manage the CAST (see below)
Use a table variable/temp table to cache results


Example. You can't rely on the CAST happening after the derived table.

SELECT
    tbl_name,
    CASE WHEN num NOT LIKE '[^0-9]' THEN CAST(num AS int) END
FROM
    (
    SELECT 
        a.tbl_name,
        CAST(REVERSE(LEFT(REVERSE(b.name),PATINDEX('%[_]%',REVERSE(b.name))-1)) AS num
    FROM 
        partition_tbl a
        INNER JOIN 
        sys.objects b ON
                 b.name COLLATE German_PhoneBook_BIN 
                        NOT LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[^0-9]')
    WHERE
        b.xtype='U'
    ) T