Watch, Follow, &
Connect with Us
Public Report
Report From: InterBase/Server    [ Add a report in this area ]  
Report #:  109214   Status: Closed
Query with union is compiled also if table in second query is not specified in from clause - gives wrong result set
Project:  InterBase Build #:  10.0.4.590
Version:    10.0 Submitted By:   Karol Bieniaszewski
Report Type:  Basic functionality failure Date Reported:  10/2/2012 4:59:57 AM
Severity:    Serious / Highly visible problem Last Updated: 9/13/2013 12:39:25 PM
Platform:    Windows 95/98/NT 4.0 on Intel Internal Tracking #:  
Resolution: Fixed (Resolution Comments) Resolved in Build: : 11.0.3.655
Duplicate of:  None
Voting and Rating
Overall Rating: No Ratings Yet
0.00 out of 5
Total Votes: None
Description
Query with union is compiled also if table in second query is not specified in from clause- cuse wrong result set

see steeps to reproduce this on employee database

i test this on XE and also on Interbase 7.1
Steps to Reproduce:
run this query on employee database

SELECT
CUS.CUSTOMER, C.CURRENCY
FROM
CUSTOMER CUS
INNER JOIN COUNTRY C ON C.COUNTRY=CUS.COUNTRY
WHERE
C.CURRENCY='Dollar'

UNION ALL

SELECT
CUS.CUSTOMER, C.CURRENCY
FROM
CUSTOMER CUS
WHERE
C.CURRENCY='Yen'


################################

as you can see in second query in union alias C not exists but query is compiled and executed !

resultset
Signature Design                  Dollar
Dallas Technologies             Dollar
Buttle, Griffith and Co.           Dollar
Mrs. Beauvais                       Dollar
Anini Vacation Rentals          Dollar

################################

changing query to

SELECT
CUS.CUSTOMER, C.CURRENCY
FROM
CUSTOMER CUS
INNER JOIN COUNTRY C ON C.COUNTRY=CUS.COUNTRY
WHERE
C.CURRENCY='Dollar'

UNION ALL

SELECT
CUS.CUSTOMER, C.CURRENCY
FROM
CUSTOMER CUS
INNER JOIN COUNTRY C ON C.COUNTRY=CUS.COUNTRY
WHERE
C.CURRENCY='Yen'


################################

resultset
Signature Design                  Dollar
Dallas Technologies             Dollar
Buttle, Griffith and Co.           Dollar
Mrs. Beauvais                       Dollar
Anini Vacation Rentals          Dollar
MPM Corporation                  Yen

Workarounds
None
Attachment
None
Comments

None

Server Response from: ETNACODE01