View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0001240Database Workbench v5Firebird Modulepublic2015-06-09 10:372018-02-15 14:50
ReporterSvein Erling Tysvær Assigned ToMartijn Tonies  
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionduplicate 
Product Version5.0.10 
Summary0001240: TPlanParser.ParsePlanItem: Unrecognized PLAN syntax -- "NATURAL", "INDEX" or "ORDER" not found after alias: RDB$PRIMARY38)))))
DescriptionWhen trying to prepare

with tmp(Snr, tPrio) as
    (select s.SYKDOMSTILFELLENR, max(mt.PRIO)
     from SYKDOMSTILFELLE s
     join melding m on s.SYKDOMSTILFELLENR = m.SYKDOMSTILFELLENR
     join KV_CTNM2002_t mt on m.C_t = mt.t
     left join P_DIAGNOSEPERIODE(s.diagnosedato, m.diagnosedato) dp on (1=1)
     where mt.topo = '99'
     group by 1)
  select ct.ID, ct.T, count(*), count(distinct t.Snr) Antall
  from tmp t
  join KV_CTNM2002_t ct on t.tPrio = ct.PRIO
  join SYKDOMSTILFELLE s on t.Snr = s.SYKDOMSTILFELLENR
  where ct.TOPO = '99'
  group by 1, 2

DBW 5.0.10 initially gives me this error:

TPlanParser.ParsePlanItem: Unrecognized PLAN syntax -- "NATURAL", "INDEX" or "ORDER" not found after alias: RDB$PRIMARY38)))))

Doing minor changes, like removing a WHERE clause, preparing, adding back the WHERE clause and preparing again, gives me this plan:

PLAN SORT (MERGE (SORT (CT INDEX (XKVCTNM2002T)), SORT (JOIN (JOIN (JOIN (T S ORDER RDB$PRIMARY38, T M INDEX (MELDING_SYKDOMSTILFELLENR), T MT INDEX (XKVCTNM2002T)), P_DIAGNOSEPERIODE NATURAL), S INDEX (RDB$PRIMARY38)))))
Steps To ReproduceI tried to get the same error using only the RDB$ tables (only adding a dummy stored procedure), but failed.

DDL for the tables involved (skipping irrelevant parts) are:

CREATE TABLE SYKDOMSTILFELLE --This table contains 1-2 million rows
(
  SYKDOMSTILFELLENR INTEGER NOT NULL,
  DIAGNOSEDATO DATE,
 PRIMARY KEY (SYKDOMSTILFELLENR)); --RDB$PRIMARY38

CREATE TABLE MELDING --This table contains 3-4 million rows
(
  MELDINGSNR INTEGER NOT NULL,
  SYKDOMSTILFELLENR INTEGER,
  C_T DOM_TNM_T ,
 PRIMARY KEY (MELDINGSNR));
CREATE ASC INDEX MELDING_SYKDOMSTILFELLENR ON MELDING (SYKDOMSTILFELLENR);

CREATE TABLE KV_CTNM2002_T --This table is a lookup table only containing 97 rows and no sensitive data (the other tables contain sensitive data)
(
  ID INTEGER NOT NULL,
  TOPO VARCHAR( 3) COLLATE ISO8859_1,
  T VARCHAR( 16) COLLATE ISO8859_1,
  PRIO INTEGER,
 PRIMARY KEY (ID));
CREATE UNIQUE ASC INDEX XKVCTNM2002T ON KV_CTNM2002_T (TOPO, T);

SET TERM ^^ ;
CREATE PROCEDURE P_DIAGNOSEPERIODE (
  S_DATO Date,
  M_DATO Date)
 returns (
  DIAGNOSEPERIODE Char(1))
AS
declare variable smnd integer;
declare variable mmnd integer;
begin
  smnd = ((extract(year from S_DATO)-1950)*12)+extract(month from S_DATO);
  mmnd = ((extract(year from M_DATO)-1950)*12)+extract(month from M_DATO);
  diagnoseperiode = iif(smnd >= mmnd-4, 'J', 'N');
  suspend;
end ^^
SET TERM ; ^^
Additional InformationDBW 4.4.8 have no problems with this statement (well, short of this stripped down version of my real query simply taking too long to execute).

Don't know whether this issue is similar to 0001177 (that I reported in February) or not.
TagsNo tags attached.

Relationships

related to 0001203 closedMartijn Tonies Plan analysis fails on specific plans 

Activities

Svein Erling Tysvær

2015-06-09 10:53

reporter   ~0000381

Sorry, I forgot:

CREATE DOMAIN DOM_TNM_T AS VARCHAR(10) COLLATE ISO8859_1;

The default character set of the database is ISO8859_1

Martijn Tonies

2018-02-15 14:50

administrator   ~0000455

0001487

Issue History

Date Modified Username Field Change
2015-06-09 10:37 Svein Erling Tysvær New Issue
2015-06-09 10:53 Svein Erling Tysvær Note Added: 0000381
2015-06-09 16:54 Martijn Tonies Status new => acknowledged
2015-06-09 16:54 Martijn Tonies Relationship added related to 0001203
2018-02-15 14:50 Martijn Tonies Note Added: 0000455
2018-02-15 14:50 Martijn Tonies Status acknowledged => closed
2018-02-15 14:50 Martijn Tonies Assigned To => Martijn Tonies
2018-02-15 14:50 Martijn Tonies Resolution open => duplicate