Watch, Follow, &
Connect with Us
Public Report
Report From: InterBase/Server    [ Add a report in this area ]  
Report #:  120523   Status: Reported
Subquery from procedure with with NULL Execute statement  couse empty result set
Project:  InterBase Build #:  WI-V10.0.5.595
Version:    10.0 Submitted By:   Karol Bieniaszewski
Report Type:  Crash / Data loss / Total failure Date Reported:  11/15/2013 11:22:13 PM
Severity:    Serious / Highly visible problem Last Updated: 4/5/2014 4:47:17 PM
Platform:    Not OS or platform specific Internal Tracking #:  
Resolution: None (Resolution Comments) Resolved in Build: : None
Duplicate of:  None
Voting and Rating
Overall Rating: No Ratings Yet
0.00 out of 5
Total Votes: None
Description
Now when we try subquery from procedure which use execute statement inside
and this statement is NULL couse empty resultset without throwing any error
Steps to Reproduce:
CREATE PROCEDURE TEST_EXECUTE(B VARCHAR(10))
RETURNS(A VARCHAR(100))
AS
DECLARE VARIABLE SQL VARCHAR(5000);
BEGIN
SQL = 'SELECT ' || B || ' FROM RDB$DATABASE;';

  EXECUTE STATEMENT SQL INTO :A;
  SUSPEND;
END

commit;

SELECT R.*, (SELECT P.A FROM TEST_EXECUTE(NULL) P) FROM RDB$RELATIONS R

Now: No error and no records returned
Expected: Error or null for P.A field for every record
Workarounds
None
Attachment
None
Comments

Sriram Balasubramanian at 4/4/2014 10:08:21 AM -
Is this duplicate of QC 122621?

Karol Bieniaszewski at 4/5/2014 1:10:56 PM -
No this is different issue
look at this query

SELECT
R.*
FROM
RDB$RELATIONS R
return many rows

but when i add subquery with stored procedure and inside it is execute statement with null query statement to execute - then the same query with only additional subquery return no records without throwing error
SELECT
R.*
, (SELECT P.A FROM TEST_EXECUTE(NULL) P) /* this couse whole query to be empty */
FROM
RDB$RELATIONS R

Server Response from: ETNACODE01