Hi,

It does change indeed.

Before moving the outer join :

PLAN SORT (JOIN (JOIN (SAMPLE INDEX (RDB$FOREIGN62),EXC INDEX
(RDB$FOREIGN66)),JOIN (STAINING INDEX (RDB$PRIMARY23),
CASSETTE INDEX (RDB$PRIMARY42),SPECIMEN INDEX (RDB$PRIMARY38),ANALYSIS
INDEX (RDB$PRIMARY27))))


After moving the outer join :

PLAN SORT (JOIN (JOIN (ANALYSIS NATURAL,SPECIMEN INDEX
(RDB$FOREIGN41),CASSETTE INDEX (RDB$FOREIGN45),
SAMPLE INDEX (RDB$FOREIGN59,RDB$FOREIGN62),STAINING INDEX
(RDB$PRIMARY23)),EXC INDEX (RDB$FOREIGN66)))

Regards,

Henk.

--- In firebird-support@yahoogroups.com, "Rick Debay" <rdebay@...> wrote:
>
> 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.
>