home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Shareware Overload
/
ShartewareOverload.cdr
/
database
/
db4bugs.zip
/
ANOMALY2.TXT
< prev
next >
Wrap
Text File
|
1988-12-23
|
6KB
|
159 lines
In keeping with our commitment to provide you, the user, with support
that will maximize your productivity with the dBASE IV product, we are
continuing our tradition of publishing timely, detailed anomaly and
work-around reports.
The following listing addresses three known anomalies when using
dBASE IV SQL commands. Use of these techniques will aid you in
avoiding anomalies that have been reported to our Software Support
Center. This report will be supplemented as new information is
received.
*******************************************************************
The following queries either fail with an "Internal Error" when
executed by dBASE IV's SQL, or give incomplete results. Three
distinct anomalies are responsible for the seven failing queries:
a) If the data in a table is more than 95% physically sorted on a
field,
BUT less than 100% sorted,
AND that field is indexed
the SQL optimizer will assume that the field is, in fact,
100% sorted. SQL queries against that field will miss the
records which are out of order.
The most likely ways for a user to get a data file "almost in order"
are:
- the user begins using an application for the first time,
and enters a large quantity of data in sorted order (such as
typing in an address book). The user then starts adding,
deleting, and changing records. While the number of changed
records is less than 5% of the database, the anomaly can
occur if the critical field is also indexed.
- the user has an application which uses the dBASE SORT
command to generate a physically sorted file for reporting
purposes. The application also updates the file. The user then
adds an SQL "addition" to the existing dBASE application.
EXAMPLES:
1. SELECT DISTINCT CLIENTS.ACCTNUM, NAME FROM CLIENTS,
TRANSACT
WHERE CLIENTS.ACCTNUM = TRANSACT.ACCTNUM
AND SYMBOL IN
(SELECT SYMBOL FROM TRANSACT
WHERE ACCTNUM=1002);
2. SELECT DISTINCT CLIENTS.ACCTNUM, NAME FROM CLIENTS,
TRANSACT
WHERE CLIENTS.ACCTNUM = TRANSACT.ACCTNUM
AND SYMBOL IN
(SELECT SYMBOL FROM TRANSACT
WHERE ACCTNUM=1002)
AND ACCTNUM = 1144;
Note 1: This query returns an "Ambiguous column name" error since
the column "ACCTNUM" is in both tables. The last line in example
2 should read "AND CLIENTS.ACCTNUM = 1144".
3. SELECT distinct NAME FROM CLIENTS
WHERE "GM" IN
(SELECT trim(SYMBOL) FROM TRANSACT
WHERE ACCTNUM = CLIENTS.ACCTNUM);
Note 2: One needs to add the "DISTINCT" keyword and the
"TRIM()" function indicated above in lower case to get the
right results.
4. SELECT ACCTNUM, COUNT(*) FROM TRANSACT
GROUP BY ACCTNUM
HAVING COUNT(*) > 60;
WORK-AROUND:
Observe that all four queries involve selections involving the
column TRANSACT.ACCTNUM which is also indexed. This is the field
which is "almost in order". The work-around is to drop the index
on the field.
b) If a query contains an existential predicate ("WHERE EXISTS..."),
AND contains a nested subquery,
WHICH has a local predicate,
AND WHICH is against a table of more than 5,000 records,
then the user MAY get an "Internal Error". The error is
handled normally by dBASE IV's error trapping.
EXAMPLES:
1. SELECT NAME FROM CLIENTS
WHERE NOT EXISTS
(SELECT * FROM TRANSACT
WHERE ACCTNUM = CLIENTS.ACCTNUM
AND SYMBOL = "GM");
2. SELECT NAME FROM CLIENTS
WHERE EXISTS
(SELECT * FROM TRANSACT
WHERE ACCTNUM = CLIENTS.ACCTNUM
AND SYMBOL = "GM");
Note the presence of "EXISTS" or "NOT EXISTS", they make the
predicate existential. Also note the nested subquery
"(SELECT * ..." and the local predicate "AND SYMBOL='GM', a
predicate which is confined, or local, to the table
referenced in the subquery. If the above conditions exist and
Also, if the table in the subquery, TRANSACT has more than 5,000,
the anomaly may occur (it won't always).
WORK-AROUND:
Split the query into two queries, storing intermediate
results in a temporary table.
SELECT * FROM TRANSACT
WHERE "GM" = trim(SYMBOL) SAVE TO TEMP T1;
SELECT NAME FROM CLIENTS
WHERE EXISTS
(SELECT * FROM T1 WHERE ACCTNUM = CLIENTS.ACCTNUM);
c) If a query contains a two-table join,
AND a local predicate on the join column,
AND both join columns are indexed,
then the user MAY get an "Internal Error".
EXAMPLE:
SET SQL ON
START DATABASE SAMPLES;
DROP INDEX ORDER_ID;
DBDEFINE TRANSACT;
DBDEFINE STOCK;
CREATE INDEX ORDER_ID ON TRANSACT(ORDER_ID);
CREATE INDEX ORDERID ON STOCK(ORDER_ID);
SELECT TRANSACT.ORDER_ID FROM TRANSACT, STOCK
WHERE TRANSACT.ORDER_ID = STOCK.ORDER_ID
AND TRANSACT.ORDER_ID = '87-105';
WORK-AROUND:
Drop one of the indexes.
Note: The two-table join (WHERE TRANSACT.ORDER_ID =
STOCK.ORDER_ID), the indexes on both joined columns, and the
predicate condition local to the TRANSACT table. Dropping either
index will allow the query to succeed.