Icon View Incident Report

Serious Serious
Reported By: hadi
Reported On: 2/25/2005
For: Version 4.17 Build 1
# 1968 IN Sub-Queries On String Columns Not Working Properly for Arabic Locales

Running this SQL query (Select) on the same table returns 3 rows on 4.08 But zero rows on 4.15, 4.16, and 4.17.

Query notes:
Im selecting students who passed the French/27 course (DptID = 11) And (SubjectID = '27') and (Grade in (A,A-,B+,B,B- .D+,D)) ) AND did not pass French/47 (DptID = 11) And (SubjectID = '47') And (Grade in ( F,I,)) ) Excluding the ones registered in French/47 in the current course and the ones who passed the course on the second, third ...etc retakes.

SELECT StudentNo
FROM TStdCourses
WHERE (((DptID = 11) And (SubjectID = '27') And
(Grade in ('','-','+','','-','+','','-','+','') ))) AND
StudentNo IN(
SELECT StudentNo
FROM TStdCourses
WHERE (((DptID = 11) And (SubjectID = '47') And
(Grade in ( '','','') )))
EXCEPT(
SELECT StudentNo
FROM TStdCourses
WHERE (((DptID = 11) And (SubjectID = '47') And
(Grade = '' )) AND ((StudyYear = 2004) And
(StudySemester = 2)) ) OR (((DptID = 11) And
(SubjectID = '47') And
(Grade in ('','-','+','','-','+','','-','+','') )))
)
)



Comments Comments
The problem was caused by two issues:

1) DBISAM was putting too many key fields in the temporary index that it was creating for optimizing the sub-query, and this was initially causing the problem.

2) However, this wasn't an issue for non-Arabic locales, so after further investigation it was found that the sorting of the ANSI characters for the Arabic locales was not working properly and sorting non-printable characters higher than printable characters. This messed up the building of the internal character map that is used by DBISAM to build buffer ranges for optimized filters and queries that can use an available index.

The solution was to exclude non-printable characters (#0-#31) from the sorting portion of building the internal character map.


Resolution Resolution
Fixed Problem on 2/26/2005 in version 4.18 build 1
Image