[Firebird-devel] Lateral derived tables (was: Plans to support cross and outer apply)
All, The key point of this standard feature is to allow sub-queries to reference priorly defined contexts (in joins). While thinking about this, I have a few questions to raise here. The standard defines LATERAL for derived tables only. This sounds logical but there are some corner cases to be discussed. 1) LATERAL used when it doesn't make sense, e.g. -- single context FROM LATERAL (SELECT ...) -- first context FROM LATERAL (SELECT ...) JOIN T ... In both cases, there's nothing priorly defined to be referenced to. a) should be prohibited syntactically b) should raise error at prepare c) should raise warning at prepare d) should be silently ignored 2) LATERAL does not reference prior contexts -- T is not referenced from inside the derived table FROM T JOIN LATERAL (SELECT ...) a) should raise error at prepare b) should be ignored, but join order must be enforced (derived table is executed per every row of T) -- perhaps with a warning? c) should be ignored, join order can be changed by the optimizer -- perhaps with a warning? 3) LATERAL was historically implied for joined stored procedures, e.g. FROM T JOIN P(T.ID) ON 1=1 It used to produce invalid plan (and runtime errors) in older versions but FB3 handles it properly by executing P after reading T. a) keep status quo, disallow LATERAL syntax for procedures b) keep status quo, allow LATERAL for procedures as syntax noise (LATERAL is implied even if missing) c) allow LATERAL for procedures, allow external references via parameters only if LATERAL is specified (thus breaking legacy behaviour) d) disallow LATERAL for procedures, require to be rewritten as standard-friendly (thus breaking legacy behaviour): FROM T JOIN LATERAL (SELECT ID FROM P(T.ID)) 4) LATERAL in nested parenthesis joins, e.g. FROM T1 JOIN LATERAL (T2 JOIN T3 ON T1.ID1 = T2.ID2 AND T2.ID3 = T3.ID3) ON TRUE a) should be allowed b) should not be allowed Opinions, please. My personal votes: 1d, 2c, 3a, 4b BTW, I see no way to allow LATERAL in RIGHT or FULL outer joins, so it should raise error. Dmitry -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5608) Massive blob field usage end up in exception in fbclient.dll / fbclient.so with error "Invalid Blob ID"
Massive blob field usage end up in exception in fbclient.dll / fbclient.so with
error "Invalid Blob ID"
---
Key: CORE-5608
URL: http://tracker.firebirdsql.org/browse/CORE-5608
Project: Firebird Core
Issue Type: Bug
Components: API / Client Library
Environment: Server Version: LI-V3.0.2.32703 Firebird 3.0 AMD64
Client: 3.0.2.32703. dll / so Windows 32/64Bit /Linux 64Bit
Reporter: Sascha Michel
Priority: Critical
Massiv writing BlobFields via StoredProcedure ( sql insert not tested ) will
end up in a exception in the fbclient.
example code for reproducing the problem.
if ( AMsg->Size < 8190 )
IBCSP_PutMailTemp->ParamByName("MessageShort")->AsString =
AMsg->ToString();
else
IBCSP_PutMailTemp->ParamByName("Message")->LoadFromStream( AMsg
, ftBlob);
IBCSP_PutMailTemp->ExecProc();
When AMsg->Size lower 8190 bytes the SP use the VarChar Parameter.
So i can write without any problems 3200 rows in 152 seconds.
This work without any Problem more then 20 times with 3200 rows.
When i only use the Blob version without the if.
IBCSP_PutMailTemp->ParamByName("Message")->LoadFromStream( AMsg , ftBlob);
I got an exception after 80-150 rows in the fbclient.dll.
The error message is "Invalid BLOB ID" and the debugger always catches it at
the same position.
Here the sample database:
SET SQL DIALECT 3;
CREATE DOMAIN "d_BLOB_Text_Gross" AS
BLOB SUB_TYPE 1 SEGMENT SIZE 4096 CHARACTER SET UTF8;
CREATE DOMAIN "d_Akt_DatumZeit" AS
TIMESTAMP
DEFAULT current_timestamp;
CREATE DOMAIN "d_strMax" AS
VARCHAR(8191) CHARACTER SET UTF8
DEFAULT ''
COLLATE UTF8;
CREATE GENERATOR "GEN_E-MailNewIn_ID";
CREATE TABLE "E-MailNewIn" (
"ENI_MaildID" BIGINT NOT NULL,
"ENI_Message" "d_BLOB_Text_Gross",
"ENI_Groesse" INTEGER,
"ENI_Eingegangen" "d_Akt_DatumZeit"
);
ALTER TABLE "E-MailNewIn" ADD CONSTRAINT "PK_E-MailNewIn" PRIMARY KEY
("ENI_MaildID");
SET TERM ^ ;
/* Trigger: "E-MailNewIn_BI" */
CREATE OR ALTER TRIGGER "E-MailNewIn_BI" FOR "E-MailNewIn"
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new."ENI_MaildID" is null) then
new."ENI_MaildID" = gen_id("GEN_E-MailNewIn_ID",1);
end
^
create or alter procedure "NewMailIn" (
"Message" "d_BLOB_Text_Gross",
"MessageGroesse" integer,
"MessageShort" "d_strMax" = null)
returns (
"RetCode" integer)
as
begin
if ( :"MessageShort" is not null ) then
insert into "E-MailNewIn" ( "ENI_Message", "ENI_Groesse" ) values (
:"MessageShort" , :"MessageGroesse" );
else
insert into "E-MailNewIn" ( "ENI_Message", "ENI_Groesse" ) values (
:"Message" , :"MessageGroesse" );
suspend;
end
^
SET TERM ; ^
--
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
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Lateral derived tables (was: Plans to support cross and outer apply)
Hi, 1aBecause it is the same asFROM LEFT JOIN (SELECT) 2a LATERAL is for context reference and cannot be missed 3b if it work already as lateral but is this the true? Or only for referenced stored proc? 4b as it should be derived table Regards,Karol Bieniaszewski Oryginalna wiadomość Od: Dmitry Yemanov Data: 08.09.2017 10:03 (GMT+01:00) Do: firebird-devel@lists.sourceforge.net Temat: [Firebird-devel] Lateral derived tables (was: Plans to support cross and outer apply) All, The key point of this standard feature is to allow sub-queries to reference priorly defined contexts (in joins). While thinking about this, I have a few questions to raise here. The standard defines LATERAL for derived tables only. This sounds logical but there are some corner cases to be discussed. 1) LATERAL used when it doesn't make sense, e.g. -- single context FROM LATERAL (SELECT ...) -- first context FROM LATERAL (SELECT ...) JOIN T ... In both cases, there's nothing priorly defined to be referenced to. a) should be prohibited syntactically b) should raise error at prepare c) should raise warning at prepare d) should be silently ignored 2) LATERAL does not reference prior contexts -- T is not referenced from inside the derived table FROM T JOIN LATERAL (SELECT ...) a) should raise error at prepare b) should be ignored, but join order must be enforced (derived table is executed per every row of T) -- perhaps with a warning? c) should be ignored, join order can be changed by the optimizer -- perhaps with a warning? 3) LATERAL was historically implied for joined stored procedures, e.g. FROM T JOIN P(T.ID) ON 1=1 It used to produce invalid plan (and runtime errors) in older versions but FB3 handles it properly by executing P after reading T. a) keep status quo, disallow LATERAL syntax for procedures b) keep status quo, allow LATERAL for procedures as syntax noise (LATERAL is implied even if missing) c) allow LATERAL for procedures, allow external references via parameters only if LATERAL is specified (thus breaking legacy behaviour) d) disallow LATERAL for procedures, require to be rewritten as standard-friendly (thus breaking legacy behaviour): FROM T JOIN LATERAL (SELECT ID FROM P(T.ID)) 4) LATERAL in nested parenthesis joins, e.g. FROM T1 JOIN LATERAL (T2 JOIN T3 ON T1.ID1 = T2.ID2 AND T2.ID3 = T3.ID3) ON TRUE a) should be allowed b) should not be allowed Opinions, please. My personal votes: 1d, 2c, 3a, 4b BTW, I see no way to allow LATERAL in RIGHT or FULL outer joins, so it should raise error. Dmitry -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdotFirebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Lateral derived tables (was: Plans to support cross and outer apply)
Hi, 1a 2c 3b 4b -- Mgr. Jiří Činčura https://www.tabsoverspaces.com/ -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Lateral derived tables
On 8-9-2017 10:03, Dmitry Yemanov wrote: All, The key point of this standard feature is to allow sub-queries to reference priorly defined contexts (in joins). While thinking about this, I have a few questions to raise here. The standard defines LATERAL for derived tables only. This sounds logical but there are some corner cases to be discussed. Isn't this all contained in the SQL specification? 1) LATERAL used when it doesn't make sense, e.g. -- single context FROM LATERAL (SELECT ...) -- first context FROM LATERAL (SELECT ...) JOIN T ... In both cases, there's nothing priorly defined to be referenced to. a) should be prohibited syntactically b) should raise error at prepare c) should raise warning at prepare d) should be silently ignored It might not make sense, but syntactically the SQL specification allows this, and I can't find a rule that disallows it. As far as I can tell, as a lateral derived table is only allowed to reference table factors that are defined early, it simply devolves to a (simple) derived table (at least that is how I read the access rule in "If a or LDT simply containing expression> QE is specified, then the result of LDT is the result of QE.". See also the definition of in the SQL standard and the rules in section . 2) LATERAL does not reference prior contexts -- T is not referenced from inside the derived table FROM T JOIN LATERAL (SELECT ...) a) should raise error at prepare b) should be ignored, but join order must be enforced (derived table is executed per every row of T) -- perhaps with a warning? c) should be ignored, join order can be changed by the optimizer -- perhaps with a warning? Same as previous, should be allowed, it devolves to a simple derived table. See also rules. Also wording like "If TRB contains a containing an outer reference that references TRA, then shall not contain RIGHT or FULL." implies that there can be TRB with a lateral derived table that does not have an outer reference to TRA. 3) LATERAL was historically implied for joined stored procedures, e.g. FROM T JOIN P(T.ID) ON 1=1 It used to produce invalid plan (and runtime errors) in older versions but FB3 handles it properly by executing P after reading T. a) keep status quo, disallow LATERAL syntax for procedures b) keep status quo, allow LATERAL for procedures as syntax noise (LATERAL is implied even if missing) c) allow LATERAL for procedures, allow external references via parameters only if LATERAL is specified (thus breaking legacy behaviour) d) disallow LATERAL for procedures, require to be rewritten as standard-friendly (thus breaking legacy behaviour): FROM T JOIN LATERAL (SELECT ID FROM P(T.ID)) I suggest 3b 4) LATERAL in nested parenthesis joins, e.g. FROM T1 JOIN LATERAL (T2 JOIN T3 ON T1.ID1 = T2.ID2 AND T2.ID3 = T3.ID3) ON TRUE a) should be allowed b) should not be allowed Not allowed by the syntax construction in the SQL specification: ::= LATERAL ::= ::= ::= [ ] [ ] [ ] [ clause> ] ::= | UNION [ ALL | DISTINCT ] [ ] | EXCEPT [ ALL | DISTINCT ] [ ] ::= | INTERSECT [ ALL | DISTINCT ] [ ] ::= | [ ] [ ] [ clause> ] ::= | | ::= TABLE ::= SELECT [ ] Opinions, please. My personal votes: 1d, 2c, 3a, 4b BTW, I see no way to allow LATERAL in RIGHT or FULL outer joins, so it should raise error. "3) If TRB contains a containing an outer reference that references TRA, then shall not contain RIGHT or FULL." (see rules for ) Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Lateral derived tables
08.09.2017 15:31, Mark Rotteveel wrote:
Isn't this all contained in the SQL specification?
Reading the spec may differ. And we don't follow the standard strictly
sometimes.
3) LATERAL was historically implied for joined stored procedures, e.g.
I suggest 3b
4) LATERAL in nested parenthesis joins, e.g.
Not allowed by the syntax construction in the SQL specification:
In our parser:
%type table_primary
table_primary
: table_proc
| derived_table
| '(' joined_table ')'
;
IMHO, it wouldn't look logical to support LATERAL for "table_proc" but
disallow it for "joined table".
Dmitry
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Lateral derived tables
On 8-9-2017 15:26, Dmitry Yemanov wrote:
08.09.2017 15:31, Mark Rotteveel wrote:
Isn't this all contained in the SQL specification?
Reading the spec may differ. And we don't follow the standard strictly
sometimes.
3) LATERAL was historically implied for joined stored procedures, e.g.
I suggest 3b
4) LATERAL in nested parenthesis joins, e.g.
Not allowed by the syntax construction in the SQL specification:
In our parser:
%type table_primary
table_primary
: table_proc
| derived_table
| '(' joined_table ')'
;
IMHO, it wouldn't look logical to support LATERAL for "table_proc" but
disallow it for "joined table".
But LATERAL **is** a table_primary, it doesn't **use** table_primary (at
least not until you dive down into the select_expr), the rule would become
%type table_primary
table_primary
: table_proc
| derived_table
| lateral_derived_table
| '(' joined_table ')'
;
With lateral_derived_table defined something like:
lateral_derived_table
: LATERAL '(' select_expr ')' as_noise correlation_name
derived_column_list;
(or maybe simply with production: LATERAL derived_table)
Note that this may be an indication that I need to revise my opinion on
point 3 though.
Mark
--
Mark Rotteveel
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Lateral derived tables
08.09.2017 16:55, Mark Rotteveel wrote:
%type table_primary
table_primary
: table_proc
| derived_table
| '(' joined_table ')'
;
IMHO, it wouldn't look logical to support LATERAL for "table_proc" but
disallow it for "joined table".
But LATERAL **is** a table_primary, it doesn't **use** table_primary (at
least not until you dive down into the select_expr), the rule would become
I understood that you suggested to allow "LATERAL table_proc" (for
procedures). "LATERAL derived_table" is by standard, no questions here.
But you suggested to disallow "LATERAL ( joined_table )". This is what I
see as inconsistent. I'd rather disallow LATERAL for both non-standard
rules, or allow it for both.
%type table_primary
table_primary
: table_proc
| derived_table
| lateral_derived_table
| '(' joined_table ')'
;
This is how I see it too.
Note that this may be an indication that I need to revise my opinion on
point 3 though.
It seems so ;-)
Dmitry
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Lateral derived tables (was: Plans to support cross and outer apply)
Dmitry, > Opinions, please. > > My personal votes: 1d, 2c, 3a, 4b For myself: 1d, 2c, 3a or 3b, 4b -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
