Add PLAN used by SELECTABLE STORED PROCEDURE also into PLAN where it used
-------------------------------------------------------------------------
Key: CORE-5035
URL: http://tracker.firebirdsql.org/browse/CORE-5035
Project: Firebird Core
Issue Type: New Feature
Components: Engine
Affects Versions: 4.0 Initial
Environment: Any
Reporter: Arno Brinkman
Priority: Minor
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)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Go from Idea to Many App Stores Faster with Intel(R) XDK
Give your users amazing mobile app experiences with Intel(R) XDK.
Use one codebase in this all-in-one HTML5 development environment.
Design, debug & build mobile apps & 2D/3D high-impact games for multiple OSs.
http://pubads.g.doubleclick.net/gampad/clk?id=254741551&iu=/4140
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
