SQL Filter behaving differently than expected

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Erik van Putten
Posts: 20
Joined: Thu 11 Nov 2004 08:24
Location: Moerkapelle

SQL Filter behaving differently than expected

Post by Erik van Putten » Thu 16 Oct 2014 10:46

Hi all,

Just to let you know I came across this problem.

I have a query SELECT * FROM GOODS where PRODUCT = 'APPLE'
This returns a number of records.
I've set my FILTER SQL TO COLOR = 'GREEN' OR COLOR = 'RED'

This returns all Green Apples and ALL Red Fruits.
I expected it to return only Green and Red Apples.

To solve this you have to set FILTERSQL TO (COLOR = 'GREEN' OR COLOR = 'RED')

I was under the impression that a query returns a set of given records.
FilterSQL would act as a filter, and not possibly add records to that set query.

Devart explained that the way it works it that FILTERSQL is added as 'AND ' + FILTERSQL.
So setting the Filter SQL to COLOR = 'GREEN' OR COLOR = 'RED' result in the query becoming : SELECT * FROM GOODS WHERE (PRODUCT = 'APPLE' ) AND COLOR = 'GREEN' OR COLOR = 'RED'


Devart doesn't want to change this behavior as they believe everybody is expecting this behavior.

I'm now changing all my FilterSQL to include brackets before and after the condition.

Please be warned about this, not only for MSSQL but for all the products.

Am I the only one disagreeing about this with Devart ?

Kind regards,
Erik.

GNiessen
Posts: 28
Joined: Wed 18 Jan 2012 20:15

Re: SQL Filter behaving differently than expected

Post by GNiessen » Thu 16 Oct 2014 12:18

What happens if you set your Filter to "(COLOR = 'GREEN' OR COLOR = 'RED')"?

Erik van Putten
Posts: 20
Joined: Thu 11 Nov 2004 08:24
Location: Moerkapelle

Re: SQL Filter behaving differently than expected

Post by Erik van Putten » Thu 16 Oct 2014 12:48

Hi GNiessen, as I wrote putting the filter between brackets solves the problem.
(You only get Red and Green Apples returned)

My point was that I did not expect the need to do that.
The problem arises as soon as you use AND or OR in your FilterSQL.

So it's easy to forget you need to enclose it in brackets.

ccmcbride
Posts: 101
Joined: Tue 01 May 2007 16:36

Re: SQL Filter behaving differently than expected

Post by ccmcbride » Mon 05 Jan 2015 18:13

Personally, I agree.
if it's appended as an 'and', then the 'or' behavior should not be introduced.

Post Reply