Force inner query to execute before outer query

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?

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]

You can try to save result of inner query in temporary/variable table and use it in main query.

Something like:

CREATE TABLE #vars (itemid, fileid)

-- store values for inner query
INSERT INTO 
    #vars (itemid, fileid)
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] <> ''


SELECT 
    distinct SelectedItems.itemid,SelectedItems.fileid
FROM 
    #vars as SelectedItems
    JOIN containstable(items, *, '<some query>') as items ON SelectedItems.fileid = items.[KEY]    

This should solve your problem.

I think you could also add the condition IsNumeric([value]) = 1 to the first join:

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
                          AND IsNumeric([value]) = 1 
        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]