FWD » Core Development » Database

These are some issues which I encountered while testing MSSQL dialect but found to affect others, as well. I did not go too deep on them so I cannot decide at this moment if they are classified as persistence/database, runtime or even conversion.

1. Primary index not used
   I do not recall the exact mechanism for picking the right one, but it seems that we may have a problem. The classic book table has 5 indexes: book-id (primary), author-id, isbn, pub-date, and publisher. If no sorting order is specified, 4GL seems to use the primary one (naturally). However, FWD emits them in following order (in schema_index.sql): idx__book_isbn, idx__book_book_id, idx__book_author_id, idx__book_pub_date, idx__book_publisher. This may be irrelevant, but at runtime, a query like

   FIND FIRST book WHERE (book.book-id EQ 1) = (book.isbn EQ '1') LOCK NO-ERROR.

   will convert to

   new FindQuery(book, "(book.bookId = 1) = (upper(book.isbn) = '1')", null, "book.isbn asc", LockType.NONE).silentFirst();

   Since the idx__book_isbn will be used, the result is different (same for MSSQL and PSQL)
2. Case-sensitivity in queries
   It looks to me that we do not correctly honour the case insensitivity in where expressions. The following statement

   FIND FIRST book WHERE STRING(book.isactive AND book.isactive2) EQ "yes" NO-LOCK NO-ERROR.

   is converted as

   new FindQuery(book, "toString(book.isactive = true and book.isactive2 = true) = 'YES'", null, "book.bookId asc", LockType.NONE).silentFirst();

   Note the "yes" literal was uppercased in order to facilitate the case-insensitive test, but there is no upper() injected to the left side of = operator. Since toString() operator will return a lowercase string, the compare operation will fail on a CS collation. Should upper be injected at runtime, in preprocessor, maybe? (Tested with MSSQL with CS collation and PSQL. MSSQL with CI collation works correctly).