Confusion about sqlite query planner-CodePudding

I have a view which is wrote in SQLite which is V_Order_Calculator3. And also 1.5 million records inside the orders table.

imagine the code below:

select * from (
    SELECT 
        order_id
    FROM V_Order_Calculator3 
    WHERE /* CHOICE1 order_id = '00002092-03b4-4661-a9f4-afa73984860a'*/
    GROUP BY
    (CASE 
        WHEN order_type IN ('webshop_sell','sell','buy') THEN order_id
        WHEN order_type IN ('sell_return','buy_return')  THEN order_linked_id
        ELSE 0
    END)
) a /* CHOICE2 where a.order_id = '00002092-03b4-4661-a9f4-afa73984860a'*/

If I uncomment CHOICE1 then the the query takes 15 milliseconds to run. and if I uncomment CHOICE2 then it will be 18000 milliseconds.

It seems the sqlite query planner not calculating the CHOICE2 where clause before using the view.

I got confused and tried many ways but no luck.

CodePudding user response：

You think that the time should be the same because you assume that the queries have the same meaning and should give the same results, so sqlite should optimize the second query by pushing the WHERE down in the inner query, but your assumption is wrong.

The first one (inside WHERE) will filter the table for a single order_id, then will create groups based on the values of order_type, order_id and order_linked_id that it finds on the filtered rows. For every group it will return the order_id of an unspecified row of the group. Since you filtered for a specific order_id, it will always be the same value for every group.

The second one (outside WHERE) will scan all the table, creating groups based on the values of order_type, order_id and order_linked_id that it finds on all rows. For every group it will return the order_id of an unspecified row of the group, which at this point could be any value. The outside WHERE will then filter the result based on THESE values.

This is an example data where your two queries give different results: https://www.db-fiddle.com/f/xqiFACuv8PzU3VBnLbEoWF/2

It doesn't matter that in your application this set of data would not be possible or meaningful. Since the queries are not semantically equivalent, sqlite cannot transform the second one into the first.