dynamobi/dynamobi — Issue #863: [FRG-7] problematic table reference syntax in explain plan
Author: dynamobi-build
Created: 2011-12-30 03:53:37+00:00
State: open
Labels: FRG, Planner Framework

[reporter="jvs", created="Mon, 19 Dec 2005 15:23:59 -0500 (GMT-05:00)"]
Contrived script below shows the problem. Besides being ugly, the table reference syntax in explain plan can lead to problems because the optimizer uses these strings to match relational expressions, so in this case it could get a false match (assuming the index names were contrived to match as well).

0: jdbc:farrago:> create schema "a, b";

No rows affected (0.105 seconds)

0: jdbc:farrago:> create schema "a";

No rows affected (0.08 seconds)

0: jdbc:farrago:> create table "a, b"."c"(i int not null primary key);

No rows affected (0.343 seconds)

0: jdbc:farrago:> create table "a"."b, c"(i int not null primary key);

No rows affected (0.334 seconds)

0: jdbc:farrago:> explain plan for select * from "a, b"."c";

'column0'

'FennelToIteratorConverter'

' FtrsIndexScanRel(table=[[LOCALDB, a, b, c]], projection=[*], index=[SYS$CONSTRAINT_INDEX$c$SYS$PRIMARY_KEY], preserveOrder=[false])'

2 rows selected (0.143 seconds)

0: jdbc:farrago:> explain plan for select * from "a"."b, c";

'column0'

'FennelToIteratorConverter'

' FtrsIndexScanRel(table=[[LOCALDB, a, b, c]], projection=[*], index=[SYS$CONSTRAINT_INDEX$b, c$SYS$PRIMARY_KEY], preserveOrder=[false])'

2 rows selected (0.141 seconds)

0