SQL Parentheses and "AND" bug?

[Edwin Rose]

When I was working on Valentina and developing cross platform (Access on
Windows, Valentina on Mac) I came up with some rules...

Here are the rules:

1) if fields are compared to constants, all fields from the same table must
be enclosed in parentheses.  Example:
 ( (STUDENT_ACTIVITY_STATUS.ID = 1) AND
(STUDENT_ACTIVITY_STATUS.ACTIVITY_CODE = 'SR21') )
2) everything except parenthesis must have spaces around it:
NOT: (STUDENT_ACTIVITY_STATUS.ID=1) BUT  (STUDENT_ACTIVITY_STATUS.ID = 1)
this sometimes isn't necessary and sometimes is,  to be safe just do it
everywhere.
3) when 2 tables have 2 or more links, all those links must be in
parentheses. Example:
 (  (STUDENT_ACTIVITY_QUESTION_STATUS.SAS_ID =
STUDENT_ACTIVITY_STATUS.SAS_ID) 
 AND (STUDENT_ACTIVITY_QUESTION_STATUS.ACTIVITY_CODE =
STUDENT_ACTIVITY_STATUS.ACTIVITY_CODE) ) 
4) it is best for speed's sake to have the compare to constant at the start
of the SQL and other statements in order of decreasing sifting of the data.
5) ALL fields in the ORDER BY must be in the SELECT fields.  This is not
true for Access, but is true for Valentina.  Solution: put the fields at the
end of the SELECT fields (so it won't impact the ordering).
6) No JOINS using SQL '92 syntax, no Access queries, everything in:
SELECT, FROM, WHERE, ORDER BY form

Of course, with the new SQL parser, this will ALL become obsolete (THANK
GOODNESS).

--Ed Rose
Leapster Emulator/Optimizer Guy
x5347


-----Original Message-----
From: Francois Van Lerberghe [mailto:fvanlerberghe at freegates.be]
Sent: Monday, July 07, 2003 11:10 PM
To: Valentina Developers
Subject: Re: SQL Parentheses and "AND" bug?


le 7-07-03 21:55, erobar at earthlink.net <erobar at earthlink.net> a écrit :

> Note: the ONLY difference is that I've added parentheses around the last 2
> criteria, which produces the right results.  With the first SQL statement,
it
> returns items where table_B.field_B is 1 through 5!  So, the third
criterion
> seems to be obliterated somehow....

I think it's a long standing problem.
And I think it's even better to group and enclose in parentheses all
conditions of the same table, not only the last
Thus I'd write this query as this :

select count(*) from table_A, table_B where
(table_A.field_A >= 3 and table_A.field_A <= 5) and
(table_B.field_B >= 3 and table_B.field_B <= 10)

Cheers

François Van Lerberghe
Thier Monty, 15A
B-4570 Marchin
Belgique

_______________________________________________
Valentina mailing list
Valentina at lists.macserve.net
http://lists.macserve.net/mailman/listinfo/valentina