Bug in Access Execution plan ?

M

Mathieu Pagé

Hi,

I have a problem with a query that is returning me a "Divide by Zero"
error when it should not (Talked about this in an other thread). I have
found what is the problem, however I don't know how to correct it since,
I think, it's caused by a bug in Access (I should not say that :).

I'll try to simplify the problems.

I have these queries :

qryA : SELECT a, b, c FROM tableA WHERE d IS NOT NULL; // a, b and c are
never equal to zero

qryB : SELECT e, f FROM tableB WHERE g = 1; // f is never equal to zero
when g = 1, so this query should only return rows with "f <> 0".

qryC : SELECT a, b, IIF(a < b; a / b; b/ a) as CalculatedField FROM qryA
INNER JOIN qryB ON qryA.a = qryB.e WHERE IIF(a < b; a / b; b/ a) > 0;

Now, what I expect is that Access execute qryA and qryB then qryC.
However, I asked Access to output its plan in a text file and what I get
is that the condition in qryB and qryC are evaluated at the same time as
if they were in a single query.

So two things may explain the error I get.

1) Even if "g = 1" evaluate to false, "IIF(a < b; a / b; b/ a) > 0" is
evaluated, causing a division by zero.

2) "IIF(a < b; a / b; b/ a) > 0" is evaluated first, before "g = 1".

In each case it should not happen, because the first condition was put
there to ensure the second can be evaluated correctly.

Does anyone have a hint about what is going on?

Here is the Access execution plan, the condition are evaluated in the
step '04)':

<code>
- Inputs to Query -
Table 'tblAffectations'
Using index 'tblTaches_tblAffectations_FK1'
Having Indexes:
tblTaches_tblAffectations_FK1 1313 entries, 4 pages, 9 values
which has 1 column, fixed
tblEmployes_tblAffectations_FK1 1313 entries, 3 pages, 27 values
which has 1 column, fixed
tblDetailtblAffectations 1313 entries, 4 pages, 597 values
which has 1 column, fixed
tblAffectations_PK 1313 entries, 4 pages, 1313 values
which has 1 column, fixed, unique, clustered and/or counter,
primary-key, no-nulls
Table 'tblTaches'
Table 'tblDetailPreDefinit'
Using index 'tblDetailPreDefinit_PK'
Having Indexes:
tblDetailPreDefinit_PK 647 entries, 3 pages, 647 values
which has 1 column, fixed, unique, primary-key, no-nulls
Table 'tblDetailPreDefinitLinguistique'
Using index 'tblDetailPreDefinitNonLinguistique1_PK'
Having Indexes:
tblDetailPreDefinitNonLinguistique1_PK 647 entries, 3 pages, 647 values
which has 1 column, fixed, unique, primary-key, no-nulls
- End inputs to Query -

01) Restrict rows of table tblTaches
by scanning
testing expression "Not tblTaches.CodeService Is Null"
02) Inner Join result of '01)' to table 'tblAffectations'
using index 'tblAffectations!tblTaches_tblAffectations_FK1'
join expression "tblTaches.CodeTache=tblAffectations.CodeTache"
03) Group result of '02)'
04) Inner Join result of '03)' to table 'tblDetailPreDefinit'
using index 'tblDetailPreDefinit!tblDetailPreDefinit_PK'
join expression
"reqNbMotsAffectesParDetailParService.CodeDetail=reqNbMotsParDetailParService.CodeDetail"
then test expression "tblDetailPreDefinit.CodeUnite=1 And
IIf([SommeDeNbMots]>[Quantite],([SommeDeNbMots]-[Quantite])/[Quantite],([Quantite]-[SommeDeNbMots])/[SommeDeNbMots])>0"
05) Inner Join result of '04)' to table 'tblDetailPreDefinitLinguistique'
using index
'tblDetailPreDefinitLinguistique!tblDetailPreDefinitNonLinguistique1_PK'
join expression
"tblDetailPreDefinit.CodeDetail=tblDetailPreDefinitLinguistique.CodeDetail"
then test expression
"reqNbMotsAffectesParDetailParService.CodeService=reqNbMotsParDetailParService.CodeService"
</code>
 
M

Mathieu Pagé

Mathieu Pagé a écrit :
Hi,

I have a problem with a query that is returning me a "Divide by Zero"
error when it should not (Talked about this in an other thread). I have
found what is the problem, however I don't know how to correct it since,
I think, it's caused by a bug in Access (I should not say that :).

I'll try to simplify the problems.

I have these queries :

qryA : SELECT a, b, c FROM tableA WHERE d IS NOT NULL; // a, b and c are
never equal to zero

qryB : SELECT e, f FROM tableB WHERE g = 1; // f is never equal to zero
when g = 1, so this query should only return rows with "f <> 0".

qryC : SELECT a, b, IIF(a < b; a / b; b/ a) as CalculatedField FROM qryA
INNER JOIN qryB ON qryA.a = qryB.e WHERE IIF(a < b; a / b; b/ a) > 0;

Now, what I expect is that Access execute qryA and qryB then qryC.
However, I asked Access to output its plan in a text file and what I get
is that the condition in qryB and qryC are evaluated at the same time as
if they were in a single query.

So two things may explain the error I get.

1) Even if "g = 1" evaluate to false, "IIF(a < b; a / b; b/ a) > 0" is
evaluated, causing a division by zero.

2) "IIF(a < b; a / b; b/ a) > 0" is evaluated first, before "g = 1".

In each case it should not happen, because the first condition was put
there to ensure the second can be evaluated correctly.

Does anyone have a hint about what is going on?

Here is the Access execution plan, the condition are evaluated in the
step '04)':

<code>
- Inputs to Query -
Table 'tblAffectations'
Using index 'tblTaches_tblAffectations_FK1'
Having Indexes:
tblTaches_tblAffectations_FK1 1313 entries, 4 pages, 9 values
which has 1 column, fixed
tblEmployes_tblAffectations_FK1 1313 entries, 3 pages, 27 values
which has 1 column, fixed
tblDetailtblAffectations 1313 entries, 4 pages, 597 values
which has 1 column, fixed
tblAffectations_PK 1313 entries, 4 pages, 1313 values
which has 1 column, fixed, unique, clustered and/or counter,
primary-key, no-nulls
Table 'tblTaches'
Table 'tblDetailPreDefinit'
Using index 'tblDetailPreDefinit_PK'
Having Indexes:
tblDetailPreDefinit_PK 647 entries, 3 pages, 647 values
which has 1 column, fixed, unique, primary-key, no-nulls
Table 'tblDetailPreDefinitLinguistique'
Using index 'tblDetailPreDefinitNonLinguistique1_PK'
Having Indexes:
tblDetailPreDefinitNonLinguistique1_PK 647 entries, 3 pages, 647 values
which has 1 column, fixed, unique, primary-key, no-nulls
- End inputs to Query -

01) Restrict rows of table tblTaches
by scanning
testing expression "Not tblTaches.CodeService Is Null"
02) Inner Join result of '01)' to table 'tblAffectations'
using index 'tblAffectations!tblTaches_tblAffectations_FK1'
join expression "tblTaches.CodeTache=tblAffectations.CodeTache"
03) Group result of '02)'
04) Inner Join result of '03)' to table 'tblDetailPreDefinit'
using index 'tblDetailPreDefinit!tblDetailPreDefinit_PK'
join expression
"reqNbMotsAffectesParDetailParService.CodeDetail=reqNbMotsParDetailParService.CodeDetail"

then test expression "tblDetailPreDefinit.CodeUnite=1 And
IIf([SommeDeNbMots]>[Quantite],([SommeDeNbMots]-[Quantite])/[Quantite],([Quantite]-[SommeDeNbMots])/[SommeDeNbMots])>0"

05) Inner Join result of '04)' to table 'tblDetailPreDefinitLinguistique'
using index
'tblDetailPreDefinitLinguistique!tblDetailPreDefinitNonLinguistique1_PK'
join expression
"tblDetailPreDefinit.CodeDetail=tblDetailPreDefinitLinguistique.CodeDetail"
then test expression
"reqNbMotsAffectesParDetailParService.CodeService=reqNbMotsParDetailParService.CodeService"

</code>

Hi,

Just want to let you know that I worked around this problem. However i'm
still interested to discuss it. Is this a bug in Access or was it a
mistake from myself ?

Mathieu Pagé
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top