Icon View Incident Report

Serious Serious
Reported By: Tomasz Mierzwinski
Reported On: 12/7/2004
For: Version 4.15 Build 1
# 1910 OR Conditions Combined with Column Comparisons in WHERE Clause Can Cause Incorrect Live Results

This SQL statement work in 4.05 but in 4.15 return ALL records in table ANNIVERSARIES although not every row in table meet conditions.

SELECT * 
  FROM ANNIVERSARIES A
  WHERE (A.ALARM=TRUE AND A.ALARM_YEAR<:CURR_YEAR)
    AND
    (
      (
      EXTRACT(MONTH FROM A.ANNIVERS_DATE)=:CURR_MONTH
      AND
        (
        (EXTRACT(MONTH FROM A.ANNIVERS_DATE)=A.ALARM_MONTH AND
        ALARM_DAY<=:CURR_DAY AND
        EXTRACT(DAY FROM A.ANNIVERS_DATE)>=:CURR_DAY)
        OR
        (EXTRACT(MONTH FROM A.ANNIVERS_DATE)<>A.ALARM_MONTH AND
        EXTRACT(DAY FROM A.ANNIVERS_DATE)>=:CURR_DAY)
        )
      )
      OR
      (
      ALARM_MONTH=:CURR_MONTH AND
      EXTRACT(MONTH FROM A.ANNIVERS_DATE)<>:CURR_MONTH AND
      ALARM_DAY<=:CURR_DAY
      )
    )
  ORDER BY ANNIVERS_ID



Comments Comments and Workarounds
The problem was caused when an OR operator was joining together two binary expressions (=, <, >, etc.) with columns on both sides of the binary operator. Also, this was only an issue when the TDBISAMQuery RequestLive property was set to True. The workaround is to use a canned result set instead (RequestLive=False).


Resolution Resolution
Fixed Problem on 12/9/2004 in version 4.16 build 1
Image