Watch, Follow, &
Connect with Us

QualityCentral

Public Report
Report From: InterBase/Server    [ Add a report in this area ]  
Report #:  74260   Status: Reported
Strange subquery behaviour
Project:  InterBase Build #:  -
Version:    8.0 Submitted By:   Stephen Cross
Report Type:  Issue Date Reported:  5/26/2009 9:54:52 PM
Severity:    Infrequently encountered problem Last Updated:
Platform:    All platforms Internal Tracking #:  
Resolution: None  Resolved in Build: : None
Duplicate of:  None
Voting and Rating
Overall Rating: No Ratings Yet
0.00 out of 5
Total Votes: None
Description
This issue seems to happen for versions of InterBase between 7 and 9 inclusive. Certain COUNT subqueries give a different result than I would expect if the subquery is not correlated with the main query and the main query is being executed within a FOR SELECT loop of a stored procedure or trigger.
Steps to Reproduce:
Run this script:

---

create table A (A1 INTEGER);

create table B (B1 INTEGER, B2 INTEGER);

create table C (C1 INTEGER);

insert into A values (1);
insert into A values (2);

insert into B values (1, 1);
insert into B values (2, 2);

insert into C values (1);
insert into C values (2);

CREATE PROCEDURE test1
RETURNS (v1 integer, v2 integer, v3 integer, v4 integer) AS
  DECLARE VARIABLE CVal INTEGER;
  DECLARE VARIABLE Cnt1 integer;
  DECLARE VARIABLE Cnt2 integer;
BEGIN
  FOR SELECT C1
    FROM C order by C1
    INTO :CVal
    DO
    BEGIN
      SELECT COUNT(*) FROM A WHERE (A.A1 = :CVal) AND (((SELECT COUNT(*) FROM B WHERE (B.B1 = :CVal) AND (B.B2 = 1))) = 0) INTO :Cnt1;
      SELECT COUNT(*) FROM A WHERE (A.A1 = :CVal) AND (((SELECT COUNT(*) FROM B WHERE (B.B1 = A.A1) AND (B.B2 = 1))) = 0) INTO :Cnt2;
      IF (:CVal = 1) THEN
      BEGIN
        V1 = Cnt1;
        V2 = Cnt2;
      END
      IF (:CVal = 2) THEN
      BEGIN
        V3 = Cnt1;
        V4 = Cnt2;
      END
    END

END;

execute procedure test1;

---

When I run this script I get the following output

   V1    V2    V3    V4
===== ===== ===== =====
    0     0     0     1

when I would expect


   V1    V2    V3    V4
===== ===== ===== =====
    0     0     1     1

The two main SELECT queries in the procedure should produce the same result in variables Cnt1 and Cnt2. The first iteration through the loop (with variable CVal being 1) this is indeed what happens. But with the second iteration we get Cnt1 = 0 and Cnt2 = 1. I think the value of Cnt1 is wrong in this case, both values should be 1.
Workarounds
None
Attachment
None
Comments

None

Server Response from: ETNACODE01

Copyright© 1994 - 2013 Embarcadero Technologies, Inc. All rights reserved.