0

My query uses 'exists' function as filter and it has scalar valued function within it. The scalar valued funciton contains cte and "(select top 1 1)". When I use exists it does not filter at all. Rather when I use "where 1=(svf)" it seems work.Did I miss anything or anything wrong in exists query? 


SELECT * FROM TBL1
WHERE EXISTS (SELECT SVF(1,2))
--where SVF is my scalar valued from which returns bit and looks like as shown below.


CREATE FUNCTION SVF
(
    @x int,
    @y int
)
RETURNS bit
AS
BEGIN
declare @result bit
;WITH T1
    AS (
        SELECT * from tbl2
    )
SELECT  @result= (select top 1 1 
        FROM t1
        )
        return isnull(@result,0)
END
GO



--the following query works
SELECT * FROM TBL1
WHERE 1=(SELECT SVF(1,2))
xyz
  • 762
  • 7
  • 24
  • What is the point of this scalar function? It seems to be way overly complicated. Why not drop this scalar function and simply use "where exists (select * from tbl2)"? Scalar functions are horribly inefficient and in this case seems wholly unnecessary. – Sean Lange May 11 '16 at 13:42
  • 1
    Also, read up on [`EXISTS`](https://msdn.microsoft.com/en-GB/library/ms188336.aspx): "Returns TRUE if a subquery contains any rows." and note that it doesn't say anything about the *contents* of the rows. You have a subquery that (because it lacks a `FROM` clause) always generates exactly one row. It's always going to trivially satisfy the `EXISTS` test. – Damien_The_Unbeliever May 11 '16 at 13:43
  • Looking at this a little close the whole function is completely pointless. It will ALWAYS return a value so the exists check is always going to succeed. – Sean Lange May 11 '16 at 13:43
  • Cannot drop scalar funcation. It has a lot of scirpts inside it.This is just the sample of the script not the real one. @SeanLange – xyz May 11 '16 at 13:46
  • @Damien_The_Unbeliever Can you elaborate this more? Why cannot the sfv return null or zero? – xyz May 11 '16 at 13:49
  • 1
    @bill - a query consisting of a `SELECT` but no `FROM` clause always produces *exactly* one row. `EXISTS` checks whether a particular subquery produces any rows. It doesn't check *what* is in the row, so it doesn't matter *what* `svf` returns (which could, indeed, by 1, 0, NULL, *anything*) – Damien_The_Unbeliever May 11 '16 at 13:52
  • I am scared by the comment "Cannot drop scalar funcation. It has a lot of scirpts inside it.". That makes me nervous for your system. Scalar function are so slow and putting lots of logic in them compounds the performance problems. – Sean Lange May 11 '16 at 14:44
  • @SeanLange I agree with you. But what can be the better alternate? – xyz May 11 '16 at 14:48
  • I couldn't possibly answer that as I have no clue what your current scalar function does. For me, a scalar function that has lots of logic is a red flag of some other serious design challenges that will impede fast performance. – Sean Lange May 11 '16 at 14:51

2 Answers2

2

The EXISTS() operator

Returns TRUE if a subquery contains any rows.

(emphasis added)

That's important because your Scalar function is going to return a value, either 1 or 0 (as Damien pointed out even NULL will satisfy) but IT IS going to return a value.

And because it returns a value, the EXISTS is always going to return TRUE.

You are basically asking SQL Server:

-- Doesn't matter what my function returns as it's always going to return a row...
BEGIN 
-- then run this query
SELECT * FROM TBL1;
END;

Try to re-write your query to not use the function as a Scalar function is not normally Set Based and so is normally a performance killer.

Shaneis
  • 1,065
  • 1
  • 11
  • 20
  • 1
    It doesn't even have to return a value - it could return `NULL` and the exists test would *still* succeed since all it cares about is the existence of a *row*. – Damien_The_Unbeliever May 11 '16 at 13:48
  • So, if I stick to svf then I have to check 1 like in my last query, right? better change svf ? But changing svf seems not feasible for me as it contains so much of tables and logics. – xyz May 11 '16 at 14:00
  • 1
    I'm not sure I agree with your "basically asking" re-write, since it still gives the mistaken impression that the values returned from `SVF` are relevant. It's closer to asking SQL Server - "will `SELECT COUNT(*) FROM (SELECT `. – Damien_The_Unbeliever May 11 '16 at 14:01
  • @Damien_The_Unbeliever once again I bow to your logic as a quick read would give that impression. Edited there – Shaneis May 11 '16 at 14:04
  • @Shaneis You forgot to count (select count(*)) in your script. – xyz May 11 '16 at 14:09
  • @bill eh, where would I count(*) in this script? – Shaneis May 11 '16 at 14:11
  • "select * from tbl1" may yield no row as well. – xyz May 11 '16 at 14:13
  • @bill that's intended to show you that as your code is written now, all you are doing is `SELECT * FROM tbl1`, your exists will always be satisfied. It's like writing `IF 1 = 1`, it's always true. Apologies if that came across as replacement code. – Shaneis May 11 '16 at 14:16
  • @Shaneis what you are saying is correct, but keep in mind that his function is just an example. His real UDF can return 0 or 1, depending on the parameters. His question was why exists does not work. Your answer that "IT IS going to return a value" (allways) is correct and states why bills assumption was wrong. – Raul May 11 '16 at 14:25
1

As it already been stated, the exists function will always evaluate as true, since your scalar function returns a value. Exists would only work when the function call could eventually return no value.

You could use cross apply for example:

SELECT * 
FROM TBL1 as T1
CROSS APPLY (SELECT svf = dbo.SVF(T1.x,T2.y)) c
WHERE svf = 1

EDIT: Keep in mind that scalar functions are most of the times performance killers, as those are not sargable.

Raul
  • 2,745
  • 1
  • 23
  • 39
  • how is it different from my last script? – xyz May 11 '16 at 14:12
  • if I use SELECT * FROM TBL1 WHERE 1=(SELECT SVF(1,2)) – xyz May 11 '16 at 14:15
  • @bill there is basically no difference to your script. You can check the execution plans for each query, but both approaches are bad. To improve the performance, you would need to forget about the UDF. – Raul May 11 '16 at 14:20
  • @Shaneis NULL does satisfy an exists condition when there is a match of tuples but the value is NULL. As I stated that a "function call could eventually return no value" would not satisfy exists. I removed the (null) in my post, to not avoid confusion. – Raul May 11 '16 at 14:21