Appendix B1

dinnerattentionΔιαχείριση Δεδομένων

28 Νοε 2012 (πριν από 4 χρόνια και 10 μήνες)

376 εμφανίσεις



B1
-
1








Failure Observed (Bohrbug)




Failure Not Reproduced (Possible Heisenbug)





Fault is Interbase specific, cannot reproduce triggering condition in PostgreSQL




Bug ID

(SF)

Date
reported

Description, Bug Reproduction SQL code and output
in Inte
rbase

Bug Reproduction SQL code and output in
PostgreSQL

Was failure
Observed

In Interbase?

Was failure
Observed

In PosgreSQL

212340

20
-
Aug
-
2000

Token unknown in simple SELECT with GROUP
BY and ORDER BY


CREATE TABLE CUSTOMER (COUNTRY INT);


This simple

SELECT statement does not work


SELECT COUNTRY, COUNT(COUNTRY)

FROM CUSTOMER

GROUP BY COUNTRY

ORDER BY COUNT(COUNTRY)


I'm getting SQL error code =
-
104, Token unknown
count.





CREATE TABLE CUSTOMER(COUNTRY
INTEGER);


SELECT COUNTRY, COUNT(COUNTRY)

FROM

CUSTOMER

GROUP BY COUNTRY

ORDER BY COUNT(COUNTRY);


Country | Count

--------------------


No error.

Yes

No. It works in
PostgreSQL.

214298

12
-
Sep
-
Select count(*) expression anomaly when table is

Yes

No. It works in
A
A
P
P
P
P
E
E
N
N
D
D
I
I
X
X


B
B
1
1
:
:


I
I
N
N
T
T
E
E
R
R
B
B
A
A
S
S
E
E


B
B
U
U
G
G
S
S


I
I
N
N
C
C
L
L
U
U
D
D
E
E
D
D


I
I
N
N


T
T
H
H
E
E


E
E
X
X
P
P
E
E
R
R
I
I
M
M
E
E
N
N
T
T




B1
-
2

2000

empty



SELECT COUNT(*) + 1 RETURNS ZERO WHEN
TABLE
HAS NO ROWS BUT RETURNS CORRECT
RESULT IF COUNT(*) 0.


Sample code:


CREATE TABLE CUSTOMER(COUNTRY INT);


SELECT COUNT(*) + 1 FROM CUSTOMER;


?column?

-----------

0






CREATE TABLE CUSTOMER(COUNTRY
INTEGER);


SELECT COUNT(*) + 1 FROM CUSTOMER;


?column?

-----------

1


Which is the correct result.

PostgreSQL.

216579

11
-
Oct
-
2000

generators in computed by columns will return
wrong results


Using generators in computed by columns will return
wrong results and create an unusable database
.


CREATE GENERATOR GEN1;


CREATE TABLE T0 (A INTEGER, GENID_FIELD
COMPUTED BY (A + GEN_ID(GEN1, 1)));


SHOW TABLE T0;


INSERT INTO T0(A) VALUES(10);

INSERT INTO T0(A) VALUES(12);


SELECT * FROM T0;


a | genid_field


Yes

Not
reproducible in
PostgreSQL.
The gen
-
id()
function and
computed by
not supported
in PostgreSQL.
One solution
could be to
create a gen
-
id() function in
PostgreSQL,
However, due
to time
constraints in
the project this
was not d
one.



B1
-
3

------------------------

10 |

11

12 | 14


216733

12
-
Oct
-
2000

Too Many Generators Can Corrupt Database.


The number of generators you can have is dependant on
(page size
-

unknown overhead) / size of generator. IB
allows you to

create generators past this limit with no complaint, but
these g
enerators will return random data and corrupt the
database if

incremented.

IB seems to limit generators to one page, but no range
checking is done. This is particularly bad on databases
with small page

sizes which migrate from ODS 9 to ODS 10, since the
si
ze of generates doubles from 32 bit to 64 bit, seriously
reducing the limit.

On a 1024 page size, this limit is somewhere less than
128 generators.


Steps:


Create a database of page size 1024. Then:


CREATE GENERATOR GEN1;

CREATE GENERATOR GEN2;………

………….

CREATE GENERATOR GEN129;


then use a generator in a table. The results returned
are incorrect.




Create a database.


Then

CREATE SEQUENCE SEQ1;

CREATE SEQUENCE SEQ2; ……

…….

CREATE SEQUENCE SEQ129;


DO FOR EXAMPLE


SELECT NEXTVAL('SEQ129');


nextval

----
-----

1


which is the correct result.

Yes.

No. It works in
PostgreSQL.

217042(
1)

16
-
Oct
-
2000

IB doesn't validate weird constructions


1) CREATE TABLE T0(CHECK(0=0))


CREATE TABLE T0(CHECK(0=0))


Yes

No.
PostgreSQL
produces an


B1
-
4


How can a table constraint be made without any field?


I thought at least one field shou
ld be defined.



Error: Define relation: Please inherit from a relation
or define an attribute.


Which is the correct behaviour.



error as it
should.

217042(
2)

16
-
Oct
-
2000

IB doesn't validat
e weird constructions


2) CREATE TABLE TGEN2(A COMPUTED BY(0))


CREATE TABLE TGEN3(A COMPUTED BY(0/(0)))


CREATE TABLE TGEN4(A COMPUTED
BY(RDB$DB_KEY))


Do you see any value in accepting this declaration? A
computed field alone? I can never have a row.


3)

CREATE TABLE TGEN5(A INT, B COMPUTED
BY(RDB$DB_KEY))


Insert values in field a and then try to select = string
truncation or arithmetic overflow.



Yes

Not
reproducible in
PostgreSQL.
the computed
by function is
not supported
in PostgreSQL

217042(
3)

16
-
O
ct
-
2000

IB doesn't validate weird constructions


4) CREATE TABLE (A INTEGER DEFAULT ‘ABC’
NOT NULL)


Will be accepted.


This is only a small example. IB never validates the
default for any type of field. I can insert any kind of
value as default for domai
ns of any data type and also
for fields defined inside a table.




CREATE TABLE (A INTEGER DEFAULT ‘ABC’
NOT NULL)


Error: pg_atoi error in ‘abc’; can’t parse ‘abc’


Which is the correct behavior.

Yes

No.
PostgreSQL
displays error,
which is the
correct
be
haviour.

217138

17
-
Oct
-
JOIN including a complex view kills the server


Yes

Not


B1
-
5

2000


The SQL is as follows:


CREATE DOMAIN D_GLOBAL_ID AS
VARCHAR(15) NOT NULL ;

CREATE DOMAIN D_LONG_DESC AS
VARCHAR(200);

CREATE DOMAIN D_GROUP AS INTEGER
DEFAULT 0 CHECK ((value is

not null));


CREATE DOMAIN D_GLOBAL_REF AS
VARCHAR(15);

CREATE DOMAIN D_ICON AS SMALLINT CHECK
(((value is null) or (value

between 0 and 8)));


CREATE TABLE KNOWLEDGESTREAMS (

STREAM_ID D_GLOBAL_ID NOT NULL,

NAME D_LONG_DESC,

CONTENT_GROUPS D_GROUP,

CONST
RAINT PK_KNOWLEDGESTREAMS
PRIMARY KEY (STREAM_ID)

);


CREATE TABLE MAINMENU (

MENU_ID D_GLOBAL_ID NOT NULL,

PARENT_ID D_GLOBAL_REF,

DESCRIPTION D_LONG_DESC,

CONTENT_GROUP D_GROUP NOT NULL,

ICON D_ICON,

CONSTRAINT PK_MAINMENU PRIMARY KEY
(MENU_ID)

);


ALTER

TABLE MAINMENU ADD CONSTRAINT
FK_MAINMENU FOREIGN KEY (PARENT_ID)

REFERENCES MAINMENU(MENU_ID) ON
reproducible in
PostgreSQL.
Domains are
not supported
in PostgreSQL.
PostgreSQL
sup
ports user
defined types in
the form of
Create Type
statement. That
statement
however
requires
creation of two
functions
before it can be
used. Due to
time
constraints the
bug was left as
lower priority,
and did not
have time to get
back to it. Left
as fur
ther work
for future
developers.



B1
-
6

DELETE CASCADE ON UPDATE CASCADE;


CREATE TABLE MENU_GROUPS (

MENU_ID D_GLOBAL_ID NOT NULL,

CONTENT_ID D_GLOBAL_ID NOT NULL

);


CREATE INDEX MENU_GROUPS_IDX1

ON
MENU_GROUPS (MENU_ID);

CREATE INDEX MENU_GROUPS_IDX2 ON
MENU_GROUPS (CONTENT_ID);


CREATE TABLE STREAMMENU (

STREAM_ID D_GLOBAL_ID NOT NULL,

PARENT D_GLOBAL_ID NOT NULL,

CONSTRAINT PK_STREAMMENU PRIMARY KEY
(PARENT, STREAM_ID)

);


ALTER TABLE STREAMMEN
U ADD
CONSTRAINT FK_STREAMMENU_PARENT
FOREIGN KEY

(PARENT) REFERENCES MAINMENU(MENU_ID)
ON DELETE CASCADE;


ALTER TABLE STREAMMENU ADD
CONSTRAINT FK_STREAMMENU_STREAM_ID
FOREIGN

KEY (STREAM_ID) REFERENCES
KNOWLEDGESTREAMS(STREAM_ID) ON
DELETE

CASCADE;


CRE
ATE VIEW FULLMENU (

CODE,

PARENT,

DESCRIPTION,



B1
-
7

LINK,

CONTENT_GROUP

) AS

SELECT
MENU_ID,PARENT_ID,DESCRIPTION,CAST(NULL
AS

VARCHAR(100)),CONTENT_GROUP FROM
MAINMENU

UNION ALL

SELECT M.STREAM_ID, M.PARENT, S.NAME

,CAST('/servlets/uk.co.wmeng.intelus.Knowledg
eStrea
m?ACTION=DISPLAY&ID='

|| S.STREAM_ID AS
VARCHAR(100)),CONTENT_GROUPS FROM
STREAMMENU M JOIN

KNOWLEDGESTREAMS S ON S.STREAM_ID =
M.STREAM_ID

;


SELECT DISTINCT FM.CODE, FM.DESCRIPTION,
FM.LINK

FROM FULLMENU FM JOIN MENU_GROUPS MG
ON FM.CODE = MG.MENU_
ID


When I try and prepare this I get:

"Unable to complete network request to host
10.0.0.200. Error reading data from the connection
and the connection is

lost."


219525

26
-
Oct
-
2000

No current record for fetch operation


CREATE DOMAIN d_currency AS FLOAT;

CREATE DOMAIN d_date AS TIMESTAMP;

CREATE DOMAIN d_des AS VARCHAR(30);

CREATE DOMAIN d_percent AS FLOAT;

CREATE DOMAIN d_arecod
AS SMALLINT;


Yes

Domains are
not supported
in PostgreSQL.

Domain is the
key to this bug,
since if domain
is not used then


B1
-
8

CREATE DOMAIN d_itmcod AS SMALLINT;

CREATE DOMAIN d_colcod AS SMALLINT;

CREATE DOMAIN d_detcod AS SMALLINT;

CREATE DOMAIN d_dsccod AS SMALLINT;

CREATE DOMAIN d_invcod AS INTEGER;


/* Lookup tables */


CREATE TABLE tabare (

arecod d_arecod NOT
NULL,

aredes d_des NOT NULL

);


CREATE TABLE tabcol (

colcod d_colcod NOT NULL,

coldes d_des NOT NULL

);


CREATE TABLE tabdsc (

dsccod d_dsccod NOT NULL,

dscdes d_des NOT NULL,

dscmlt d_percent

);


CREATE TABLE tabitm (

itmcod d_itmcod NOT NULL,

itmdes d_d
es NOT NULL

);


/* Main tables */


CREATE TABLE tabdet (

invcod d_invcod NOT NULL,

detcod d_detcod NOT NULL,

itmcod d_itmcod NOT NULL,

colcod d_colcod ,

detuni SMALLINT NOT NULL,

there is no
erroneous
behaviour in
Interbase
either.

PostgreSQL
supports user
defined types in
the form of
Create Type
statement. That
stateme
nt
however
requires
creation of two
functions
before it can be
used. Due to
time
constraints the
bug was left as
lower priority,
and did not
have time to get
back to it. Left
as further work
for future
developers.



B1
-
9

detppu d_currency NOT NULL,

dsccod d_dsccod NOT NULL

);


CREATE TABLE tabinv
(

invcod d_invcod NOT NULL,

invnum VARCHAR(10) NOT NULL,

invdt d_date NOT NULL,

arecod d_arecod

);


/* Primary keys */


ALTER TABLE tabare ADD CONSTRAINT
tabare_PK PRIMARY KEY (arecod);

ALTER TABLE tabcol ADD CONSTRAINT
tabcol_PK PRIMARY KEY (colcod);

ALTE
R TABLE tabdsc ADD CONSTRAINT
tabdsc_PK PRIMARY KEY (dsccod);

ALTER TABLE tabitm ADD CONSTRAINT
tabitm_PK PRIMARY KEY (itmcod);

ALTER TABLE tabdet ADD CONSTRAINT
tabdet_PK PRIMARY KEY

(invcod,detcod);

ALTER TABLE tabinv ADD CONSTRAINT
tabinv_PK PRIMARY KEY

(invcod);


/* Unique keys */


ALTER TABLE tabare ADD CONSTRAINT
aredes_UK UNIQUE (aredes);

ALTER TABLE tabcol ADD CONSTRAINT
coldes_UK UNIQUE (coldes);

ALTER TABLE tabdsc ADD CONSTRAINT
dscdes_UK UNIQUE (dscdes);

ALTER TABLE tabitm ADD CONSTRAINT
itmdes_U
K UNIQUE (itmdes);



B1
-
10

ALTER TABLE tabdet ADD CONSTRAINT
invcoditmcod_UK UNIQUE

(invcod,itmcod);


/* Foreign keys */


ALTER TABLE tabdet ADD CONSTRAINT
tabdet_tabinv_FK FOREIGN KEY

(invcod) REFERENCES tabinv (invcod);

ALTER TABLE tabdet ADD CONSTRAINT
tabdet_t
abitm_FK FOREIGN KEY

(itmcod) REFERENCES tabitm (itmcod);

ALTER TABLE tabdet ADD CONSTRAINT
tabdet_tabcol_FK FOREIGN KEY

(colcod) REFERENCES tabcol (colcod);

ALTER TABLE tabdet ADD CONSTRAINT
tabdet_tabdsc_FK FOREIGN KEY

(dsccod) REFERENCES tabdsc (dsccod)
;

ALTER TABLE tabinv ADD CONSTRAINT
tabinv_tabare_FK FOREIGN KEY

(arecod) REFERENCES tabare (arecod);


/* Views */


CREATE VIEW view_det (

invcod ,

detcod ,

itmcod ,

colcod ,

detuni ,

detppu ,

dsccod ,

itmdes ,

dscdes ,

coldes )

AS SELECT

a.invcod ,



B1
-
11

a.detc
od ,

a.itmcod ,

a.colcod ,

a.detuni ,

a.detppu ,

a.dsccod ,

b.itmdes ,

c.dscdes ,

d.coldes

FROM tabdet a

LEFT OUTER JOIN tabcol d ON a.colcod=d.colcod

INNER JOIN tabitm b ON a.itmcod=b.itmcod

INNER JOIN tabdsc c ON a.dsccod=c.dsccod

;

CREATE VIEW view_inv
(

invcod ,

invnum ,

invdt ,

arecod ,

aredes )

AS SELECT

a.invcod ,

a.invnum ,

a.invdt ,

a.arecod ,

b.aredes

FROM tabinv a

LEFT OUTER JOIN tabare b ON a.arecod=b.arecod

;


COMMIT;


/* Example Data */


INSERT INTO tabare (arecod,aredes) VALUES
(1,'Area A');

INSERT INTO tabare (arecod,aredes) VALUES


B1
-
12

(2,'Area B');

INSERT INTO tabare (arecod,aredes) VALUES
(3,'Area C');


INSERT INTO tabcol (colcod,coldes) VALUES (1,'Red'
);

INSERT INTO tabcol (colcod,coldes) VALUES
(2,'Green' );

INSERT INTO tabcol (colcod,coldes
) VALUES
(3,'Blue' );

INSERT INTO tabcol (colcod,coldes) VALUES
(4,'Yellow');


INSERT INTO tabdsc (dsccod,dscdes,dscmlt) VALUES
(1,'Discount

A',90.00);

INSERT INTO tabdsc (dsccod,dscdes,dscmlt) VALUES
(2,'Discount

B',80.00);

INSERT INTO tabdsc (dsccod,dscd
es,dscmlt) VALUES
(3,'Discount

C',75.00);


INSERT INTO tabitm (itmcod,itmdes) VALUES
(1,'Pens' );

INSERT INTO tabitm (itmcod,itmdes) VALUES
(2,'Pencils');

INSERT INTO tabitm (itmcod,itmdes) VALUES
(3,'Markers');

INSERT INTO tabitm (itmcod,itmdes) VALUES
(4
,'Erasers');

INSERT INTO tabitm (itmcod,itmdes) VALUES
(5,'Cards' );


INSERT INTO tabinv (invcod,invnum,invdt,arecod)
VALUES (1,'1/A'

,'10
-
SEP
-
2000',2);



B1
-
13

INSERT INTO tabinv (invcod,invnum,invdt,arecod)
VALUES

(2,'20/A','12
-
SEP
-
2000',1);

INSERT INTO tabinv (
invcod,invnum,invdt,arecod)
VALUES (3,'8/G'

,'14
-
SEP
-
2000',2);

INSERT INTO tabinv (invcod,invnum,invdt,arecod)
VALUES

(4,'22/Z','14
-
SEP
-
2000',1);

INSERT INTO tabinv (invcod,invnum,invdt,arecod)
VALUES

(5,'15/H','16
-
SEP
-
2000',3);


INSERT INTO tabdet
(invcod
,detcod,itmcod,colcod,detuni,detppu,dsccod)

VALUES (1,1,2,1, 12, 4.95,1);

INSERT INTO tabdet
(invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)

VALUES (1,2,3,3, 5, 2.90,1);

INSERT INTO tabdet (invcod,detcod,itmcod,
detuni,detppu,dsccod)

VALUES (1,3,4, 2,10
.10,1);

INSERT INTO tabdet
(invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)

VALUES (1,4,5,2, 2, 7.50,1);

INSERT INTO tabdet
(invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)

VALUES (2,1,1,1,100, 5.00,1);

INSERT INTO tabdet (invcod,detcod,itmcod,
detuni,
detppu,dsccod)

VALUES (2,2,4, 50, 2.90,1);

INSERT INTO tabdet (invcod,detcod,itmcod,
detuni,detppu,dsccod)

VALUES (3,1,1, 6, 4.80,1);

INSERT INTO tabdet
(invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)

VALUES (3,2,2,3, 11, 4.95,2);



B1
-
14

INSERT INTO tabdet
(in
vcod,detcod,itmcod,colcod,detuni,detppu,dsccod)

VALUES (3,3,3,2, 10,10.00,1);

INSERT INTO tabdet
(invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)

VALUES (3,4,4,1, 3, 7.40,1);

INSERT INTO tabdet (invcod,detcod,itmcod,
detuni,detppu,dsccod)

VALUES (3,5,5,
5, 3.15,1);

INSERT INTO tabdet
(invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)

VALUES (4,1,1,3, 1, 4.95,1);

INSERT INTO tabdet
(invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)

VALUES (4,2,2,3, 1, 2.95,1);

INSERT INTO tabdet
(invcod,detcod,itmcod,colco
d,detuni,detppu,dsccod)

VALUES (4,3,3,3, 1, 9.95,2);

INSERT INTO tabdet (invcod,detcod,itmcod,
detuni,detppu,dsccod)

VALUES (4,4,4, 1, 7.55,1);

INSERT INTO tabdet
(invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)

VALUES (4,5,5,3, 1, 3.20,1);

INSERT INTO t
abdet
(invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)

VALUES (5,1,2,2, 10, 4.85,1);

INSERT INTO tabdet (invcod,detcod,itmcod,
detuni,detppu,dsccod)

VALUES (5,2,3, 10, 2.95,3);

INSERT INTO tabdet
(invcod,detcod,itmcod,colcod,detuni,detppu,dsccod)

VALUES
(5,3,5,1, 10, 9.90,1);

COMMIT;


SELECT * FROM view_det INNER JOIN view_inv
ON



B1
-
15

view_det.invcod=view_inv.invcod


DOESN'T WORK
-

No current record for fetch
operation.

... and neither will:


SELECT * FROM view_det, view_inv WHERE
view_det.invcod=view_inv.invc
od


221649

04
-
Nov
-
2000

Unique index allowed o
n NULLABLE field


CREATE TABLE MAU(A INT);

COMMIT;

CREATE UNIQUE INDEX IDX_MAU ON MAU(A);

COMMIT;


creates unique index without giving error despite the
fact that attribute a is nullable.




CREATE TABLE MAU(A INT);


CREATE UNIQUE INDEX IDX_MAU ON MAU(
A);


creates unique index without giving error despite the
fact that attribute a is nullable.

Yes

Yes

221921

07
-
Nov
-
2000

ORDER BY has no effect


Take the following example of a self
-
referential table
and a sproc that returns the children of a specifie
d item:


CREATE TABLE EXAMPLETABLE (

CODE INTEGER NOT NULL PRIMARY KEY,

NAME VARCHAR(100) NOT NULL UNIQUE,

PARENT INTEGER,

FOREIGN KEY (PARENT) REFERENCES
EXAMPLETABLE(CODE)

);


/* Children result is not null if this item has it's own
children */

SET TERM
!! ;

CREATE PROCEDURE CHILDRENOFITEM(PAR
INTEGER) RETURNS (CODE INTEGER,CHILDREN

Yes

Not
reproducible in
PostgreSQL.
Left joins not
supported in
PostgreSQL
7.0.



B1
-
16

INTEGER) AS

BEGIN

FOR SELECT

MAINTYPES.CODE, MIN(CHILDTYPES.CODE)

FROM

EXAMPLETABLE MAINTYPES

LEFT JOIN EXAMPLETABLE CHILDTYPES ON
MAINTYPES.CODE = CHILDTYPES.PARENT

WHERE MAI
NTYPES.PARENT = :PAR

OR (MAINTYPES.PARENT IS NULL AND :PAR IS
NULL)

GROUP BY MAINTYPES.CODE

INTO :CODE,:CHILDREN

DO

SUSPEND;

END!!

SET TERM ; !!


INSERT INTO EXAMPLETABLE VALUES
(0,'A',NULL);

INSERT INTO EXAMPLETABLE VALUES
(1,'AA',0);

INSERT INTO EXAMPLET
ABLE VALUES
(3,'AB',0);

INSERT INTO EXAMPLETABLE VALUES
(4,'AC',0);

INSERT INTO EXAMPLETABLE VALUES
(2,'AD',0);

INSERT INTO EXAMPLETABLE VALUES
(5,'B',NULL);

INSERT INTO EXAMPLETABLE VALUES
(6,'BA',5);

INSERT INTO EXAMPLETABLE VALUES
(7,'BB',5);

INSERT INT
O EXAMPLETABLE VALUES
(8,'BC',5);

INSERT INTO EXAMPLETABLE VALUES


B1
-
17

(9,'BD',5);

INSERT INTO EXAMPLETABLE VALUES
(10,'BE',5);

INSERT INTO EXAMPLETABLE VALUES
(11,'BF',5);


SELECT * FROM CHILDRENOFITEM(0);

/* Gives 1,2,3,4 as you would expect */


SELECT * FROM

CHILDRENOFITEM(0) INNER
JOIN EXAMPLETABLE ON
CHILDRENOFITEM.CODE =
EXAMPLETABLE.CODE

ORDER BY NAME;

/* gives 'AA','AD','AB','AC' even though it is order
on name!! Codes are still 1,2,3,4


HOWEVER, in this example, changing it to ORDER
BY NAME DESC correct
ly returns AD,AC,AB,AA


221925

07
-
Nov
-
2000

Left joining table to sproc: ORDER BY makes fields
NULL


CREATE DOMAIN JOBCODETYPE AS
VARCHAR(20);

CREATE DOMAIN STOCKCODETYPE AS
VARCHAR(30);


CREATE TABLE SUBMIT (

JOBID JOBCODETYPE NOT NULL PRIMARY KEY,

COMPLETEBY TIMESTAMP

);


CREATE TABLE FAULTS (

ID STOCKCODETYPE NOT NULL,

WHENREPORTED TIMESTAMP DEFAULT 'NOW'
NOT NULL,


No. I get exactly
the same results
for the two
queries.

Not
reproducible in
PosgreSQL.

Left joins (and
outer joins in
general) are not
supported in
PosgreSQL 7.0.



B1
-
18

WHENDONE TIMESTAMP,

JMSREF JOBCODETYPE NOT NULL,

PRIMARY KE
Y (ID,WHENREPORTED),

FOREIGN KEY (JMSREF) REFERENCES
SUBMIT(JOBID) ON UPDATE CASCADE

);


CREATE VIEW XALLFAULTS(JOBID) AS

SELECT DISTINCT JMSREF FROM FAULTS;

CREATE VIEW XWAITINGFAULTS(JOBID) AS

SELECT DISTINCT JMSREF FROM FAULTS
WHERE WHENDONE IS NULL;


C
REATE VIEW ALLFAULTS(JOBID,WAITING) AS

SELECT
XALLFAULTS.JOBID,XWAITINGFAULTS.JOBID
FROM XALLFAULTS LEFT JOIN
XWAITINGFAULTS ON XALLFAULTS.JOBID =

XWAITINGFAULTS.JOBID;


/* All jobid's with faults, and 'f' or 'F' according to if any
are undone */

SET TERM
!! ;

CREATE PROCEDURE JOBFAULTS RETURNS

(JOBID VARCHAR(20), FAULT VARCHAR(1))

AS

DECLARE VARIABLE WAITING VARCHAR(20);

BEGIN

FOR SELECT

XALLFAULTS.JOBID,XWAITINGFAULTS.JOBID

FROM XALLFAULTS

LEFT JOIN XWAITINGFAULTS ON
XWAITINGFAULTS.JOBID = XALLFAULTS.JOBI
D

INTO :JOBID,:WAITING

DO

BEGIN

IF (WAITING IS NULL) THEN



B1
-
19

FAULT = 'f';

ELSE

FAULT = 'F';

SUSPEND;

end

end!!

SET TERM ; !!


INSERT INTO SUBMIT VALUES ('A',CAST('Now'
AS TIMESTAMP));


INSERT INTO SUBMIT VALUES ('B',CAST('Now'
AS TIMESTAMP));


INSERT INTO
FAU
LTS(ID,WHENREPORTED,JMSREF) VALUES
('ITEM A',CAST('NOW' AS TIMESTAMP),'A');

INSERT INTO
FAULTS(ID,WHENREPORTED,WHENDONE,JMSR
EF) VALUES ('ITEM B',CAST('Now' AS
TIMESTAMP),CAST('NOW' as

timestamp),'A');


/* NOW TRY THE FOLLOWING */

SELECT * FROM SUBMIT JOIN
JOBFAULTS

ON SUBMIT.JOBID = JOBFAULTS.JOBID

ORDER BY SUBMIT.COMPLETEBY;

/* Here there appear to be no faults joined to the Submit
table */


SELECT * FROM SUBMIT JOIN JOBFAULTS

ON SUBMIT.JOBID = JOBFAULTS.JOBID

/* Yet here the fault is shown correctly joine
d!! */


222375

13
-
Nov
-
2000

Grants overwrite previous rdb$security_classes
entries



CREATE TABLE abcdefghijklmnopqrstuvwxyzabcde(a
int)

Yes

No. It works in
PostgreSQL.
PostgreSQL


B1
-
20

First,
let me say that a clarification is need. If Firebird
can rely solely on rdb$user_privileges, then this report is
not critical.

Otherwise, the results of records being overwritten
silently on rdb$security_classes is an issue. The
following is an almost uned
ited copy from an article I
wrote on the NG:


-

Create a table with 31
-
byte name.

-

Create a second table with the same name, except that
it differs only in the last, 31th character.

-

Grant permissions to userA on the first table and to
userB on the secon
d table.

-

Go to see rdb$user_privileges and you'll see your
entries as they should be.

-

Go to rdb$security_classes: with rdb$ as a prefix, 31
-
4=27,


Here's your 27
-
byte limitation: what happened to this

table?
Both entries were mapped to the same record
,
same security class! As a result of that, the second
GRANT overwrote the information for the first one,
because rdb$security_classes.rdb$security_class used
sql$table_name hence the last 4 bytes were ignored.

Feature? Undocumented limitation? Bug?

So, wh
at LangRef says could be refurbished as:

Some objects, such as security class names, are
restricted in practice to 27 bytes in length.



CREATE TABLE abcdefghijklmnopqrstuvwxyzabcdf(a
int)


GRANT ALL ON
abcdefghijklmnopqrstuvwxyzabcde
TO ilir


GRANT ALL ON abcdefghijklmnopqrstuvwxyzabcdf
TO david


\
z
command in psql displays both tables with their
corresponding user and privileges.

does not have a
security_clas
s
table, which is
why there are
no problems.

222376

13
-
Nov
-
2000


Horrible plan with a lot of OR conditions

Consider:


select distinct doc.id docID, doc.directory directoryID

from documents doc, DOC_APPLICATION docAppl

where (docAppl.DOC_ID=doc.ID) and

(d
ocAppl.SITE_ID=2 or docAppl.SITE_ID=7 or




EXPLAIN

SELECT * FROM PG_CLASS

WHERE RELNAME LIKE '%a%'

OR RELNAME LIKE '%b%'

OR RELNAME LIKE '%c%'

Yes

Not sure. The
plan itself could
not be seen in
PosgreSQL so,
difficult to
determine
whether there
is an


B1
-
21

docAppl.SITE_ID=25 or ....)


the corresponding plan is


PLAN SORT (JOIN (DOC NATURAL,DOCAPPL
INDEX

(RDB$FOREIGN122,RDB$FOREIGN123,RDB$FORE
IGN123,RDB$FOREIGN123,RDB$FOREIGN123...)))


And an RDB$FOREIGN123 is added fo
r each
additionnal or docAppl.SITE_ID= in the query. In one
particular circumstance the query gets particularly long
and the corresponding plan as well... In that case I'm
getting the unknown error in ISQL

(the records are fetched and presented in the dbgr
id,
statistics are here, but plan is empty).


And yes, IB does a very poor job at generating the plan
in this particular case, the optimal plan is just


PLAN SORT (JOIN (DOC NATURAL,DOCAPPL
INDEX (RDB$FOREIGN122,RDB$FOREIGN123))


which speeds the request q
uite a lot...


Problem can also be reproduced using system tables as
follows:


SELECT * FROM RDB$INDICES

WHERE RDB$INDEX_NAME = 'F'

OR RDB$INDEX_NAME = 'a'

OR RDB$INDEX_NAME = 'b'

OR RDB$INDEX_NAME = 'c'

OR RDB$INDEX_NAME = 'd'

OR RDB$INDEX_NAME = 'e'

OR R
DB$INDEX_NAME = 'f'

OR RDB$INDEX_NAME = 'g'

OR RELNAME LIKE '%d%'

OR RELNAME LIKE '%e%'

OR RELNAME LIKE '%f%'

OR RELNAME LIKE '%g%'

OR RELNAME LIKE '%h%'

OR RELNAME LIKE '%i%'

OR RELNAME LIKE '%j%'

OR RELNAM
E LIKE '%k%'

OR RELNAME LIKE '%l%'

OR RELNAME LIKE '%m%'

OR RELNAME LIKE '%n%'

OR RELNAME LIKE '%o%'

OR RELNAME LIKE '%p%'

OR RELNAME LIKE '%q%'

OR RELNAME LIKE '%r%'

OR RELNAME LIKE '%s%'

OR RELNAME LIKE '%t%'

OR RELNAME LIKE '%u%'

OR RELNAME LIKE '%v%'

O
R RELNAME LIKE '%w%'

OR RELNAME LIKE '%x%'

OR RELNAME LIKE '%y%'

OR RELNAME LIKE '%z%';


SEQ SCAN ON PG_CLASS (COST=0.00..5.88
ROWS=14 WIDTH=85)


Retrieves all the relations. Could not use pg_index
system table since that does not have the relname
attribut
e.

Difficult to interpret these results since the query
plan of PostgreSQL is different from that of
Interbase.

improvement in
PostgreSQL or
not.
Further
work and
analyses
required.



B1
-
22

OR RDB$INDEX_NAME = 'h'

OR RDB$INDEX_NAME = 'i'

OR RDB$INDEX_NAME = 'j'

OR RDB$INDEX_NAME = 'k'

OR RDB$INDEX_NAME = 'l'

OR RDB$INDEX_NAME = 'm'

OR RDB$INDEX_NAME = 'n'

OR RDB$INDEX_NAME = 'o'

OR RDB$INDEX_NAME =
'p'

OR RDB$INDEX_NAME = 'q'

OR RDB$INDEX_NAME = 'r'

OR RDB$INDEX_NAME = 's'

OR RDB$INDEX_NAME = 't'

OR RDB$INDEX_NAME = 'u'

OR RDB$INDEX_NAME = 'v'

OR RDB$INDEX_NAME = 'w'

OR RDB$INDEX_NAME = 'x'

OR RDB$INDEX_NAME = 'y'

OR RDB$INDEX_NAME = 'z'


222476

14
-
Nov
-
2000

Avg and sum return empty field names in dialect 3


SELECT AVG(1), SUM(1) FROM RDB$DATABASE


returns:


f1_ | f2_

-----------

1 | 5


If we go to dialect 1, things work as expected: the first
fiel
d is named AVG and the second, SUM.





SELECT AVG(1), SUM(1) FROM PG_DATABASE


returns


avg | sum

-------------

1 | 5


which is the correct result.

Yes

No. It works in
PostgreSQL.

223059

21
-
Nov
-
2000

Updating VARCHAR does not clear old data


Whe
n IB updates VARCHAR string, it does not zero
rest of the string. (when the row is decompressed in the


CREATE TABLE t ( a VARCHAR(50) );


Yes.

No. It works
‘normally’ in
PostgreSQL
(however needs


B1
-
23

buffer).

e.g. you have table


CREATE TABLE t ( a VARCHAR(50) );

INSERT INTO t

VALUES
('abcdefghijklmnopqrstuvwxyz1234567890abcdefghijkl
mn');

COMMIT;


Whe
n you update it by

UPDATE t SET a='XYZ';

then IB creates new version of the row that should
contain string:

'XYZ' + zero filled rest but it stores (in gdb file) this
string instead:

'XYZdefghijklmnopqrstuvwxyz1234567890abcdefghijk
lmn'


Because VARCHARs con
tain length of the string, client
application will never notice any problem (i.e. it will
always get correct result), but the gdb file can grow
faster than expected (because such additional data can't
be re compressed), and database can

get slower (because

less useful data fit onto page).


INSERT INTO t

VALUES
('abcdefghijklmnopqrstuvwxyz1234567890abcdefghijkl
mn');


UPDATE t SET a='XYZ';



After several runs, the database file was growing
‘normally’ not faster. It needs
to be said that it is
difficult to know what to expect here, so it is unclear
whether there is a gain in PostgreSQL or not.

further
investigation).

223128

21
-
Nov
-
2000

SYSDBA can grant non existent

roles


IB doesn't check the user in a GRANT statement
probably because the db can be moved to another server
where such user is defined, since the information is
stored in isc4.gdb only.

B

ut why would IB allow SYSDBA to grant no existent
roles to users?

For example, this is accepted:


GRANT anything to ALICE



Login to database as

user pgsql


GRANT anything TO ilir


Error: parser: parse error at or near ‘anything’

Yes

No.
PostgreSQL
gives an error,
as it should.



B1
-
24


However, anything doesn't exist in rdb$roles but
rdb$user_privileges logs a role granted to alice.

In contrast, a non
-
privileged user can't grant a role that
doesn't exists.


223133

21
-
Nov
-
2000

Ambiguous self join produce bizarre results


Doing a self join without assigning alias to both
occur
rences of the table can be a fatal trap for newbies. I
will use a contrives, useless example:


SELECT RDB$RELATION_NAME FROM
RDB$RELATIONS R

JOIN RDB$RELATIONS

ON RDB$RELATION_ID = R.RDB$RELATION_ID

ORDER BY 1


= produces a listing of tables, ordered as ex
pected. The
first table in the sample db is called A.


Observe that only one occurrence of rdb$relations is
qualified with an alias.


Now, by simply assigning the alias to the second

occurrence instead of the first one, we force the server to
yield incredi
ble results:


SELECT RDB$RELATION_NAME FROM
RDB$RELATIONS

JOIN RDB$RELATIONS R

ON RDB$RELATION_ID = R.RDB$RELATION_ID

ORDER BY 1


= produces

A

A

A



SELECT RELNAME FROM PG_CLASS

JOIN PG_CLASS C

ON RELNAME = C.RELNAME

ORDER BY 1


Gives error: Column ‘re
lname’ is ambiguous.


Note: pg_class does not have a relation_id field,
however the relname field is the unique identifier for
a relation, and that’s why it was used in the ‘on’
clause above

Yes

No.
PostgreSQL
gives error
stating that the
‘relname’
column
is
ambiguous.



B1
-
25




223512(
1)

26
-
Nov
-
2000

DROP TABLE shouldn't drop a view.


Use CONNECT or CREATE DATABASE to specify a
database

SQL CREATE DATABASE 'Z0.FDB';

SQL CREATE TABLE T(A INT);

SQL CREATE VIEW V AS SELECT A FROM T;

/* Incorrect behavior, according to

SQL standards.

A view cannot be dropped as a table */

SQL DROP TABLE V;


However view v is dropped.



CREATE TABLE T(A INT4);

CREATE VIEW V AS SELECT A FROM T;


/* PostgreSQL incorrevtly drops a view as a table */



drop table v;



Yes

Yes.
PostgreSQL
als
o drops a
table, when it is
in fact a view,
which is
incorrect
behaviour.

223512(
2)

26
-
Nov
-
2000

DROP VIEW shouldn't drop a table.


Use CONNECT or CREATE DATABASE to specify a
database

SQL CREATE DATABASE 'Z0.FDB';

SQL CREATE TABLE T(A INT);

SQL CREATE VIE
W V AS SELECT A FROM T;

/* Incorrect behavior, according to SQL standards.

A table cannot be dropped as a view */


SQL DROP VIEW T;


However here the table t is dropped.




CREATE TABLE T(A INT4);

CREATE VIEW V AS SELECT A FROM T;



/* This gives error, as

it should */


DROP VIEW T;


Error: Rule or view t not found.

Yes

No. The drop
view, when
view is a table
produces an
error as
expected.



223514

26
-
Nov
-
2000

IB Crashes with two procedures Intermixed


Note: Need to download and install the example
databa
se which is attached as part of this bug report in
the sourceforge website.


Yes

Not reproduced
in PostgreSQL.
Due to time
constraints for
the project, this


B1
-
26


These two procedures expect an order and an order
-
line
number.
The following will make IB crash:


// Show al possible parties on stock, with the same
characteristics

SELECT * FRO
M GET_ORDVRD(258,1)

// Show the status of the stock (matched, alternatives
etc)

SELECT * FROM INKORDVRDSTATUS(258,1)

// First one

SELECT * FROM GET_ORDVRD(258,1)

bug was not
r
eproduced
since it
required a
detailed schema
translation of
the database.
Left as possible
further work
for future
developers.



223793

29
-
Nov
-
2000

isc_add_user() allows adding 32
-
char usernames


In the file interbase
\
jrd
\
alt.c, function isc_add_user()

(and also isc_modify_user, isc_delete_user)

there is line

if (strlen (user_data
-
user_name) 32)


so this function allows adding usernames 32
characters long!


CREATE USER abcdefghijklmnopqrstuvwxyzabcdef;


The following message is displayed:


Notice: ident
ifier
‘abcdefghijklmnopqrstuvwxyzabcdef’ will be
truncated to ‘abcdefghijklmnopqrstuvwxyzabcde’


Therefore PostgreSQL does not allow usernames of
32 characters long to be created.


Yes

No.
PostgreSQL
does not allow
usernames of
32 characters
to be created.

224810

06
-
Dec
-
2000

DISTINCT propagates outside a VIEW


Given these two views:


CREATE VIEW VDISTIDX
(RDB$RELATION_NAME) AS

SELECT DISTINCT RDB$RELATION_NAME
FROM RDB$INDICES


CREATE VIEW VDISTIDX2
(RDB$RELATION_NAME) AS

SELECT RDB$RELATION_NAME FROM
RDB
$INDICES


Yes

Not
reproducible in
PostgreSQL.
Distinct is not
supported in
Views in
Pos
tgreSQL.



B1
-
27

GROUP BY RDB$RELATION_NAME


Then


SELECT COUNT(*) FROM VDISTIDX V

JOIN RDB$RELATION_FIELDS RF

ON V.RDB$RELATION_NAME =
RF.RDB$RELATION_NAME


with the scheduler saying


PLAN SORT (JOIN (V RDB$INDICES NATURAL,RF
INDEX (RDB$INDEX_4)))


produces a dif
ferent result than


SELECT COUNT(*) FROM VDISTIDX2 V

JOIN RDB$RELATION_FIELDS RF

ON V.RDB$RELATION_NAME =
RF.RDB$RELATION_NAME


with the scheduler saying


PLAN MERGE (SORT (RF NATURAL),SORT (SORT
(V RDB$INDICES NATURAL)))


Notice both JOIN statements produ
ce different query
plans but both views alone produce the same plan. I
think that
the first case is a bug: the DISTINCT
clause is propagating outside the VIEW;

just change
the fist case to become


SELECT RDB$RELATION_NAME

FROM VDISTIDX V JOIN
RDB$RELATION_
FIELDS RF

ON V.RDB$RELATION_NAME =
RF.RDB$RELATION_NAME



B1
-
28


and you'll observe that effectively, the DISTINCT
applied to the result of the JOIN, leaving each table
name only once.


225283

10
-
Dec
-
2000

ORDER BY on a VIEW turns values in fields into
NULL


I used a different example from the sourceforge website,
which was part of the same bugreport but easier to
reproduce.


CREATE TABLE P1 (X_P1
NUMERIC(10,0),F_ENTRADA DATE);


CREATE VIEW VP1 (X_P1,F_ENTRADA) AS
SELECT X_P1, F_ENTRADA FROM P1;


CREATE TABLE P2 (X_P2
NUMERIC(10,0),P1_X_P1 NUMERIC(10,0), N_ONE
NUMERIC(10,0),N_TWO NUMERIC(10,0));


CREATE VIEW VP2 (P1_X_P1,N_ONE,N_TWO) AS
SELECT P1_X_P1, SUM(N_ONE), SUM(N_TWO)
FRO
M P2 GROUP BY P1_X_P1;


CREATE VIEW VVP1 (P1_X_P1, F_ENTRADA,
N_ONE,N_TWO) AS

SELECT P1.X_P1, P1.F_ENTRADA, P2.N_ONE,
P2.N_TWO

FROM VP1 P1 LEFT JOIN VP2 P2 ON
P1.X_P1=P2.P1_X_P1;




/* INSERT */

INSERT INTO P1 VALUES (1,'07/10/2001');


Yes

Not
reproducib
le in
PostgreSQL.
Since
PostgreSQL
does not
support outer
joins and also
does not
support union
(which
simulates outer
joins) in views.
If an inner join
is used in the
view vvp1 the
query works in
both servers.



B1
-
29

INSERT INTO P1 VALUES

(2,'07/13/2001');

INSERT INTO P1 VALUES (3,'08/12/2001');

INSERT INTO P2 VALUES (1,1,0,1);

INSERT INTO P2 VALUES (2,2,1,0);

INSERT INTO P2 VALUES (3,1,0,1);


SELECT * FROM VVP1 ORDER BY F_ENTRADA;


Returns null values for all fields.


227375

02
-
Jan
-
2001

Grouping on derived fi
elds processing NULL data
kills IB


The database has the following table:


CREATE TABLE TWODATE(

TWODATEID INTEGER NOT NULL,

DATEBEGIN DATE,

DATEEND DATE,

CONSTRAINT PK_TWODATE PRIMARY KEY
(TWODATEID));


INSERT INTO TWODATE VALUES (22, null,null);


The
table contains several records. Some of the values in
the two date fields are null.

Furthermore the database has the

following view:

CREATE VIEW CALCDIFF (TWODATEID,
DIFFYEAR) AS

SELECT TWODATEID, EXTRACT(YEAR FROM
DATEBEGIN)
-

EXTRACT(YEAR FROM
DATEEND)

FROM TWODATE;


The following Select statement causes a lost
connection to database
-

error:





CREATE TABLE TWODATE(

TWODATEID INTEGER NOT NULL,

DATEBEGIN DATE,

DATEEND DATE,

CONSTRAINT PK_TWODATE PRIMARY KEY
(TWODATEID));


INSERT INTO TWODATE VALUES (22, null,null);


CREATE VIEW

CALCDIFF (TWODATEID,
DIFFYEAR) AS

SELECT TWODATEID, EXTRACT(YEAR FROM
DATEBEGIN)
-

EXTRACT(YEAR FROM
DATEEND)

FROM TWODATE;


SELECT DiffYear, count(*) FROM CalcDiff Group by
DiffYear;


Results:


Diffyear | count

----------------------

Yes

No. It works in
PostgreSQL.



B1
-
30


SELECT DiffYear, count(*) FROM CalcDiff Group
by DiffYear;


The error occures only, when

-

there are null values in the database

-

there is a subtraction between t
he two extract
statements

-

the select has a group by statement



| 1


Which is the correct behaviour.

227758

06
-
Jan
-
2001

Field names with spaces cannot be used in VIEWS


The following DDL exemplifies more completely the
problem I've been encountering with accessing views.


CREATE DOM
AIN IDINTEGER AS INTEGER NOT
NULL;

CREATE DOMAIN IDVARCHAR AS
VARCHAR(31) NOT NULL;


CREATE TABLE COMPANY LIST (

COMPANY NAME IDVARCHAR NOT NULL
PRIMARY KEY,

COMPANY ID IDINTEGER NOT NULL UNIQUE );


CREATE TABLE Vendor List (

Company ID IDINTEGER NOT NULL
PRIMARY
KEY,

Days to Quote Expiration SMALLINT );


/* The following view is inaccessible.*/

CREATE VIEW VENDOR NAME LIST (COMPANY
ID, COMPANY NAME)

AS SELECT COMPANY ID, COMPANY NAME
FROM COMPANY LIST CL

WHERE EXISTS (SELECT * FROM VENDOR LIST
VL




CREATE TABLE Company List (

Company Name varcha
r(31) PRIMARY KEY,

Company ID integer NOT NULL UNIQUE );


ERROR: parser: parse error at or near "list"


CREATE TABLE VENDOR LIST (

COMPANY ID INTEGER PRIMARY KEY,

DAYS TO QUOTE EXPIRATION SMALLINT );


ERROR: parser: parse error at or near "list"



/* The

following view is inaccessible.*/

CREATE VIEW VENDOR NAME LIST (COMPANY
ID, COMPANY NAME)

AS SELECT COMPANY ID, COMPANY NAME
FROM COMPANY LIST CL

WHERE EXISTS (SELECT * FROM VENDOR LIST
VL

WHERE VL.COMPANY ID = CL.COMPANY ID);


ERROR: parser: parse error

at or near "name"


No. It gives
parsing error at
each stage.

No. It gives
parsing error
at each stage.



B1
-
31

WHERE VL.
COMPANY ID = CL.COMPANY ID);


The message I got is:

Cannot access column Company Name in view
Vendor Name List

Statement: select * from Vendor Name List


And I verified that IB indeed stored the definition as it
appears above, so this is not a problem of I
BConsole or
IB_WISQL. This is a bug in IB: views based on tables
whose fields carry spaces in dialect 3 don't work
because they can't access those table's fields.


/* However, the equivalent DDL with the spaces
removed allows access to the

view. */

CREATE
TABLE COMPANYLIST (

COMPANYNAME IDVARCHAR NOT NULL
PRIMARY KEY,

COMPANYID IDINTEGER NOT NULL UNIQUE );

CREATE TABLE VENDORLIST (

COMPANYID IDINTEGER NOT NULL PRIMARY
KEY,

DAYSTOQUOTEEXPIRATION SMALLINT );


227760

06
-
Jan
-
2001

Zero
-
length db object names shouldn't be allowed


Zero
-
length field names aren't valid and should be
banned.
Currently, IB in dia
lect 3 allows
constructions like these:


CREATE TABLE ( INT);

COMMIT;

CREATE INDEX ON ();


SET TERM ^;

CREATE PROCEDURE P RETURNS( INT)




CREATE TABLE ( INT);


CREATE INDEX ON ();


Both theses statements give parsing errors:


Error: Pa
rsing: parse error at or near ‘(‘



No. It was
giving a parsing
error when
trying to execute
each of the
statements.

No. It was
giving a
parsing error
in PostgreSQL



B1
-
32

AS BEGIN

FOR SELECT FROM INTO : DO

BEGIN

= : * :;

SUSPEND;

END

END ^

SET TERM ^;

COMMIT;

CREATE ROLE ;

GRANT SELECT ON T
O ;

GRANT EXECUTE ON PROCEDURE P TO ;

INSERT INTO VALUES(1);

INSERT INTO VALUES(2);

INSERT INTO VALUES(3);

COMMIT;


Then,

SELECT * FROM P

works perfectly.


228030

08
-
Jan
-
2001

Float problems.


I found some problems with float type decimal part.

For example I write to field: FLOAT 0.35 physically
it writes;


0.349999994039536.


Sample code:


CREATE TABLE FLOAT_T (F FLOAT);


INSERT INTO FLOAT_T VALUES(0.35);


SELECT * FROM FLOAT_T;




CREATE TABLE FLOAT_T (F
FLOAT);


INSERT INTO FLOAT_T VALUES(0.35);


SELECT * FROM FLOAT_T;



f

----

0.35


Which is the correct behaviour.

Yes

No. It works in
PostgreSQL



B1
-
33

f

-----------------

0.349999994039536


228135

08
-
Jan
-
2001

NULL is returned as zero through a left join in
simple VIEW


This is a simpler example wh
ich was taken from the
same bug report in the firebird sourceforge bug
database.


CREATE TABLE T6(A INT);


INSERT INTO T6 VALUES(1);


CREATE DOMAIN DOM_U INT NOT NULL;


CREATE TABLE U(B DOM_U);


CREATE VIEW V AS SELECT T6.A, U.B FROM T6
LEFT JOIN U ON T6.A

= U.B;

COMMIT;


SELECT * FROM V;


A | B

-----------

1 | 0


Which is an error. B should be <null> not 0.



Yes

Not reproduced
in PostgreSQL.
Domains are
not supported
in PostgreSQL.
Domain is the
key to this bug,
since if domain
is not used th
en
there is no
erroneous
behaviour in
Interbase
either.

PostgreSQL
supports user
defined types in
the form of
Create Type
statement. That
statement
however
requires
creation of two
functions
before it can be
used. Due to
time
constraints the
bug was left
as
lower priority,
and did not
have time to get


B1
-
34

back to it. Left
as further work
for future
developers.


228526

12
-
Jan
-
2001

ambiguous statements return unpredictable results


I noticed that IB happily executes an ambiguous query
of the form:

select *

FROM

ORDERS O

LEFT JOIN CUSTOMERS C1 ON
(O.CUSTOMER_ID1 = C1.ID)

LEFT JOIN CUSTOMERS C2 ON
(O.CUSTOMER_ID2 = C2.ID)

WHERE SOMEFIELD = 'SOMEVALUE'


or


SELECT *

FROM ORDERS O, CUSTOMERS C1, CUSTOMERS
C2

WHERE O.CUSTOMER_ID = C1.CUSTOMER_ID
OR O.CUSTOMER_ID = C2
.CUSTOMER_ID AND
SOMEFIELD = 'SOMEVALUE'



Assuming somefield exists in both the customers and
the orders table, the query is ambiguous unless the
reference in the where clause is qualified.


Schema definitions:


CREATE TABLE CUSTOMERS(

CUSTOMER_ID INT NO
T NULL PRIMARY KEY,

SOMEFIELD VARCHAR(20));




CREATE TABLE CUSTOMERS(

CUSTOMER_ID INT NOT NULL PRIMARY KEY,

SOMEFIELD VARCHAR(20));


CREATE TABLE ORDERS (

CUSTOMER_ID INT NOT NU
LL,

SOMEFIELD VARCHAR(20));


SELECT *

FROM ORDERS O, CUSTOMERS C1, CUSTOMERS
C2

WHERE O.CUSTOMER_ID = C1.CUSTOMER_ID
OR O.CUSTOMER_ID = C2.CUSTOMER_ID AND
SOMEFIELD = 'SOMEVALUE'


Error: Column ‘somefield’ is ambiguous
.

Yes

No.
PostgreSQL 7.0
does not
sup
port outer
joins, however
when executing
this similar
statement
(which was not
in the original
bug report, but
that Interbase
also
erroneously
executes
without giving
an error) an
error is
returned about
the unqualified
attribute
somefield,
which is the
co
rrect
behaviour.



B1
-
35

CREATE TABLE ORDERS (

CUSTOMER_ID INT NOT NULL,

SOMEFIELD VARCHAR(20));



228716

13
-
Jan
-
2001

Tbl left join sproc + order by = no records returned


Note: need employee.gdb database which comes with
Interbase installation.


Create the following procedure with the employee.gdb
that comes with IB6, still in diale
ct 1:


SET TERM ^;

CREATE PROCEDURE
DEL_OLD_SALARY_HISTORY

AS

DECLARE VARIABLE DBK CHAR(8);

DECLARE VARIABLE DBK_PREV CHAR(8);

DECLARE VARIABLE EMP_NO SMALLINT;

DECLARE VARIABLE EMP_NO_PREV
SMALLINT;

BEGIN

EMP_NO_PREV = NULL;

FOR SELECT SH.RDB$DB_KEY, EMP_
NO

FROM SALARY_HISTORY SH

ORDER BY EMP_NO, CHANGE_DATE

INTO :DBK, :EMP_NO

DO BEGIN

IF (EMP_NO = EMP_NO_PREV)

THEN DELETE FROM SALARY_HISTORY WHERE
RDB$DB_KEY = :DBK_PREV;

EMP_NO_PREV = EMP_NO;

DBK_PREV = DBK;

END

END ^

SET TERM ;^


Yes

Not reproduced
in PostgreSQL.
Due to time
constraints on
the project, this
bug was not
reproduced in
PostgreSQL
since a
translation of
the schema of
the
employee.gdb
database was
needed. Left as
further work
for future
deve
lopers.




B1
-
36


But for testing that wit
hout deleting records, I decided to
show which one I would delete, so I converted the
procedure;

only the records that are shown are deleted in the real
case:


SET TERM ^;

CREATE PROCEDURE
SHOW_DEL_OLD_SALARY_HISTORY

RETURNS (OUT SMALLINT, OUT2 TIMESTAMP)

AS

DECLARE VARIABLE DBK CHAR(8);

DECLARE VARIABLE DBK_PREV CHAR(8);

DECLARE VARIABLE EMP_NO SMALLINT;

DECLARE VARIABLE EMP_NO_PREV
SMALLINT;

DECLARE VARIABLE CHANGE_DATE
TIMESTAMP;

DECLARE VARIABLE CHANGE_DATE_PREV
TIMESTAMP;

BEGIN

EMP_NO_PREV = NULL;

FOR
SELECT SH.RDB$DB_KEY, EMP_NO,
CHANGE_DATE

FROM SALARY_HISTORY SH

ORDER BY EMP_NO, CHANGE_DATE

INTO :DBK, :EMP_NO, :CHANGE_DATE

DO BEGIN

OUT = EMP_NO_PREV;

OUT2 = CHANGE_DATE_PREV;

IF (EMP_NO = EMP_NO_PREV)

THEN /* DELETE FROM SALARY_HISTORY
WHERE RDB$DB_KE
Y = :DBK_PREV */

SUSPEND;

DBK_PREV = DBK;

EMP_NO_PREV = EMP_NO;



B1
-
37

CHANGE_DATE_PREV = CHANGE_DATE;

END

END ^

SET TERM ;^


Then, since I didn't want to do a tedious comparation by
hand between the affected table and the procedure, this
sentence should compare
records in the table with the
ones that the real procedure would delete: the ones with
NULL in the proc side aren't deleted:


SELECT S.EMP_NO, S.CHANGE_DATE, P.OUT,
P.OUT2

FROM SALARY_HISTORY S LEFT JOIN
SHOW_DEL_OLD_SALARY_HISTORY P

ON S.EMP_NO = P.OUT AN
D S.CHANGE_DATE =
P.OUT2


It works, but since the mandatory table is not ordered, it
makes very difficult to tell if the shown records are the
correct. Hence, I added a sorting clause to the above
LEFT JOIN and...


***

-

order by 1 = plan but no data (no r
ecords)

-

order by 1,2 = plan but no data (no records)

-

order by 2 = plan but «Conversion error from
string 1»

-

order by 3,4 = plan but «Conversion error from
string 1»

-

group by s.emp_no, s.change_date, p.out, p.out2 =
plan but no data (no records)

-

g
roup by p.out, p.out2, s.emp_no, s.change_date =
plan but «Conversion error from string 1»


***




B1
-
38

Changing LEFT JOIN to JOIN displays data even with
an ORDER BY, but that's not useful for solving the need
I had to

check my original procedure's actions.


***

Trying to narrow down the conversion error problem, I
used only a sort on the procedure:


SELECT P.OUT, P.OUT2

FROM SHOW_DEL_OLD_SALARY_HISTORY P

ORDER BY 1,2

= no problem


SELECT S.EMP_NO

FROM SALARY_HISTORY S LEFT JOIN
SHOW_DEL_OLD_SALARY_HISTORY P

ON S
.EMP_NO = P.OUT

ORDER BY 1

= no records


SELECT S.EMP_NO

FROM SALARY_HISTORY S LEFT JOIN
SHOW_DEL_OLD_SALARY_HISTORY P

ON S.EMP_NO = P.OUT

ORDER BY P.OUT

= «Conversion error from string 1» and since I'm not
showing the two timestamp fields, this is a probl
em
with the sort

routine.


229231

18
-
Jan
-
2001

revoke is case sensitive


If I create a user called ilir and then grant all privileges
on a table to them, the revoke command doesn't work
unless I use the




create user ILIR


grant all on tablename to ilir

revoke all from tablename from ilir.

No. It worked
when I tried

it.

In lowercase or
uppercase the
privileges were
revoked.
No. It works in
PostgreSQL.



B1
-
39

ilir name in upper case.


Here is an example:

grant all on config to

ilir;

revoke all on config from ilir;

You would think this would result in ilir having no
privilege on config but a select on the
RDB$USER_PRIVILEGE table shows the

ilir user still has full access! You need to do:

revoke all on config from ILIR

in order t
o remove the privileges!



\
z

command from the psql terminal shows that all
the privileges from tablename have been revoked
from user ilir.



Possible Heisen
bug.

229237

18
-
Jan
-
2001

Blank passwords poorly supported


The tools like isql poorly support having a user with a
blank password. You are allowed to
create one but then
you can't

ever connect using isql if you create the user that way.
The obvious option of trying
-
p for the password doesn't
work

and is thrown out by the argument parser. Not supplying
a password also doesn't work.


The isql tool should

allow
-
p for the password and try to
connect using a blank password in that case.


Recreation steps:


CREATE USER ‘ilir’ PASSWORD ‘ ‘

Created a user with a password consisting of three
empty spaces:


Trying to connect as user ilir and password as three
empty spaces gives: Error incorrect password



CREATE USER ILIR WITH PASSWORD ‘ ‘

Created a user with password consisting of three empty
spaces


\
connect testdb ilir


psql asks for password. Press space three times and
access will be granted to tetsdb.

Yes. And it is
not a problem
with just isql.
IBConsole also
has the
problem, which
is probably the
reason why this
bug was
reported as a
core engine /
security bug
rather then a
isql bug.

No. It works in
PostgreSQL.



B1
-
40

231998

12
-
Feb
-
2001

space before CASTed

numeric expression in dialect 1


Andrew Velikoredchanin found interesting behavior in
dialect 1, when numeric expression is being casted to a
varchar.

For example:

SELECT CAST(22 / 7 AS VARCHAR(20)) FROM
RDB$DATABASE

will give


' 3.142857142857143'

as a
result (note that there is a space before first
digit).


If not an expression, but numeric value is casted, all is
ok, i.e. no space before casted result.

Dialect 3 does not have this problem.

no difference between integer and numeric values
-

22.0
/ 7.0 w
ill give space before first digit.



SELECT CAST (22/7 AS VARCHAR(20))


gives:


?column?

-----------

3


SELECT CAST (22.0 /7 AS VARCHAR(20))


gives:


?column?

-----------

3.142857142857143


It seems that PostgreSQL ignores the decimal points
altogether whe
n converting to varchar, if the two
numbers used in the division are integers. This is the
way the PostgreSQL is implemented, therefore it is
not a bug. However there is no space before the first
digit, and it will give the correct result if one of the
num
bers used in the division is a real number.


Yes

No.
PostgreSQL
does not have a
space before
the first digit
and it gives the
correct result if
at least one of
the numbers in
the division is a
real number.

233124

19
-
Feb
-
2001

Connection lost during the ba
d SQL code execution


If I try to alter domain with a bit wrong syntax like:


ALTER DOMAIN DOMAIN_NAME SET TYPE
SMALLINT;


where 'set' is illegal, the connection between server and
client is lost with the following well
-
known error:

Unable to complete net
work request to host
host_name.

Error writing data to the connection.


Yes

Not
reproducible in
PostgreSQL.
Alter domain
(or Alter Type)
not supported
in PostgreSQL



B1
-
41

unknown Win32 error 10054


233304

20
-
Feb
-
2001

computed field and TIME datatype


CREATE TABL
E A

(WORKSTART TIME,

WORKEND TIME,

DURATION COMPUTED BY (WORKEND
-

WORKSTART));


field duration will be of numeric type, not TIME as it
supposed to be.



Yes

Not
reproducible in
PostgreSQL.
The computed
by expression is
Interbase
specific.


233644

22
-
Feb
-
2001

cannot specify PLAN in UPDATE statement


I need to set FULLNAME=NAME for all records in
company where fullname IS NULL or fullname = '', so I
wrote the following

statement:




UPDATE COMPANY CP

SET FULLNAME= (SELECT NAME FROM
CONTACT CT WHERE CT.ID=
CP.CONTACTKEY)

WHERE CP.FULLNAME IS NULL OR
CP.FULLNAME = '';


Both tables contains ~40K records so the request hangs
my machine. After reboot I checked the plan and it was:


PLAN(company NATURAL, contact NATURAL)

I tried to set PLAN for entire UPDATE stat
ement or
only for SELECT sub
-
query but in both cases I received
error message:


Invalid SQL….



CREATE TABLE CONTACT (ID INT NOT NULL
PRIMARY KEY, NAME VARCHAR(20))


CREATE TABLE COMPANY (CONTACTKEY INT
NOT NULL PRIMARY KEY, FULLNAME
VARCHAR(20))


UPDATE
COMPANY

SET FULLNAME= (SELECT NAME FROM
CONTACT CT WHERE
CT.ID=COMPANY.CONTACTKEY)

WHERE COMPANY.FULLNAME IS NULL OR
COMPANY.FULLNAME = '';

Yes

No. It works in
PostgreSQL.



B1
-
42


406355

06
-
Mar
-
2001

Deletion from referenced tables in SP


For example, in ISQL:


SE
T SQL DIALECT 3;


CREATE DATABASE 'c:
\
test.gdb' USER 'SYSDBA'
PASSWORD '******' PAGE_SIZE 1024;


CREATE TABLE Table1 (

id INTEGER NOT NULL,

foreign_id INTEGER);


CREATE TABLE Table2 (

id INTEGER NOT NULL);


INSERT INTO Table2 (id) VALUES (1);

INSERT INTO T
able2 (id) VALUES (2);

INSERT INTO Table1 (id, foreign_id) VALUES (1, 1);


COMMIT WORK;


ALTER TABLE Table1 ADD CONSTRAINT
PK_TABLE1 PRIMARY KEY (id);


ALTER TABLE Table2 ADD CONSTRAINT
PK_TABLE2 PRIMARY KEY (id);


ALTER TABLE Table1 ADD CONSTRAINT
FK_TABL
E1 FOREIGN KEY (foreign_id)
REFERENCES Table2 (id);


SET TERM ##;

CREATE PROCEDURE CLEAR_TABLES

AS

BEGIN


No. When
execu
ting the
procedure it is
not silent. It
gives error:
Violation of
FOREIGN KEY
constraint
"FK_TABLE1"
on table
"TABLE1".
Possible Heisen
Bug.

Not
reproducible in
PostgreSQL.
Add Constraint
is not
implemented
with Alter
Table.



B1
-
43

DELETE FROM TABLE2;

SUSPEND;

END ##

SET TERM; ##


Executing procedure CLEAR_TABLES silent, and
no records deleted as result execution.


New
bug

25
-
Mar
-
2003

Bug wit
h user admin.


I have noticed while working with Interbase bugs, that
Interbase allows the creation of a user named admin but
then you cannot grant anything to this user.


Example:


From IB console create a new user

Username: admin

Password: admin


Login a
s SYSDBA


Grant all on tablename to admin


Gives error: Token unknown ‘admin’






create user admin with password ‘admin’


Login as user pgsql


grant all on tablename to admin


Doesn’t give an error



Yes

No. It works in
PostgreSQL

412417

30
-
Mar
-
2001

a
ltering from CHAR to VARCHAR


Altering CHAR to VARCHAR column adds 2 bytes to
field length. How to reproduce:


CREATE TABLE TEST( N CHAR(40));

then

ALTER TABLE TEST ALTER N TYPE
VARCHAR(40);



Yes

Not
re
producible in
PostgreSQL.

Altering the
type of a
column as part
of the alter
statement is not
supported in
PosgreSQL.



B1
-
44

after that RDB$FIELD_LENGTH will be 42, not 40
as it should be.

RDB$CHARACTER_LENGTH is Ok, i.e. it stays 40
after ALTER.


The '40' is not a magic number
-

same addition of 2
bytes will be for any altering from char to varchar. For
example, altering

char(40) to varchar(50) will give the resulting varchar
field 52 chara
cters length.

Real varchar storage size is 2 bytes greater than char.
But, column length definition (RDB$FIELD_LENGTH)
does not store

physical field lenght
-

it stores column length. So,
altering char to varchar must keep the same column size.


414420

06
-
Apr
-
2001

wrong order by in table join storedproc


InterBase will not sort correctly the result set of a table
joined with a st
ored procedure (inner join), if an
ascending sort on an indexed field is requested.


To expose the bug we need to join a table with a stored
procedure; here's the test metadata (tested with dialect
1):


CREATE TABLE MY_TABLE (

K VARCHAR(10) NOT NULL,

D1 IN
TEGER,

D2 INTEGER,

V1 VARCHAR(10),

PRIMARY KEY (K));


CREATE PROCEDURE SELECT_ME RETURNS (

DATA VARCHAR(10) )

AS

Begin


Yes

Not
reproducible in
PostgreSQL.
The
PostgreSQL
functions
always have to
end with a
select
statement. I
could not
therefore
reproduce the
procedure
select_me with
a PostgreSQL
function since a
join between a
table and a
function is not

accepted in


B1
-
45

DATA = 'one';

SUSPEND;

DATA = 'two';

SUSPEND;

DATA = 'three';

SUSPEND;

END


Let's populate my_table with some test data:


INSERT INTO MY_
TABLE VALUES ('one', 1, 99,
'zz');

INSERT INTO MY_TABLE VALUES ('two', 2, 98,
'yy');

INSERT INTO MY_TABLE VALUES ('three', 3, 97,
'xx');


The following query exposes the bug:

SELECT *

FROM MY_TABLE T JOIN SELECT_ME P

ON (T.K = P.DATA)

ORDER BY T.D1


The or
dering is correct on every field we put in the
ORDER BY clause, UNLESS that field is also indexed.
Here are the

correct results of the query:

K D1 D2 V1 DATA

========== ============ ============
========== ==========

one 1 99 zz one

two 2 98 yy two

three 3

97 xx three


If we now do:


CREATE INDEX I1 ON MY_TABLE(D1);

PostgreSQL.



B1
-
46


the same query returns the following results:


K D1 D2 V1 DATA

========== ============ ============
========== ==========

one 1 99 zz one

three 3 97 xx three

two 2 98 yy two


The results have n
ot been sorted.


414833

09
-
Apr
-
2001

Join Procedure Bug Initial Bug

Create a test Datadase using script

//
------------------------------------------------
//

SET NAMES WIN1251;

CREATE DATABASE 'D:
\
TEST.GDB'

USER 'SYSDBA' PASSWORD 'masterkey'

PAGE_SI
ZE 2048

DEFAULT CHARACTER SET WIN1251;


CREATE TABLE TEST_TABLE (

ID INTEGER NOT NULL,

DATEBEGIN DATE,

DATEEND DATE);


INSERT INTO TEST_TABLE (ID, DATEBEGIN,
DATEEND)

VALUES (1, '01/01/2001 00:00:00', '02/01/2000

00:00:00');

COMMIT WORK;


SET TERM ^ ;

CREA
TE PROCEDURE TEST1 (

BEGIN_DATE DATE,

END_DATE DATE)

RETURNS (




CREATE TABLE TEST_TABLE (

ID INTEGER NOT NULL,

DATEBEGIN DATE,

DATEEND DATE);


INSERT INTO TEST_TABLE (ID, DATEBEGIN,
DATEEND)

VALUES (1, '01/01/2001 00:00:00', '02/01/2000

00:00:00');


CREATE FUNCTION test1 (DATE,

DATE) RETURNS integer


AS 'select 0 as result'

LANGUAGE 'sql';


SELECT
TEST1(TEST_TABLE.DATEBEGIN,TEST_TABLE.D
ATEEND) AS ANSWER,

H.DATEBEGIN,H.DATEEND

FROM TEST_TABLE H;


answer | datebegin | dateend

Yes

No. It works in
PostgreSQL.
PostgreSQL
does not
support
procedures,
therefore it is
necessary to
use functions.



B1
-
47

RESULT INTEGER)

AS

begin

RESULT=0;

suspend;

end

^

SET TERM ; ^


and try to execute following statement


SELECT

H.DATEBEGIN,H.DATEEND

FROM

TEST_TABLE H,

TEST1(H.DATEBEGIN,H.DATEEND) G


Error: no

current records for fetch operation.


-------------------------------------

0
|
'01/01/2001|'02/01/2000


Which is the correct behaviour.


416477

16
-
Apr
-
2001

Malfunction of permissions/privileges


In V6.0, we will gran
t permissions for a procedure and
ONLY execution of the procedure to a user (users don't
have direct access

to data).

In new V6.0.1, is not possible to the user's schema to
execute procedures or select views, because the user
must have ALL

permissions on t
ables (and execute permissions on
procedure) to execute a procedure.

The documentation say in Data Definition Guide, page
205: 'Tip: As a security measure, privileges to tables can
be granted to a procedure instead of to individual users.
If a user has EXE
CUTE privilege on a procedure that
accesses a table, then the user

does not need privileges to the table.'. This is not
possible in V6.0.1.


Yes

Not
reproducible in
PostgreSQL.
Grant execute
is not
supported in
PostgreSQL.
Also grant
privileges can
be given only to
users or user
groups, and not
procedures or
functions.



B1
-
48


Example:


CREATE TABLE TAB(I INTEGER);


SET TERM ^ ;

CREATE PROCEDURE PROC1

AS

BEGIN

EXIT;

END ^

ALTER PROCEDURE PR
OC1

AS

DECLARE VARIABLE x INTEGER;

BEGIN

INSERT INTO tab VALUES (123);

x=1/0;

WHEN ANY DO EXIT;

END ^

SET TERM ; ^


grant all on tab to proc1;


GRANT EXECUTE ON PROCEDURE proc1 TO ilir;


As user ilir


EXECUTE PROCEDURE PROC1;


Error: No permission for inse
rt/write access to table
TAB.



419065

26
-
Apr
-
2001

Join on different dat
atypes


CREATE TABLE H (FZDNR CHAR(10));




CREATE TABLE H (FZDNR CHAR(10));


No. It
works.
Only the value
11 is
returned(which
PostgreSQL
does not
support the not
in, <>, ><


B1
-
49

CREATE TABLE WA (HNR NUMERIC);


INSERT INTO H VALUES ('12');

INSERT INTO H VALUES ('13');

INSERT INTO H VALUES ('14');


INSERT INTO WA VALUES (11);

INSERT INTO WA VALUES (13);

INSERT INTO WA VALUES (14);


SELECT *
FROM WA WHERE HNR NOT IN
(SELECT FZDNR FROM H)


I get the whole table WA, so I think the join on HNR
-
FZDNR doesn’t work.


CREATE TABLE WA (HNR NUMERIC);


INSERT INTO H VALUES ('12');

INSERT INTO H VALUES ('13');

INSERT
INTO H VALUES ('14');


INSERT INTO WA VALUES (11);

INSERT INTO WA VALUES (13);

INSERT INTO WA VALUES (14);


SELECT * FROM WA WHERE HNR NOT IN
(SELECT FZDNR FROM H)


Will give error: Unable to identify operator < > for
types ‘numeric and ‘bpchar’.


is the only value
not in H).

Possible Heisen
bug.

operators for
attributes of
different types.
In order to run
the query an
explicit cast
needs to be
done. This can
b
e viewed as a
possible
limitation of
PostgreSQL 7.0

421263

04
-
May
-
2001

UDF substr gives NULL if slice input



The substr UDF that comes with FB returns NULL
when the final position is greater than the last position in
the input string

argument. This caus
es any kind of problems.


Sample code:


create table temp2(name varchar(10));

insert into temp2 values('dsfhgfg');

select substr(name,2,11) from temp2;


Returns:


substrName

--------------

<null>




CREATE TABLE TEMP2(NAME VARCHAR(10));


INSERT INTO TEMP2
VALUES('DSFHGFG');


SELECT SUBSTR(NAME,2,11) FROM TEMP2;


Returns:


substr

---------

sfhgfg


Which is the correct behaviour.

Yes

No. It works in
PostgreSQL.

423810

13
-
May
-
2001

User Password length



Yes

The present
installation of


B1
-
50

try to connect to a database with user=SYSDBA

password=
masterke


It seems that password legth is truncated to 8 char.

Looking in isc4.gdb, PASSWD (in table users) is
defined as PASSWD AS VARCHAR(32)
CHARACTER SET OCTETS;.


PostgreSQL in
the DOTS
project
computers does
not requi
re
passwords,
therefore could
not test this
bug. Left as
further work
for future
developers.


425799

20
-
May
-
2001

Renamed domain leaves behind dimensions


With a domain being an array, renaming the domain
causes rdb$field_dimensions to be left unchanged;
hence the connection

between a domain and its dimensions specification is
broken.


Example:

CREATE DOMAIN DUNNO INT[0];

COMMIT;

ALTER DOMAIN DUNNO TO DITTO;

COMMIT;


select * from rdb$field_dimensions;

= will show that the referenced domain is still
DUNNO
instead of DITTO. System tables are out of
sync.



Yes

Not
reproducible in
PostgreSQL.
Alter domain
(or Alter Type)
not supported
in PostgreSQL.

425949

21
-
May
-
2001

Engine CRASH Error


select count(*),adresy.rdb$db_key from adresy


adresy
-
this is any tabl
e


Yes

Not
reproducible in
PostgreSQL.
db_key field is
not supported


B1
-
51

in any of the
PostgreSQL
system tables.


428889

30
-
May
-
2001

alter column col position pos zero
-
based


The syntax


alter table tbl alter column col position pos;


is zero
-
based
. Since it
's SQL command, it should be in
sync with SQL positions starting at one.



Yes

Not
reproducible in
PostgreSQL.
Position is not
supported in
Alter Table
statement.

428903

30
-
May
-
2001

Exception Handling Bug


If you raise user defined exception (by EXCEPTIO
N
command) and it is handled by WHEN ANY DO or
WHEN EXCEPTION ... DO then changes made by that
SP are NOT undone !!


Here is example:


CREATE TABLE tab (i INTEGER);

CREATE EXCEPTION e 'abcd';


/* Division by zero in this SP will cause INSERT to be
undone *
/

CREATE PROCEDURE proc1

AS

DECLARE VARIABLE x INTEGER;

BEGIN

INSERT INTO tab VALUES (123);

x=1/0;

WHEN ANY DO EXIT;

END


/* Calling EXCEPTION and handling it by WHEN

Yes

Not
reproducible in
PostgreSQL.

Exceptions not
supported in
PostgreSQL.



B1
-
52

ANY in

this procedure does NOT undo INSERTed row !! */

CREATE PROCEDURE proc2

AS

BEGIN

IN
SERT INTO tab VALUES (456);

EXCEPTION e;

WHEN ANY DO EXIT;

END


/* Here is called EXCEPTION, but it is not handled by

WHEN ANY,

so inserted row will be correctly removed. */

CREATE PROCEDURE proc3

AS

BEGIN

INSERT INTO tab VALUES (789);

EXCEPTION e;

END


EX
ECUTE PROCEDURE proc1;

EXECUTE PROCEDURE proc2;

EXECUTE PROCEDURE proc3;


SELECT * FROM tab;

I

=======

456 table should be empty !!!


430509

05
-
Jun
-
2001

ORDER BY works wrong with

collate PT_PT


Consider,

CREATE TABLE STOCKS (

MNEN INTEGER NOT NULL,

ACTIVO VARCHAR(50) CHARACTER SET
ISO8859_1 COLLATE PT_PT,

CONSTRAINT PK_STOCKS PRIMARY KEY



Yes

Not
reproducible in
PostgreSQL.
collate pt_pt is
an Interbase
specific feature.



B1
-
53

(MNEN) );


having the values

1, BA

2, BES

3, BCP

4, BA Pref.

5, Banif


doing

SELECT ACTIVO FR
OM STOCKS ORDER BY
ACTIVO


gives:

BA

Banif

BA Pref.

BCP

BES


440541

12
-
Jul
-
2001

insert into... select


one table.

CREATE TABLE T1 (F1 VARCHAR(5), F2
INTEGER)


INSERT I
NTO T1 (F1, F2) VALUES ('SS',1);

INSERT INTO T1 (F1, F2) VALUES ('QQ',2);


bug

=======

insert 27(!!!) records instead 1

INSERT INTO T1 SELECT * FROM T11 WHERE
F2=2;

Halts the server (stops and restarts)

same

INSERT INTO T1 SELECT T2.* FROM T1 T2


CREATE TABLE T1 (F1 VARCHAR(5), F2
INTEGER);


INSERT INTO T1 (F1, F2) VALUES ('SS',1);

INS
ERT INTO T1 (F1, F2) VALUES ('QQ',2);


INSERT INTO T1 SELECT * FROM T11 WHERE
F2=2;

Works normally.


INSERT INTO T1 SELECT T2.* FROM T1 T2
WHERE T2.F2=2;

Works normally.


INSERT INTO T1 (F1,F2)

Yes

No. It works in
PostgreSQL



B1
-
54

WHERE T2.F
2=2;

Halts the server

(stops and restarts)


trap server if execute

INSERT INTO T1 (F1,F2)

SELECT F1,F2 FROM T1 WHERE T1.F2=2;

Halts the server

(stops and restarts)

SELECT F1,F2 FROM T1 WHERE T1.F2=2;

Works normally.


No errors

given.

444763

26
-
Jul
-
2001

Empty column names with aggregate funcs


If you execute this statement the column name of the
inner select is empty:


SELECT (SELECT COUNT(1) FROM
RDB$DATABASE) FROM RDB$DATABASE


I don't know if
this is a bug or just a limitation but it
seems that this bug(limitation) is somehow related to
#222476. The

difference is that it happen with all aggregate
functions(count, min, max, sum, avg) and also in dialect
1.



SELECT (SELECT COUNT(1) FROM
PG_DATAB
ASE) FROM PG_DATABASE


Returns:


?column?

----------

5

5

5

5

5


The column name is ‘?column?’ since there is no
column to select. Not a bug.


Yes

No. There is no
column to
select, therefore
there is no
column name.

447002

01
-
Aug
-
2001

Optimisation on subse
lects


The InterBase Optimizer does not properly utilize
indexes on sub
-
selects containing IN.


Example: (Using
-

Employee.gdb)


SELECT * FROM EMPLOYEE

WHERE DEPT_NO IN

(SELECT DEPT_NO

FROM DEPARTMENT

WHERE DEPARTMENT = MARKETING);


Yes

Not reproduced
in PostgreSQL.
Due to time
constraints on
the project, this
bug was not
reproduced in
PostgreSQL
since a
translation of
the schema of
the


B1
-
55


Produces the plan:

PLA
N (DEPARTMENT INDEX
(RDB$4,RDB$PRIMARY5))

PLAN (EMPLOYEE NATURAL)


employee.gdb
database was
need
ed. In any
case the plans
that
PostgreSQL
produces are
much more
different from
those of
Interbase and
they are
difficult to
compare. Left
as further work
for future
developers.


449312

08
-
Aug
-
2001

Create External Table with relative path segfault


The
following:

CREATE TABLE FOO EXTERNAL FILE 'FOO.DAT'
(BAR CHAR(10));


Results in:

Segmentation fault

An external file without an absolute path doesn't make
sense, but we should not segfault.



No. It works as
without giving
segmentation
fault.

Not
reproduc
ible in
PosgreSQL.
Create external
table option not
supported in
PostgreSQL