Post a plan. Also, does the plan change when you move the OUTER JOIN to
the end of the JOIN statements, just before the WHERE clause?

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of henkth
Sent: Friday, February 02, 2007 8:34 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] False result in query

Hi,

I have a weird problem.
In a system we need to print batches of labels and therefor we issue a
query to a firebird database which contains a few joins.
It has to do with requested investigations (ANALYSIS) by a customer
which contains several materials (SPECIMEN) and several
testing-procedures (STAINING) to be performed on little pieces
(CASSETTE) taken from the materials.

The query issued is :

SELECT Analysis.Analysis, Analysis.AN_NUMBER, Analysis.An_Origin,
Analysis.An_Type,
Specimen.Specimen, Specimen.Number as SpecIndex, Specimen.Macroscopy,
Cassette.Cassette, Cassette.Number as CassIndex,
Sample.Sample, Sample.Number, Sample.Request,
Staining.Technique,Cassette.Material,
Staining.NAME as Staining, EXC.Exc_Type FROM SAMPLE SAMPLE
LEFT OUTER JOIN EXC EXC ON EXC.SAMPLE = SAMPLE.SAMPLE
INNER JOIN STAINING Staining ON SAMPLE.STAINING = STAINING.STAINING
INNER JOIN CASSETTE Cassette ON CASSETTE.CASSETTE = SAMPLE.CASSETTE
INNER JOIN SPECIMEN Specimen ON SPECIMEN.SPECIMEN = CASSETTE.SPECIMEN
INNER JOIN ANALYSIS ANALYSIS ON ANALYSIS.ANALYSIS = SPECIMEN.ANALYSIS
WHERE Sample.PRINTBATCH=%d ORDER BY Analysis.AN_NUMBER,
Specimen.Number,Cassette.NUMBER, Staining.NAME, Exc.Exc

This system has been functioning flawless in several enterprises for
over 5 years, until 2 months ago in 1 enterprise we appear to have
received a false result twice, being the investigation-number printed
not matching the real item in the database. All other information is
correct, it just seems that the join to the analysis-table, having a
unique key created by a generator, returns the wrong row.
This happens for just one sample,while other samples connected to the
same cassette display the proper analysisnumber (AN_NUMBER).


We examined the database, and everything seems to be ok. Also we found
no signs of corruption using the standard firebird tools ( gfix ).
Also no interesting entries in Firebird.log.
If we request a reprint of the wrong label for that specific
investigation we get the proper information.

The firebird-engine we use is 1.5.3.4870 .
Development in Delphi, using DbExpress.
Firebird engine runs on W2K server on VmWare as service superserver
mode.
The difference with the past is that the engine has been upgraded from
1.5.2 and the server is migrated to VmWare.


Has anybody experienced a problem like this before ?.


Kind regards,

Henk.



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the
main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links




Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.