Submitted by: @ArnoBrinkman
PLAN Output (At least the detailed PLAN) should contain also the information from a selectable STORED PROCEDURE used in a statement.
Example DDL/DML to show issue:
--------------------------------------------------------------------------------
CREATE TABLE AUCTIONS
(
AUCTIONID BIGINT NOT NULL,
DESCRIPTION VARCHAR( 200) NOT NULL,
STARTPRICE NUMERIC( 12, 2) NOT NULL,
STEPPRICE NUMERIC( 8, 2),
CONSTRAINT PK_AUCTIONS PRIMARY KEY (AUCTIONID)
);
CREATE TABLE AUCTIONBIDS
(
AUCTIONBIDID BIGINT NOT NULL,
AUCTIONID BIGINT NOT NULL,
BIDPRICE NUMERIC( 12, 2) NOT NULL,
BIDDER VARCHAR( 100) NOT NULL,
BIDDATETIME TIMESTAMP NOT NULL,
CONSTRAINT PK_AUCTIONBIDS PRIMARY KEY (AUCTIONBIDID),
CONSTRAINT FK_AUCTIONBIDS_AUCTIONS FOREIGN KEY (AUCTIONID)
REFERENCES AUCTIONS (AUCTIONID)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE DESC INDEX I_AUCTIONBIDS_AUCTID_PRICE_DESC ON AUCTIONBIDS (AUCTIONID, BIDPRICE);
SET TERM ^^ ;
CREATE PROCEDURE P_GET_LASTBID (
AUCTIONID BigInt)
returns (
AUCTIONBIDID BigInt,
BIDDER VarChar(100),
BIDPRICE Numeric(12,2))
AS
BEGIN
FOR
SELECT
ab.AUCTIONBIDID, ab.BIDDER, ab.BIDPRICE
FROM
AUCTIONBIDS ab
WHERE
ab.AUCTIONID = :AUCTIONID
ORDER BY
ab.BIDPRICE DESC
FETCH FIRST 1 ROWS ONLY
INTO
:AUCTIONBIDID, :BIDDER, :BIDPRICE
DO
BEGIN
SUSPEND;
END
END ^^
SET TERM ; ^^
COMMIT;
INSERT INTO AUCTIONS (AUCTIONID, DESCRIPTION, STARTPRICE, STEPPRICE) VALUES (1, 'Firebird SQL mascot', 80, 10);
INSERT INTO AUCTIONS (AUCTIONID, DESCRIPTION, STARTPRICE, STEPPRICE) VALUES (2, 'Firebird SQL QA collection', 50, 5);
INSERT INTO AUCTIONBIDS (AUCTIONBIDID, AUCTIONID, BIDPRICE, BIDDER, BIDDATETIME) VALUES (1, 2, 50, 'Cheapo', '11/25/2015 11:00:00.000');
INSERT INTO AUCTIONBIDS (AUCTIONBIDID, AUCTIONID, BIDPRICE, BIDDER, BIDDATETIME) VALUES (2, 2, 55, 'ICanDoBetter', '11/25/2015 11:00:24.000');
INSERT INTO AUCTIONBIDS (AUCTIONBIDID, AUCTIONID, BIDPRICE, BIDDER, BIDDATETIME) VALUES (3, 2, 75, 'IWantIt', '11/25/2015 11:01:07.000');
INSERT INTO AUCTIONBIDS (AUCTIONBIDID, AUCTIONID, BIDPRICE, BIDDER, BIDDATETIME) VALUES (4, 1, 100, 'IWantIt', '11/25/2015 11:14:30.000');
COMMIT;
--------------------------------------------------------------------------------
Running next query:
SELECT
*
FROM
AUCTIONS a
LEFT JOIN P_GET_LASTBID(a.AUCTIONID) ON (1 = 1)
Current (FB3.0 RC1) will output PLAN:
PLAN JOIN (A NATURAL, P_GET_LASTBID NATURAL)
Expected something like:
PLAN JOIN (A NATURAL, P_GET_LASTBID (PLAN (AB ORDER I_AUCTIONBIDS_AUCTID_PRICE_DESC)))
Current (FB3.0 RC1) will output detailed PLAN:
Select Expression
-> Nested Loop Join (outer)
-> Table "AUCTIONS" as "A" Full Scan
-> Procedure "P_GET_LASTBID" Scan
Expected something like :
Select Expression
-> Nested Loop Join (outer)
-> Table "AUCTIONS" as "A" Full Scan
-> Procedure "P_GET_LASTBID" Scan
Select Expression
-> First N Records
-> Filter
-> Table "AUCTIONBIDS" as "AB" Access By ID
-> Index "I_AUCTIONBIDS_AUCTID_PRICE_DESC" Range Scan (partial match: 1/2)
Submitted by: @ArnoBrinkman
PLAN Output (At least the detailed PLAN) should contain also the information from a selectable STORED PROCEDURE used in a statement.
Example DDL/DML to show issue:
--------------------------------------------------------------------------------
CREATE TABLE AUCTIONS
(
AUCTIONID BIGINT NOT NULL,
DESCRIPTION VARCHAR( 200) NOT NULL,
STARTPRICE NUMERIC( 12, 2) NOT NULL,
STEPPRICE NUMERIC( 8, 2),
CONSTRAINT PK_AUCTIONS PRIMARY KEY (AUCTIONID)
);
CREATE TABLE AUCTIONBIDS
(
AUCTIONBIDID BIGINT NOT NULL,
AUCTIONID BIGINT NOT NULL,
BIDPRICE NUMERIC( 12, 2) NOT NULL,
BIDDER VARCHAR( 100) NOT NULL,
BIDDATETIME TIMESTAMP NOT NULL,
CONSTRAINT PK_AUCTIONBIDS PRIMARY KEY (AUCTIONBIDID),
CONSTRAINT FK_AUCTIONBIDS_AUCTIONS FOREIGN KEY (AUCTIONID)
REFERENCES AUCTIONS (AUCTIONID)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE DESC INDEX I_AUCTIONBIDS_AUCTID_PRICE_DESC ON AUCTIONBIDS (AUCTIONID, BIDPRICE);
SET TERM ^^ ;
CREATE PROCEDURE P_GET_LASTBID (
AUCTIONID BigInt)
returns (
AUCTIONBIDID BigInt,
BIDDER VarChar(100),
BIDPRICE Numeric(12,2))
AS
BEGIN
FOR
SELECT
ab.AUCTIONBIDID, ab.BIDDER, ab.BIDPRICE
FROM
AUCTIONBIDS ab
WHERE
ab.AUCTIONID = :AUCTIONID
ORDER BY
ab.BIDPRICE DESC
FETCH FIRST 1 ROWS ONLY
INTO
:AUCTIONBIDID, :BIDDER, :BIDPRICE
DO
BEGIN
SUSPEND;
END
END ^^
SET TERM ; ^^
COMMIT;
INSERT INTO AUCTIONS (AUCTIONID, DESCRIPTION, STARTPRICE, STEPPRICE) VALUES (1, 'Firebird SQL mascot', 80, 10);
INSERT INTO AUCTIONS (AUCTIONID, DESCRIPTION, STARTPRICE, STEPPRICE) VALUES (2, 'Firebird SQL QA collection', 50, 5);
INSERT INTO AUCTIONBIDS (AUCTIONBIDID, AUCTIONID, BIDPRICE, BIDDER, BIDDATETIME) VALUES (1, 2, 50, 'Cheapo', '11/25/2015 11:00:00.000');
INSERT INTO AUCTIONBIDS (AUCTIONBIDID, AUCTIONID, BIDPRICE, BIDDER, BIDDATETIME) VALUES (2, 2, 55, 'ICanDoBetter', '11/25/2015 11:00:24.000');
INSERT INTO AUCTIONBIDS (AUCTIONBIDID, AUCTIONID, BIDPRICE, BIDDER, BIDDATETIME) VALUES (3, 2, 75, 'IWantIt', '11/25/2015 11:01:07.000');
INSERT INTO AUCTIONBIDS (AUCTIONBIDID, AUCTIONID, BIDPRICE, BIDDER, BIDDATETIME) VALUES (4, 1, 100, 'IWantIt', '11/25/2015 11:14:30.000');
COMMIT;
--------------------------------------------------------------------------------
Running next query:
SELECT
*
FROM
AUCTIONS a
LEFT JOIN P_GET_LASTBID(a.AUCTIONID) ON (1 = 1)
Current (FB3.0 RC1) will output PLAN:
PLAN JOIN (A NATURAL, P_GET_LASTBID NATURAL)
Expected something like:
PLAN JOIN (A NATURAL, P_GET_LASTBID (PLAN (AB ORDER I_AUCTIONBIDS_AUCTID_PRICE_DESC)))
Current (FB3.0 RC1) will output detailed PLAN:
Select Expression
-> Nested Loop Join (outer)
-> Table "AUCTIONS" as "A" Full Scan
-> Procedure "P_GET_LASTBID" Scan
Expected something like :
Select Expression
-> Nested Loop Join (outer)
-> Table "AUCTIONS" as "A" Full Scan
-> Procedure "P_GET_LASTBID" Scan
Select Expression
-> First N Records
-> Filter
-> Table "AUCTIONBIDS" as "AB" Access By ID
-> Index "I_AUCTIONBIDS_AUCTID_PRICE_DESC" Range Scan (partial match: 1/2)