Force inner query to execute before outer query

Question

I have two tables that I need to join on:

Table VarValues has field Value that is a varchar and contains custom properties that can be text og int by the property type

Table Vars has the property type.

Table items has items in it, specifically an id and data that is fulltext indexed and I need to search.

When I query like this:

SELECT 
    distinct SelectedItems.itemid,SelectedItems.fileid
FROM 
    (
        SELECT  [values].itemid, convert(int, [value]) as fileid
        FROM 
            VarValues [values]
            JOIN Vars vars ON [values].VarID = vars. ID
        WHERE
            [type] = 6 --This type is an int
            AND
            [values].[value] <> ''
    ) as SelectedItems
    JOIN containstable(items, *, '<some query>') as items ON SelectedItems.fileid = items.[KEY]

The inner query only returns integers as the fileid but when the whole query is run I get an error because the first row in VarValues contains text although the type is not 6.

Is the query optimizer messing with my inner query? How can I make it stop? Or am I doing this completely wrong?

Answer 1

The optimizer can expand the views (including inline ones) and is free to decide the filter order.

In other words, it may first perform the join with the fulltext table and later filter the results on type = 6 (which it seems to be doing in your case).

You may try adding OPTION (FORCE ORDER) to your query, however, it only guarantees the order of the tables in the join operations, not the order the filters are applied.

Try rewriting your query like this:

SELECT 
    distinct SelectedItems.itemid,SelectedItems.fileid
FROM 
    (
        SELECT  [values].itemid, CAST(CASE WHEN IsNumeric([value]) = 1 THEN [value] END AS INT) AS field
        FROM 
            VarValues [values]
            JOIN Vars vars ON [values].VarID = vars. ID
        WHERE
            [type] = 6 --This type is an int
            AND
            [values].[value] <> ''
    ) as SelectedItems
    JOIN containstable(items, *, '<some query>') as items ON SelectedItems.fileid = items.[KEY]