Query optimization when multiplying by zero I'm on SQL Server and I have a query like this:

select * from MyTable where a * expensive_function() &gt; 0


I noticed the server always runs expensive_function(), even for rows where a = 0 (and also if I replace a with a literal 0).

Is there a clear reason for that?

Do other servers optimize queries like this?

Note: This query is trivial, and could be changed to avoid running the function. My question isn't about optimization of this query, but to know if/why/which servers would optimize (or not) queries like this.
Change your query to -

select  *

from    MyTable 

where   case 
            when    a &lt;&gt; 0
            then    case 
                        when    a * expensive_function() &gt; 0
                        then    1
                    end
        end = 1


As for the questions -
An optimizer does what optimizer does.
It is an application with features that are supported and features that are not supported as any other application.
No application is being created right away with all possible features and no development team has unlimited resources and motivation to develop any possible feature including those with insignificant business value. 



For @a_vlad

create function dbo.myfunction (@i int) returns int as begin return 0 end
GO

declare @x int = 0;
select 1 where dbo.myfunction(@x) = 1 and 1/@x = 1



  Msg 8134, Level 16, State 1, Line 6
  Divide by zero error encountered.


For @Myberg

create function dbo.myfunction (@i int) returns int as begin return 0 end
GO

create table #t (i int);
insert into #t (i) values (0);

select  1

from   (select  *
        from    #t
        where   dbo.myfunction(i) = 1
        ) t

where   1/i = 1



  Msg 8134, Level 16, State 1, Line 9
  Divide by zero error encountered.