Where exists remove select component

[xPudda]

Why in the WhereExists method there is a line of code that remove the select component and apply always a 1 costant?

https://github.com/sqlkata/querybuilder/blob/be679a9e4db51c0aeec6e8dddd2d377eec76f321/QueryBuilder/Base.Where.cs#L541-L545

I'm using SqlServer and I need to write an exists that through an except check some data. In SqlServer it should be like this:

SELECT *
FROM   dbo.SALDI_DETTAGLIO AS sd
WHERE  NOT EXISTS ( SELECT sd2.STAB
                         , sd2.MAGA
                         , sd2.PROG
                    FROM   dbo.SALDI_DETTAGLIO AS sd2
                    WHERE  ( [sd].[STAB] = [sd2].[STAB]
                             AND [sd].[MAGA] = [sd2].[MAGA]
                             AND [sd].[RIDO] = [sd2].[PROG] )
                    EXCEPT
                    SELECT sd.STAB
                         , sd.MAGA
                         , sd.PROG );

Translated in SqlKata it should be:

new Query("dbo.SALDI_DETTAGLIO AS sd")
.WhereNotExists(
new Query("dbo.SALDI_DETTAGLIO AS sd2")
           .WhereColumns("sd.STAB", "=", "sd2.STAB")
           .WhereColumns("sd.MAGA", "=", "sd2.MAGA")
           .WhereColumns("sd.RIDO", "=", "sd2.PROG")
           .Select("sd2.STAB", "sd2.MAGA", "sd2.PROG")
           .CombineRaw("EXCEPT SELECT sd.STAB, sd.MAGA, sd.PROG"))

And the query is exactly the same as the sql. But actually, that line of code remove my select and apply 1:

SELECT *
FROM   dbo.SALDI_DETTAGLIO AS sd
WHERE  NOT EXISTS ( SELECT 1
                    FROM   dbo.SALDI_DETTAGLIO AS sd2
                    WHERE  ( [sd].[STAB] = [sd2].[STAB]
                             AND [sd].[MAGA] = [sd2].[MAGA]
                             AND [sd].[RIDO] = [sd2].[PROG] ));

As a workaround, i could use AddComponent method and pass the same new query as passed in the WhereNotExists and it works.

I think that line of code should be removed because it is wrong, let the outside decide what to select.

[ahmad-moussawi]

What is the advantage of passing a select in WHERE [NOT] EXISTS clause? AFAK, the EXISTS look for the number of rows and totally ignore the columns, unless I am missing something here.

so to answer your question, SqlKata remove the SELECT part to reduce the query size

[xPudda]

The exists run the subquery to test for the existence of rows as you said.

But, in the select you could do a lot of semantic operations like except, unions, other exists to check the existance, etc... to know if exists some records. In my example, we have a table that has a foreign key in itself. In some way, we would like to ignore the records that have some values without making a cte or more complicated ways.

In fact, through the addComponent, it works.

[ahmad-moussawi]

@xPudda now you can set the OmitSelectInsideExists = false in your compiler to prevent this behavior.

[xPudda]

Thank you so much!