This file contains information describing any functionality changes, which may be introduced by the code fixes contained in this PTF.
The fix for defect 80233 affects SQL query behavior when the query uses columns
containing null values. Let us presume a table, "tableA", with a column, "colB". There
are eight rows in the table, and in three of the rows the value of colB is null,
in four rows the value of colB is "P", and in one row the value of colB is "V".
Looking at a couple of sample queries:
select * from tableA where colB like null
select * from tableA where colB not like "V%"
Prior to SQLBase 8.5 PTF4, the result set for the first query would contain
three rows - the three with nulls in colB. The result set for the second query
would contain seven rows - the three nulls, plus the four rows with "P". However, this is inconsistent with
general SQL standards, which state that a null should not be considered a match
in any comparison expression (except for the IS NULL clause). Therefore, in
SQLBase 8.5 PTF4 and after, the first query will return zero rows, and the
second query will return four rows.
There is a change in behavior in PTF3 for SQL SELECT statements which contain no
aggregates and no search condition, but do
contain an ORDER BY clause containing columns or expressions exactly
matching the key of an index.
Prior Behavior:
Prior to PTF3 the SQLBase Optimizer may choose to use the index for ordering
the query results.
Current Behavior:
With PTF3 the SQLBase Optimizer may choose not to use the same index for
ordering the query results.
Status of Behavior Change:
The behavior change is intentional because testing indicates that in all cases,
the Query Access Cycle of the PTF 3 behavior outperforms the previous behavior
on average by a 2-1 ratio, when processing all query results. However, testing
also indicates that in some cases with very large result sets the Execute phase
of the Query Access Cycle takes slightly more time when compared with the
previous behavior.
Some database views created under SQLBase 8.5 PTF2 can cause error code 2104 when used with PTF3. To correct this, UNLOAD the affected database (using either PTF2 or PTF3 to do the UNLOAD), then LOAD the database using PTF3.
There is no additional functionality added in this PTF.
There is no additional functionality added in this PTF.
Copyright © Gupta Technologies LLC. GUPTA, the GUPTA logo, Centura, and all GUPTA products are licensed or registered trademarks of Gupta Technologies, LLC., All other products are trademarks or registered trademarks of their respective owners. Copyright © 2001-2005 Gupta Technologies