SQL Support - K4000007 - 27January 2000 updated 21 Jun 2000

Click here for information on the updates to this fix.

Recent versions of our SQL support tried to add support for multiple outer joins using either SQL2 syntax or Oracle syntax (also used by Crystal reports). These changes were not entirely satisfactory, and have now been reworked. DP4 SQL now supports much of the useful SQL2 join syntax. The restrictions are:

• NATURAL,CROSS and UNION joins are not yet supported. (The functionality of NATURAL and CROSS joins is available using more traditional SQL constructions.)
• The syntax for FULL joins is accepted. However the output is curently the same as what a LEFT JOIN would output. Implementation of FULL joins is expensive at run time and would require considerable modification of our SQL run time engine.
• Some complex nested uses of RIGHT joins cause problems for our compiler. When this happens an internal error will be reported. In general you should use LEFT joins where possible.

The fields used in Joins must be specified using the ON syntax. The USING syntax is not yet supported.

We have also fixed a problem which caused poor performance with some third party software. Certain tools, such as Microsoft Access generate SQL which is excessively parenthesised. These extra parentheses previously caused our SQL compiler to fail to generate the most efficient access plan for certain queries. Numerous other small performance improvements have been made. We hope to announce more significant performance improvements to our SQL engine soon.

Where possible the SQL2 syntax for joins should be used in preference to the Oracle syntax. The ORACLE syntax is necessarily ambiguous where there is more than one outer join in a query.

A number of other changes have also been made as follows:

• The number of accesses to the DP4 data dictionary during compilation and execution of statements has been reduced. This should improve performance especially in networked configurations where there is no local database.
• A problem with sorting has been fixed. This caused large sorts (ORDER BY) to fail, either by generating a spurious error message, or by simply not sorting properly. A side effect of this problem is that DISTINCT did not work properly.
• Time fields were not sorted properly, only the hour component was sorted on.
• Heavily parenthesised expressions were not compiled properly.

Further changes 21 Jun 2000

It has come to our attention that there were problems with this fix. The problem concerned the fixes made to the compiler in the area of parenthesized expressions in where clauses . After the last changes some parenthesized where clauses were compiled incorrectly. In particular:

• A clause of the form (A=B) AND ((C=D) OR (E=F)) was treated as though it was ((A=B) AND (C=D)) OR ((E=F)). However A=B AND ((C=D) OR (E=F)) was compiled properly, as was any version of the clause where the two parts of the AND where interchanged.

A side effect of this bug, even where the incorrect condition did not actually produce wrong results was that optimisation of the search condition failed to spot equi-joins, which resulted in a big increase in execution time.

A second bug introduced by this fix was that auto-incrementing was not working any longer. This is now fixed. To use the auto-increment facility use an INSERT statement on the auto-increment table. The next auto-increment key will automatically be assigned to the appropriate field.

DP4 Products/Versions Affected

_{Where the version affected is given as 4.5xx or 4.6xx, all versions of DP4 issued prior to the date of the fix are potentially affected. Where a specific version number is given the problem was introduced by that release and prior releases are unaffected. If a patch release number is also specified (in parentheses) , the fault was introduced at that specific patch level.}

Downloads

4.520/617 Win32 SQL.EXE SQLLIBT.DLL  

_{Due to limitations on available web space downloadable fixes are only available for the most commonly used environments, and may not be separately available in both 4.5xx and 4.6xx flavours. If a file you require is not available you can ask us to e-mail it to you.}