Submitted by: Claudio Valderrama C. (robocop)

Assigned to: @ArnoBrinkman

SFID: 225218#⁠
Submitted By: robocop

Hello. According to the standard, it's up to the db vendor where NULLs should be, either at the tail or at the head of an ordered recordset. In IB, when you order ASC (default) on a field with NULLs, they go to the tail. That's a vendor decision. However, the engine must be consistent and in this case, it doesn't: if you order DESC on the same fields, NULLs will appear at the tail, too. One of the answers should be changed.

C.

Submitted by: Alice F. Bird (firebirds)

Assigned to: Geoffrey Speicher (gspeicher)

SFID: 216778#⁠
Submitted By: nobody

FreeBSD port should be linked with -ldescrypt, not -lcrypt, because http://libcrypt.so can be symlink to http://libscrypt.so.

Or it should have DES inside.

Submitted by: Andrei Kireev (andreik)

SFID: 215169#⁠
Submitted By: andreik

I've picked this bug description from IBX 4.3 sources:

Sometimes a prepared stored procedure appears to get
off sync on the server ....This code is meant to try
to work around the problem simply by "retrying". This
need to be reproduced and fixed.

it is in IBSQL.PAS, in the TIBSQL.ExecQuery:

...
SQLExecProcedure: begin
fetch_res := Call(isc_dsql_execute2(StatusVector,
TRHandle,
@FHandle,
Database.SQLDialect,
FSQLParams.AsXSQLDA,
FSQLRecord.AsXSQLDA), False);
if (fetch_res <> 0) and (fetch_res <> isc_deadlock) then
begin
{ Sometimes a prepared stored procedure appears to get
off sync on the server ....This code is meant to try
to work around the problem simply by "retrying". This
need to be reproduced and fixed.
}
isc_dsql_prepare(StatusVector, TRHandle, @FHandle, 0,
PChar(FProcessedSQL.Text), 1, nil);
Call(isc_dsql_execute2(StatusVector,
TRHandle,
@FHandle,
Database.SQLDialect,
FSQLParams.AsXSQLDA,
FSQLRecord.AsXSQLDA), True);
end;
end
...

Submitted by: Alice F. Bird (firebirds)

SFID: 216464#⁠
Submitted By: nobody

Free IB6 can't connect to database placed in directory contains non-ASCII characters, such as russian WIN1251.

====== Test Details ======

No ability to create and use non-ascii names of directories and files on test running host.

Submitted by: hipgnosis (hipgnosis)

SFID: 213460#⁠
Submitted By: hipgnosis

Overview

With a certain server network configuration, registering an event will lock up the client and lockup the server (requiring a reboot if it runs as a service, or a process kill if it runs as an application (the guardian does not detect it's lockup)) *if* the client is accessing the server through the internet (if it comes through the local lan, then it's fine).

System Discovered On

Dell Precision 610 dual 550 Xeon III's w/512 megs of memory.
Windows NT 4 Workstation Service Pack 6
Interbase 6 (normal release)
3Com Fast EtherLink XL 10/100Mb TX Ethernet NIC (3C905B-TX)
No firewalls, or other devices that might interfere with network traffic (for purposes of this test). All IP's are manually assigned (no DNS or DHCP server), and TCP/IP is the only network protocol installed on this system.

Configuration Description To Cause Bug

The above system has only one nic card in it. This card was assigned two IP's, the first being a 192.168.0.x (local network), and the second being it's internet address. A gateway for the NIC card was also set, in addition to IP Forwarding (which was removed later).

This configuration, while not considered the best, is perfectly legal, and works effectively with all the different servers run on it (MS SQL Server, ftp and web servers, etc.), both accessed from the internet and the local intranet.

It is only when a client attempts to register events when connected through the internet to the server system that the lockups occur. Otherwise, if the system is on the intranet, the access runs fine.

Fix

By setting the internet IP first, then having the intranet ip set in the advanced screen (under the internet IP), the problem is resolved (a reboot will be required after changing the configuration).

Comments

This was a strange bug to hunt down, and stems from the fact that the register events connection to the server through the internet is somehow 'different' enough to cause the crashes (the client will come back to life if the server is shutdown (in NT the server process has to be manually terminated).

While the problem requires a certain network configuration to occur (which tends to be common for small internal networks with a dsl or cablemodem connection to the internet), but given the anticipated spreading of InterBase with new applications, it becomes possible that
some of our customers may receive this frustrating error.
This is made even more dangerous by the fact that their network configurations may have been set by a third party consultant, or department IT person.

At the minimum, I would recommend that the IB server at have an exception written into it to handle this type of error gracefully, and return an exception to the connecting client indicating the network incompatibility.

Submitted by: @dyemanov

Assigned to: Claudio Valderrama C. (robocop)

SFID: 226456#⁠
Submitted By: dimitr

When I have an index, for example: "idx_asc_History_Stamp" and try to use this index in my SELECT statement as shown below:
...
PLAN ("History" ORDER "idx_asc_History_Stamp")
...
then I get an error with ISC ERROR CODE = 335544343:

invalid request BLR at offset 112
there is no index IDX_ASC_HISTORY_STAMP for table History

But if I have an index named IDX_ASC_HISTORY_STAMP or both of them, this SELECT statement with my plan works fine, but uses the upper-cased one.

So it looks like PLAN cannot recognize an index name, which is SQL delimited identifier.

Submitted by: Claudio Valderrama C. (robocop)

SFID: 223516#⁠
Submitted By: robocop

LangRef describes among values that
rdb$fields.rdb$field_type can assume the following two:
D_FLOAT - 11
INT64 - 16
Both values appear in the documentation but not in rdb$types.
I don't have any idea what D_FLOAT is but at least INT64 should be listed in rdb$types for consistency reasons. Also, putting NUMERIC (1) and DECIMAL (2) as registered sub_types would help programs that cope with metadata.

C.

Submitted by: Alice F. Bird (firebirds)

SFID: 223327#⁠
Submitted By: nobody

Installed fbinst_prod_110300-2.exe on a clean machine (WIN2000) in D:\Program Files\FireBird.

Tried to connect via IBConsole, got "Couldn't attach to password db."-thingy :-)

Checked with regedit and found:

HKEY_LOCAL_MACHINE\SOFTWARE\Borland\InterBase\CurrentVersion\RootDirectory = :\Program Files\FireBird\

and

HKEY_LOCAL_MACHINE\SOFTWARE\Borland\InterBase\CurrentVersion\ServerDirectory = c:\Program Files\FireBird\

corrected both to the installed path (d:\Program Files\FireBird\), restarted the InterBase Server service and everything was ok.

I was also missing the InterBase Guardian in my services.

Small bug, with small fix :-)

RGDS
Steffen Nyeland

Submitted by: rfm (rfm)

Assigned to: Frank Schlottmann-Goedde (fsg)

SFID: 212263#⁠
Submitted By: rfm

Workaround is to set ISC_USER / ISC_PASSWORD beforehand,
or use unix authentication

To reproduce (using windows):
unset ISC_USER and ISC_PASSWORD
isql -x {some database} -user {some valid user} -password {some valid password}

This apears to be a logic bug, rather than an option parsing bug.
In isql.e -a and -x are handled differently from other cases which
call newdb from do_isql

Submitted by: andycanfield (andycanfield)

SFID: 224130#⁠
Submitted By: andycanfield

I had previously installed the ib_client package. I installed firebird. Things got very confused. For example here are the registry entries:

[HKEY_LOCAL_MACHINE\Software\Borland\InterBase\CurrentVersion]
"UseCount"=dword:00000001
"RootDirectory"="C:\\Program Files\\Borland\\InterBase\\"
"Version"="WI-V6.0.0"
"DefaultMode"="-r"
"ServerDirectory"=":\\Program Files\\firebird\\"
"GuardianOptions"="1"

Notice that it was installed into C:\Program Files\Firebird but the root directory is C:\Program Files\Borland\Interbase. The RootDirectory entry was apparently left over from my install of ib_client. After I thoroughly uninstall Firebird and Interbase and re-install Firebird, the RootDirectory is C:\\Program Files\\firebird\\ which is probably correct.

The normal procedures of installing software under Windows is that either [a] Firebird is Interbase so I should be able to install over an old version, or [b] Firebird is not Interbase so I should have no problem installing both on the same machine. But in may ways Firebird still thinks it is named "InterBase" and gets confused.

We should anyone installing Firebird to uninstall any version of InterBase, server or client, before installing Firebird. If possible, the Firebird install should check to see if InterBase is installed, and halt with an error message if so.

Submitted by: Claudio Valderrama C. (robocop)

SFID: 223128#⁠
Submitted By: robocop

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.
But why would IB allow SYSDBA to grant no existent roles to users? For example, this is accepted:
grant anything to alice
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. So I wonder is this is a bug or a feature. Why would SYSDBA need to bypass role checking when grating roles to users?

C.

Submitted by: Claudio Valderrama C. (robocop)

SFID: 222476#⁠
Submitted By: robocop

This is already well known, but I want to document it formally here: any tool using dialect 3 (to connect to a dialect 3 db, obviously) will report two unnamed fields in this case:

select avg(1), sum(1) from rdb$database

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

C.

Submitted by: Claudio Valderrama C. (robocop)

Assigned to: brodsom (brodsom)

SFID: 223513#⁠
Submitted By: robocop

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;

/* Notice both T and V are shown, but only T should be shown.
One can argue that a view is a virtual table, etc. */
SQL> show tables;
T V

SQL> show views;
V

/* This shouldn't work */
SQL> show table v;
A INTEGER Nullable
View Source:
==== ======
select a from t

/* This shouldn't work */
SQL> show view t;
A INTEGER Nullable

I think isql should stop treating VIEWs as TABLEs in its SHOW command as this is confusing.

C.

Submitted by: Alice F. Bird (firebirds)

SFID: 212340#⁠
Submitted By: nobody

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.

====== Test Details ======

NB: PLAN up to 2.5 contains TWO parenthesis:
PLAN SORT ((CUSTOMERS ORDER CUSTOMERS_COUNTRY)) --- note that here "((" and "))" rather than "(" and ")"

Submitted by: Claudio Valderrama C. (robocop)

SFID: 222563#⁠
Submitted By: robocop

Let's name a cat, a cat. This is a bug involving metadata extraction. Given the following declaration:

CREATE PROCEDURE p
(inparam CHAR(10) CHARACTER SET unicode_fss)
RETURNS
(outparam CHAR(10) CHARACTER SET unicode_fss)
AS
DECLARE VARIABLE
var CHAR(10) CHARACTER SET unicode_fss;
BEGIN EXIT; END

The macro-command SHOW PROCEDURE P will output:
Parameters:

INPARAM INPUT CHAR(30) CHARACTER SET UNICODE_FSS
OUTPARAM OUTPUT CHAR(30) CHARACTER SET UNICODE_FSS

As you can see, CHAR(10) becomes CHAR(30), because the tool is reading rdb$field_length instead of rdb$character_length for procedure's parameters. In the case of table's fields, the correct information is read and presented. The following command confirms that the engine itself is doing the right thing:

SELECT rdb$field_length, rdb$character_length
FROM rdb$fields
WHERE rdb$field_name IN (
select rdb$field_source from rdb$procedure_parameters
where rdb$procedure_name = 'P' )

rdb$field_length rdb$character_length
================ ====================
30 10
30 10

The same problem affects IBConsole as well as some third party tools. Thanks to Ivan Prenosil for his discovery.

C.

Submitted by: prenosil (prenosil)

Assigned to: Claudio Valderrama C. (robocop)

SFID: 223793#⁠
Submitted By: prenosil

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!

Submitted by: Claudio Valderrama C. (robocop)

SFID: 221649#⁠
Submitted By: robocop

I noticed it in 1999 (IB6 in private beta testing) but I'm not sure who discovered it. It still exists and may be rather old.
create table mau(a int);
commit;
create UNIQUE index idx_mau on mau(a);
commit;
Whether this was left as a transition from Paradox (that accepts NULL PKs) or it's an oversight is what I want to confirm.
As a comparation, you cannot declarate a PK or UNIQUE constraint if you don't include the NOT NULL clause on the affected field(s), so the underlying automatic index for such constraint will be always on non-nullable column(s).

C.

Submitted by: patrickgriffin (patrickgriffin)

SFID: 213708#⁠
Submitted By: patrickgriffin

When connecting to IB60, microfocus COBOL programs receive:

Dynamic SQL Error
-SQL error code = -502
-Declared cursor already exists

Host system AIX 4.2.1.0

The identical program will work properly when connecting:
- locally to IB40C
- remotely to IB5.6

The program fails when connecting:
- locally to IB60 on AIX (CLASSIC configuration)
- remotely to IB60 on WINNT (SUPERSERVER configuration)

The problem holds true when the sample program is GPRE'ed with IB40C and with IB60.

...pat

Submitted by: Claudio Valderrama C. (robocop)

Assigned to: @ArnoBrinkman

SFID: 219525#⁠
Submitted By: robocop

I've copied the complete message and I will add my comment at the bottom:

=========
"Marco Rocci" wrote in message mailto:news:[email protected]...
>
> ---begin metadata---
>
> CREATE DATABASE "JoinTest.gdb" USER "SYSDBA" PASSWORD "masterkey"
> PAGE_SIZE 2048;
>
> 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;
> 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_des 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,
> 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);
> ALTER 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_UK UNIQUE (itmdes);
> 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_tabitm_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 ,
> a.detcod ,
> 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 (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,dscdes,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);
> 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);
> INSERT INTO tabdet (invcod,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,colcod,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 tabdet (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;
>
> ---end metadata---
>
> SELECT * FROM view_det INNER JOIN view_inv ON
> 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.invcod
>
> Any opinions or workarounds?
>
> TIA and regards,
>
> --
> Marco Rocci

If you watch the query plan generated by the failing statement, you'll notice it's really big... no surprise since it involves a join of views that in turn are based on other joins.
The workaround is to convert
SELECT * FROM view_det JOIN view_inv ON
view_det.invcod=view_inv.invcod
to become
SELECT * FROM view_det JOIN view_inv ON
view_det.invcod-view_inv.invcod=0
or
SELECT * FROM view_det JOIN view_inv ON
view_det.invcod+0=view_inv.invcod
or other variations that fool the optimizer by forcing it to a full scan to do the join.
But this doesn't deny the fact that the original statement should work, so this is a bug and the same kind of bug that IB5.X suffered from.

C.

Submitted by: prenosil (prenosil)

SFID: 223060#⁠
Submitted By: prenosil

Suppose you have table

CREATE TABLE tab (field INTEGER CHECK(field>=0) );
CREATE INDEX idx ON tab (field);

with lots of rows where field=0.
When you execute these two statements

SELECT * FROM tab WHERE field >= 1
SELECT * FROM tab WHERE field > 0

they both will return the same result set,
they both will use the same plan,
however the second one (with >) will be much slower.
The difference is caused by internal processing
of ">=" and ">" clauses when using indexes.

When you execute
SELECT * FROM tab WHERE field >= 1
IB will use index to locate all values >=1, o.k.

When you execute
SELECT * FROM tab WHERE field > 0
IB will use index to locate all values >=0 !!!
i.e. ALL rows from our test table !!!

Why nobody noticed it ?
- for columns with more even distribution of values
the speed difference is minimal
- IB usually evaluates condition twice
1. it first locates row by index
(not always correctly as you can see)
2. after fetching row it evaluates the condition again
(in this case it discards rows where field=0,
that should have been filtered out by index)

How can you verify this theory ?
- start (W)ISQL
and update one row where value is 0.
Do not commit.

- start another (W)ISQL and start transaction
SET TRANSACTION READ COMMITTED NO RECORD_VERSION NO WAIT

Now, first execute faster query (where field >= 1).
You will get correct answer.

Then execute slow query (where field > 0).
You will get "lock conflict" error.
It means that IB tries to internally fetch
row that does not meet criteria (value>0)
and that should have been skipped when using index properly.

Submitted by: Frank Schlottmann-Goedde (fsg)

SFID: 216579#⁠
Submitted By: fsg

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

Here is a script to reproduce this bug:

create database "cf.gdb";

create generator gen1;
set generator gen1 to 1000;
show 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;

set generator gen1 to 1000;
show generator gen1;
select * from t0;

drop database;
exit;

Submitted by: prenosil (prenosil)

SFID: 223058#⁠
Submitted By: prenosil

The feature that allows you to connect to IB server through
several other IB servers, like
"alpha:beta:\\gamma\delta:C:\InterBase\db.gdb"
does not work, if one of the "relay" servers
(i.e. alpha, beta, gamma from above example) is IB6.

IB6 will allow connection,
but when trying to execute any command you will get
"Dynamic SQL Error
-SQL error code = -901
-feature is not supported"

====== Test Details ======

C:\temp>isql localhost:e40
Database: localhost:e40, User: SYSDBA
SQL> commit;
SQL> connect 'localhost:localhost:e40';
Statement failed, SQLSTATE = 08001
unavailable database
SQL> connect 'localhost/3400:localhost/3400:e40';
Statement failed, SQLSTATE = 08001
unavailable database
SQL>

How this ticket should be checked ?..

Submitted by: Claudio Valderrama C. (robocop)

Assigned to: Claudio Valderrama C. (robocop)

SFID: 223133#⁠
Submitted By: robocop

Doing a self join without assigning alias to both occurrences 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 expected. 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 incredible 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
... etc. The first table is repeated a number of times and nothing more is shown.

Solution for the user: apply aliases to all occurrences of the same table in a self join:

select rdb$relation_name from rdb$relations r1
join rdb$relations r2
on r1.rdb$relation_id = r2.rdb$relation_id
order by 1
=> yields the correct results.

Solution for the engine:
- Demand that self-joins use aliases to distinguish different occurrences of the same table.
- Self-joins using the original table name should be rejected as ambiguous.

Associated with this issue, there's this one:
- The server should demand that once a table has been qualified by an alias, only the alias can be used. This helps clearing ambiguity. In fact, MsSql detects this construction an rejects it. This is an invalid statement:

select r.rdb$relation_name
from rdb$relations r join rdb$indices i
on rdb$relations.rdb$relation_name = i.rdb$relation_name
and rdb$unique_flag = 0

Look at the 3rd line. It should be instead:

on r.rdb$relation_name = i.rdb$relation_name

since the alias was declared previously in the join. I saw in the past very sensible self joins that fell because IB allows to ignore the alias and use directly the table name.

C.

Submitted by: Claudio Valderrama C. (robocop)

Assigned to: Claudio Valderrama C. (robocop)

SFID: 223512#⁠
Submitted By: robocop

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 and vice versa. */
SQL> drop table v;
SQL> drop view t;

Diane Brown as confirmed that this is not an expected behavior. DROP TABLE should eliminate only a table, not a view and same restriction exists for DROP VIEW, even though IB shares the namespace between tables, views and procedures.

C.

Submitted by: fluis (fluis)

Assigned to: rfm (rfm)

SFID: 212899#⁠
Submitted By: fluis

http://cvs.sourceforge.net/cgi-bin/cvsweb.cgi/interbase/jrd/cmp.c.diff?r1=1.2&r2=1.3&cvsroot=firebird

Submitted by: prenosil (prenosil)

Assigned to: Claudio Valderrama C. (robocop)

SFID: 223059#⁠
Submitted By: prenosil

When IB updates VARCHAR string, it does not zero rest of the string.
(when the row is decompressed in the buffer).

e.g. you have table
CREATE TABLE t ( a VARCHAR(50) );
INSERT INTO t
VALUES ('abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmn');
COMMIT;

When 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:
'XYZdefghijklmnopqrstuvwxyz1234567890abcdefghijklmn'

Because VARCHARs contain 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 rle compressed),
and database can get slower
(because less useful data fit onto page).

How can you test this behaviour ?
- Either look at gdb file with hex-viewer, or
- use gstat utility with parameter "-r".

After running example commands, you get these statistics:
Average record length: 57.00, total records: 1
Average version length: 9.00, total versions: 1
(57 = newest record version tooooo long)

When you run the same test, but with CHAR column instead,
you will see following statistics:
Average record length: 10.00, total records: 1
Average version length: 53.00, total versions: 1
(10 = CHAR column is always padded with spaces,
so rest of the string will be compressed, o.k.)
(53 = longer delta, but it will be garbage collected, o.k.)

Submitted by: prenosil (prenosil)

Assigned to: Claudio Valderrama C. (robocop)

SFID: 223056#⁠
Submitted By: prenosil

In SP, when you copy record (from/to the same table) by
INSERT INTO tab SELECT ... FROM tab WHERE ...;
or by
SELECT ... FROM ... INTO _local_variables_;
INSERT INTO tab VALUES (_local_variables_);

and the record contains BLOB, then _sometimes_
newly created row will not contain its own copy
of the BLOB, but instead it will use the same
blob-id as the original record.
(i.e. single blob is shared among more rows)

This is really severe bug, because it will cause
data lost - when you delete one of these rows,
and then try to read the other one, you will get
"BLOB not found" error !!!

Interestingly, when you execute exactly the same
command directly, not as part of SP, blob-ids will be o.k.

Here is script to reproduce this bug:

CREATE DATABASE 'C:\test.gdb' USER 'SYSDBA' PASSWORD 'masterkey';

CREATE TABLE t (
i INTEGER,
b BLOB );

SET TERM ^;

CREATE PROCEDURE p (x INTEGER) AS
BEGIN
INSERT INTO t (i,b)
SELECT i+100, b FROM t WHERE i=:x;
END^

SET TERM ;^

COMMIT;

/* insert some blob into our test table */
INSERT INTO t (i,b)
SELECT 1, rdb$trigger_blr
FROM rdb$triggers
WHERE rdb$trigger_name='RDB$TRIGGER_1';

COMMIT;

/* now make copy of that row ... */
/* INSERT INTO t (i,b)
SELECT i+100, b FROM t WHERE i=1; */
INSERT INTO t (i,b)
SELECT i+100, b FROM t WHERE i=1;
INSERT INTO t (i,b)
SELECT i+100, b FROM t WHERE i=101;
INSERT INTO t (i,b)
SELECT i+100, b FROM t WHERE i=101;

SET BLOBDISPLAY OFF;
/* ... and show result; */
/* all blobs will have different blob-ids */
SELECT * FROM t;

ROLLBACK;

/* now execute the same insert statements, but wrapped in SP */
/* EXECUTE PROCEDURE p 1; */
EXECUTE PROCEDURE p 1;
EXECUTE PROCEDURE p 101;
EXECUTE PROCEDURE p 101;

/* here you will see that blob-id is shared ... */
SELECT * FROM t;

/* and here you can see what will happen when one row is deleted ... */
SET BLOBDISPLAY ALL;
DELETE FROM t WHERE i=101;
COMMIT;
SELECT * FROM t;

/************
One more test - uncomment first of "EXECUTE PROCEDURE p 1" commands
and run this script again - you will see correct blob-ids,
i.e. the wrong behaviour is not consistent.
************/

Submitted by: Geoffrey Speicher (gspeicher)

Assigned to: Geoffrey Speicher (gspeicher)

SFID: 212129#⁠
Submitted By: gspeicher

If you try to access a database file that's mounted via NFS, but you specify its path in the filesystem as if it were local, InterBase would normally change that into a network request to prevent file corruption by multiple clients.

Example: Pretend our system mounts /home from the root directory of a machine named simpsons. Now pretend you request to connect to the database /home/marge/groceries.gdb. InterBase _should_ turn the request into a connection to simpsons:/marge/groceries.gdb and let simpsons handle reads and writes to the file on its own.

The FreeBSD port of InterBase doesn't do that. So don't go connecting to databases mounted via NFS unless you know what you're getting into or file corruption doesn't bother you a bit.

Submitted by: Alice F. Bird (firebirds)

SFID: 224332#⁠
Submitted By: nobody

There are two mkpasswd programs on Mandrake 7.2 and the
RPM install script ends up using the wrong one. The script
does not specify a path for mkpasswd so it uses the one in
/usr/sbin which doesn't work the same way or do the same
thing as the one in /usr/bin. The /usr/sbin one comes from
the shadowutils package and while the desired one comes
from the expect package.

The rpm install script should specify the full pathname for
mkpasswd or in some way verify which one gets used. If
this doesn't work, the idea should be dropped as being too
frail to work in all cases. Perhaps it should check for
mkpasswd in /usr/bin and if it doesn't exist, it could just
default to a password of masterkey like it used to.

Submitted by: Alice F. Bird (firebirds)

Assigned to: Mark O'Donohue (skywalker)

SFID: 224333#⁠
Submitted By: nobody

Mandrake 7.2 can be setup to use xinetd instead of the
regular inetd in which case the /etc/inetd.conf file is
not the right way to setup the server. Instead a file
needs to be placed in /etc/xinet.d with basically the same
information as goes in inetd.conf.

Submitted by: @helebor

SFID: 214298#⁠
Submitted By: helebor

Confirmed in v. 5.6, not tested in v. 6:

select count(*) + 1 returns zero when table has no rows but returns correct result if count(*) > 0.

Submitted by: Andrei Kireev (andreik)

Assigned to: patrickgriffin (patrickgriffin)

SFID: 212177#⁠
Submitted By: andreik

hi,

I just discovered error in IB6.0.

I have a table

create table bug_bugbase (
s varchar(20) character set win1251 collate win1251 default 'XXXX'
not null
);

I work in win1251 code page, which is for belarusian language.

Note, the default value above 'XXXX' is a cyrrilic (belarusian) text.

Database works well, I can insert records, update, and delete them.
But when I backup it and then restore I receive the next error
message from gbak:

gbak: restore failed for record in table BUG_BUGBASE
gbak: ERROR: arithmetic exception, numeric overflow, or string truncation
gbak: ERROR: Cannot transliterate character between character sets

it seems that IB supports default values only in standart
(english) code page.

I'm very upset, because, I just lost result of week of my work.
I backed up database, then deleted GDB file and then
tried to restore it and recieved this message. All records in tables
where I had default values in cyrrilic are missed.

With best regrads,

Andrei Kireev
mailto:[email protected]

Submitted by: Alice F. Bird (firebirds)

Assigned to: Claudio Valderrama C. (robocop)

SFID: 221925#⁠
Submitted By: nobody

Try the following:

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,
whendone timestamp,
jmsref jobcodetype not null,

primary key (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;

create 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.jobid
into :jobid,:waiting do
begin
if (waiting is null) then
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 "Faults"(id,whenreported,jmsref) values ('item A',cast('Now' as timestamp),'A');
insert into "Faults"(id,whenreported,whendone,jmsref) 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 joined!! */

IB 6.01, W2k SP1, IBConsole 319. Dialect 3 database.
mailto:[email protected]

Submitted by: Claudio Valderrama C. (robocop)

Assigned to: Claudio Valderrama C. (robocop)

SFID: 217138#⁠
Submitted By: robocop

Since I was able to reproduce the immediate crash by copying and pasting the code from the newsgroup and running the query against IB6 (SS) on NT, without having data in the tables, I will save the issue here, because it doesn't matter if it's SS or classic or what OS s being used:

"Ian Newby" wrote in message mailto:news:[email protected]...

Hi folks,

Background: IB6 Classic Redhat Linux 6.2 Problem occurs using quickdesk,
ibadmin etc/
I have a simple SQL statement which always kills the connection.
The SQL is as follows:

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 a " Unable to complete network request
to host "10.0.0.200". Error reading data from the connection "

and the connection is lost.

Is it a bug, and is there a workaround?

TIA Ian Newby

The applicable data definition 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,
CONSTRAINT 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 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 STREAMMENU 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;

CREATE VIEW FULLMENU (
CODE,
PARENT,
DESCRIPTION,
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, http://s.name
,cast('/servlets/uk.co.wmeng.intelus.KnowledgeStream?ACTION=DISPLAY&ID='
|| s.stream_id as varchar(100)),content_groups from streammenu m join
knowledgestreams s on s.stream_id = m.stream_id
;

====== Test Details ======

NB: all versions of 2.1 and 2.5 fail on 2nd query (issue 2002-jul-12) with message about
"too many contexts, max = 256" so this test checks only FB 3.0 and above.

Submitted by: Claudio Valderrama C. (robocop)

SFID: 223514#⁠
Submitted By: robocop

-----Original Message-----
From: DJK GreenSystems
Sent: Jueves 26 de Octubre de 2000 3:12
To: Claudio Valderrama C.
Subject: Re: Metadata

You should do the following:

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 * from 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)

I did have more problems. This is the most easy one to regenerate, because it can be done only with Interbase. Making changes to one of the procedures will lead that the system does not crash, but that it happened on other places.
[snip]
I would be very glad if this problem will be solved. We have a number of small customers, we want to work with Interbase. Please keep in touch.

Regards,
Dirk-Jan van der Kooij
-----End of Original Message-----

Now, what happens after the sequence of procedures is executed is simply that IB crashes due to a memory problem. Such sequence (see above) is shown again here:
select * from GET_ORDVRD(258,1);
select * from INKORDVRDSTATUS(258,1);
select * from GET_ORDVRD(258,1);

Example database will be available at
http://www.cvalde.com/bugs/gstest3.zip
Please try it as confidential. The person that sent me it several months ago gave me permission to share it but he doesn't want to show his business logic to all the world, of course. I had to post the backup because the error can't be reproduced by simply preparing the execution of procedures; they have to be run with minimal data.

C.

Submitted by: Claudio Valderrama C. (robocop)

Assigned to: @ArnoBrinkman

SFID: 224810#⁠
Submitted By: robocop

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
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 different 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 produce 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

and you'll observe that effectively, the DISTINCT applied to the result of the JOIN, leaving each table name only once. I consider it to be a bug, since other engines (MsSql for example) produced the same result with both statements. Comments?

C.

Submitted by: Claudio Valderrama C. (robocop)

Assigned to: Claudio Valderrama C. (robocop)

SFID: 217042#⁠
Submitted By: robocop

1) create table t0(check(0=0))
How can a table constraint be made without any field? I thought at least one field should be defined.

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.

4) create domain dint int default 'hello' not null
This is only a small example. IB never validates the default for any type of field. I can insert any kind of crap as default for domains of any datatype and also for fields defined inside a table.

Submitted by: Alice F. Bird (firebirds)

Assigned to: tamlin (tamlin)

SFID: 211790#⁠
Submitted By: nobody

Guardian window shows dates of 2000 year as dd/mm/100
instead of dd/mm/00.

Submitted by: Claudio Valderrama C. (robocop)

Assigned to: brodsom (brodsom)

SFID: 223126#⁠
Submitted By: robocop

"Jeff Overcash (TeamB)" mailto:<[email protected]> wrote in message

mailto:news:[email protected]...
> There is also a bug (also in isql I believe) that if you override a domain's
> constraints or default and also override the collate the collate will be
> extracted before the default or constraint. I fixed that also.
>
> "Claudio Valderrama C." wrote:
> [snip]

I can confirm this with an isql session:

SQL> show domain extract_bug;
EXTRACT_BUG
CHAR(1) CHARACTER SET ISO8859_1
COLLATE ES_ES Nullable
default 'v'
check(value >='a' and value <='z')

SQL> show table t_extract_bug;
A (EXTRACT_BUG) CHAR(1) CHARACTER SET ISO8859_1
COLLATE PT_PT Nullable
default 'w'
check(value >='a' and value <='z')
CONSTRAINT INTEG_60:
check(a>='c')

The SHOW command may be forgiven, but the extract command uses the same routines:

isql -x c:\proy\jeff.gdb
=>
CREATE DOMAIN "EXTRACT_BUG" AS
CHAR(1) CHARACTER SET ISO8859_1
default 'v'
check(value >='a' and value <='z')
COLLATE ES_ES;
[snip]
/* Table: T_EXTRACT_BUG, Owner: SYSDBA */
CREATE TABLE "T_EXTRACT_BUG"
("A" "EXTRACT_BUG" COLLATE PT_PT default 'w');
[snip]
ALTER TABLE "T_EXTRACT_BUG" ADD
check(a>='c');

As you can see, at the field definition level, the COLLATE clause happens before the DEFAULT clause and IB won't accept this syntax, so the script has to be fixed before it can be reused. The same happens if a field is created and the NOT NULL constraint is added along with a different charset:
SQL> show table t_extract_bug2;
A (EXTRACT_BUG) CHAR(1) CHARACTER SET ISO8859_1
COLLATE PT_PT Not Null default 'v'
check(value >='a' and value <='z')

Worse, in this case, not only the COLLATE clause appears first, but that the order of the NOT NULL constraint and the DEFAULT clause are reversed and IB will reject the definition even if we take off the collation, because IB expects the DEFAULT to come before NOT NULL:

SQL> show table t_extract_bug3;
A (EXTRACT_BUG) CHAR(1) CHARACTER SET ISO8859_1
COLLATE PT_PT Not Null default '8'
check(value >='a' and value <='z')
In this case, IBConsole (and not isql) preserved the right order between DEFAULT and NOT NULL

Thanks to Jeff Overcash for helping to uncover this issue.

C.

Submitted by: Alice F. Bird (firebirds)

Assigned to: @ArnoBrinkman

SFID: 221921#⁠
Submitted By: nobody

Take the following example of a self-referential table and a sproc that returns the children of a specified 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 integer) as
begin
for select "MainTypes".code, Min("ChildTypes".code) from
"ExampleTable" "MainTypes" left join "ExampleTable" "ChildTypes" on "MainTypes".code = "ChildTypes".parent
where "MainTypes".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 "ExampleTable" 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 into "ExampleTable" values (8,'BC',5);
insert into "ExampleTable" values (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 correctly returns AD,AC,AB,AA

In my real system however, neither ORDER BY NAME or ORDER BY NAME DESC has any effect */

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

IB 6.01, W2k SP1, IBConsole 319. Dialect 3 database.
mailto:[email protected]

Submitted by: Claudio Valderrama C. (robocop)

SFID: 212328#⁠
Submitted By: robocop

An internal test release of IBGuard with the name of iscGuard (by Mike) presents a decent date and time formatting in the crash report. No more Y2K problems in the report (IBGuard's properties). This test program closed two handle leaks, but there are still two more, probably produced when IB crashes and it's restarted by the Guardian, because the old handles are not released.
(IMHO, some parts of IBGuard that I reviewed through the web interface, seems to have been done by a sophomore and not by a professional developer.)

Submitted by: Alice F. Bird (firebirds)

Assigned to: @ArnoBrinkman

SFID: 213859#⁠
Submitted By: nobody

Given a table test which has the colums id integer (unique) and num integer.

Insert some records (~40.000 in my case). Most of the numbers in column num are distinct, only some are two ore three times repeated.

The following query fails:

Select id, num from test where num in (select num from test group by num having count(num > 1))

What is expected: those numbers WITH ID's which occur more than one time in the database.

Note: * Each query for it's own succeeds.
* Preparing column num in a way that select num from test group by num having count(num > 1) would return only one row and rewritting the statement as Select id, num from test where num = (select num from test group by num having count(num > 1)) succeeds too.

Tested with MS Access, MS-Sql, Oracle; same data, query finished within three seconds in any case.

Submitted by: dbecker (dbecker)

SFID: 216733#⁠
Submitted By: dbecker

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 generators 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 size 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.

For example try the following sequence of events:

CREATE DATABASE "development:c:\temp\gen_bug.gdb" USER "SYSDBA" PASSWORD "masterkey" PAGE_SIZE=1024;

CREATE GENERATOR GEN_1;
SELECT GEN_ID(GEN_1, 1) FROM RDB$DATABASE;

 GEN\_ID 

===========
1

CREATE GENERATOR GEN_2;
SELECT GEN_ID(GEN_2, 1) FROM RDB$DATABASE;

 GEN\_ID 

===========
1

... and so on ...

CREATE GENERATOR GEN_116;
SELECT GEN_ID(GEN_116, 1) FROM RDB$DATABASE;

 GEN\_ID 

===========
809041926

Notice the random value returned on the 116th generator. Every generator beyond this one will return such bogus data as it reads from page locations in the database which do not contain generators. When it writes this data back to the page, it corrupts the existing data.

NOTE: The exact point at which you start to see bad data varies depending on the other data in your database, and other factors. You may need to run this test several times before you start to see bad data cropping up if you test in a brand new database. An existing database with data will probably show bad results right away.

QUICK FIX: Put range checking in place and return an error when the storage space for generators is exhausted.

LONG-TERM FIX: Revamp the generators mechanism to allow it to allocate and use more pages as needed.

Submitted by: kattunga (kattunga)

Assigned to: Claudio Valderrama C. (robocop)

SFID: 221589#⁠
Submitted By: kattunga

if you do for example:

select field1 * field2 from mytable
or
select field1 * (1+field2/100) from mytable

and both fields are numeric type (interger)
like numeric(9,2), the result are incorrect.

christian

Submitted by: Claudio Valderrama C. (robocop)

SFID: 225219#⁠
Submitted By: robocop

According to the documentation:
??
The CHECK constraint in a domain definition sets a <dom_search_condition> that must be true for data entered into columns based on the domain. The CHECK constraint cannot reference any domain or column.
??

So, this should be banned:

create domain d_ontable int
check(value>(select rdb$relation_id from rdb$database))

Now what happens with a restore if the domain's check constraint is based on a user table? (And not a system table, where it could work eventually.)

Markus Kemper discovered this issue initially but he wrote that isql was unable to extract info from it, because he though it was an isql limitation.

This is only geeky detail or joke, a bit unrelated with the previous: please see how useful is this domain:
create domain value_gt_value int
check(value>value)

C.

Submitted by: Alice F. Bird (firebirds)

Assigned to: tamlin (tamlin)

SFID: 213462#⁠
Submitted By: nobody

Win 32 allows clients to connect with either servername:c:<localpathname> or servername:c:\<localpathname>. If connected clients have connected with a mixture of these string formats, the database is treated as if it were multiple databases and corruptions occur.

Submitted by: tamlin (tamlin)

SFID: 211781#⁠
Submitted By: tamlin

Allocation location is in SVC_start, last param
'&service->svc_handle'.

Submitted by: Claudio Valderrama C. (robocop)

Assigned to: Claudio Valderrama C. (robocop)

SFID: 225283#⁠
Submitted By: robocop

Hello, I've decoupled this issue from Bug #⁠122376 that I logged. Frank appended this case, but I think it's critical so it deserves its own separate report and priority. If a sort makes some fields with values to appear with NULL instead, I think it's a rather serious issue:

----------Begin of original post-----------
From: Jan Bakuwel mailto:<[email protected]>
Subject: InterBase 6 - serious bug or "feature" ?
Date: Sun, 12 Nov 2000 20:52:27 +0100

Hoi all,

Perhaps it's my lack of knowledge of the SQL92 standard, however I think
I ran into a very serious bug in InterBase. Any ideas, hints, etc are
very welcome (this will stop me from applying InterBase successfully).

Consider a database with 5 tables and a couple of views, one of those
views is called v_observation. Consider two queries (you can try them
with IBConsole):

select * from v_observation

select * from v_observation order by iobservationagree desc

These two queries return different (!) results. And not just the
order.... It seems InterBase has trouble dealing with NULL values that
are returned and, in the second case, completely omit the infomation
retrieved from the database. I have the same database in Access ... and
everything works perfect (exactly like expected) there!
You don't believe it ? You can download the database here:

http://www.greenpeace.org/~jbakuwel/db_linux.zip (gzipped)
http://www.greenpeace.org/~jbakuwel/db_win.zip (zipped)

Hope to hear from some of you....if not too much trouble please also
send you reply to my email address...

kind regards,
Jan
Greenpeace- Information Technology
------------------End of original post-----------

My comments now, extracted from the answer I did in the NGs:

I have narrowed the case to:
select IOBSERVATIONAGREE
from v_observation
order by 1

The same bug can be seen with:
select distinct IOBSERVATIONAGREE
from v_observation

And you get nicer <g> results with:
select IOBSERVATIONAGREE
from v_observation
group by iobservationagree

So the one that works is simply:
select IOBSERVATIONAGREE
from v_observation

In light of the examples I've posted above, I declare this to be a bug... unless I missed something really obvious. At first glance, the only difference when the ORDER BY clause is put is the SORT that appears in the query plan. Otherwise, the rest of the plan, including the nested JOINs, is the same.

I think IB was too tired to do the sorting after so many joins that it optimized the values towards NULL. <g>

This is the ugly workaround: replace the final view v_observation by a selectable stored procedure that does the same:

set term ^;
create procedure test
returns(IOBSERVATIONID int,
ILEAGUEID int,
IUSERID int,
ICATEGORYID int,
DSUBMITTED timestamp,
MOBSERVATION blob sub_type text,
MQUESTION blob sub_type text,
MSUGGESTION blob sub_type text,
MBACKGROUNDINFO blob sub_type text,
MOFFICIALRESPONSE blob sub_type text,
IOBSERVATIONAGREE int,
IOBSERVATIONNEUTRAL int,
IOBSERVATIONDISAGREE int,
IQUESTIONAGREE int,
IQUESTIONNEUTRAL int,
IQUESTIONDISAGREE int,
ISUGGESTIONAGREE int,
ISUGGESTIONNEUTRAL int,
ISUGGESTIONDISAGREE int,
IWORKGROUPAGREE int,
IWORKGROUPNEUTRAL int,
IWORKGROUPDISAGREE int)
as begin
for SELECT
t_Observation.*,
v_ObservationAgree.iObservationAgree,
v_ObservationNeutral.iObservationNeutral,
v_ObservationDisagree.iObservationDisagree,
v_QuestionAgree.iQuestionAgree, v_QuestionNeutral.iQuestionNeutral,
v_QuestionDisagree.iQuestionDisagree,
v_SuggestionAgree.iSuggestionAgree, v_SuggestionNeutral.iSuggestionNeutral,
v_SuggestionDisagree.iSuggestionDisagree,
v_WorkgroupAgree.iWorkgroupAgree, v_WorkgroupNeutral.iWorkgroupNeutral,
v_WorkgroupDisagree.iWorkgroupDisagree
FROM (((((((((((t_Observation
LEFT JOIN v_ObservationAgree ON t_Observation.iObservationID =
v_ObservationAgree.iObservationID)
LEFT JOIN v_ObservationNeutral ON t_Observation.iObservationID =
v_ObservationNeutral.iObservationID)
LEFT JOIN v_ObservationDisagree ON t_Observation.iObservationID =
v_ObservationDisagree.iObservationID)
LEFT JOIN v_QuestionAgree ON t_Observation.iObservationID =
v_QuestionAgree.iObservationID)
LEFT JOIN v_QuestionNeutral ON t_Observation.iObservationID =
v_QuestionNeutral.iObservationID)
LEFT JOIN v_QuestionDisagree ON t_Observation.iObservationID =
v_QuestionDisagree.iObservationID)
LEFT JOIN v_SuggestionAgree ON t_Observation.iObservationID =
v_SuggestionAgree.iObservationID)
LEFT JOIN v_SuggestionNeutral ON t_Observation.iObservationID =
v_SuggestionNeutral.iObservationID)
LEFT JOIN v_SuggestionDisagree ON t_Observation.iObservationID =
v_SuggestionDisagree.iObservationID)
LEFT JOIN v_WorkgroupAgree ON t_Observation.iObservationID =
v_WorkgroupAgree.iObservationID)
LEFT JOIN v_WorkgroupNeutral ON t_Observation.iObservationID =
v_WorkgroupNeutral.iObservationID)
LEFT JOIN v_WorkgroupDisagree ON t_Observation.iObservationID =
v_WorkgroupDisagree.iObservationID
into :IOBSERVATIONID,
:ILEAGUEID,
:IUSERID,
:ICATEGORYID,
:DSUBMITTED,
:MOBSERVATION,
:MQUESTION,
:MSUGGESTION,
:MBACKGROUNDINFO,
:MOFFICIALRESPONSE,
:IOBSERVATIONAGREE,
:IOBSERVATIONNEUTRAL,
:IOBSERVATIONDISAGREE,
:IQUESTIONAGREE,
:IQUESTIONNEUTRAL,
:IQUESTIONDISAGREE,
:ISUGGESTIONAGREE,
:ISUGGESTIONNEUTRAL,
:ISUGGESTIONDISAGREE,
:IWORKGROUPAGREE,
:IWORKGROUPNEUTRAL,
:IWORKGROUPDISAGREE
do suspend;
end ^;
set term ;^

I still think the parenthesis are redundant, since I tried without them and got (apparently) the same results. After all, you have only LEFT JOINs.

Now,
a) select * from test
b) select * from test
order by iobservationagree desc
c) select * from test
order by iobservationagree asc
d) select distinct iobservationagree from test

seem to be all correct, except that IB always sorts NULLs at the tail. This is a known behavior. The trick is to fool the fooler.

If the db is no longer available at
http://www.greenpeace.org/~jbakuwel/db_win.zip
there will be a copy of it at
http://www.cvalde.com/bugs/db_win.zip
where I include the SP "test" that does work.

C.

Submitted by: Claudio Valderrama C. (robocop)

Block progress on CORE82

Votes: 1

SFID: 222375#⁠
Submitted By: robocop

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 unedited 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 second 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, oh what a brilliant person I am.
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, what LangRef says could be refurbished as:
<Some objects, such as security class names, are restricted in practice to 27 bytes in length.>

The final part comes from Ivan Prenosil:

Or perhaps something like

I shortly played with these things and found out that
-when creating new table, two entries are inserted into rdb$security_classes, one SQL$tab_name, one SQL$DEFAULTx.
-so I tried to create new table with name DEFAULT1 and it overwrote already existing row in rdb$security_classes;
I do not know what exact consequencies it will cause though.

C. (On behalf of Ivan, too.)

Commits: a37aa2f

Submitted by: andycanfield (andycanfield)

SFID: 224129#⁠
Submitted By: andycanfield

I downloaded fbinst_prod_110300-2.exe from SourceForge. I ran the .exe it on my computer, which is Windows 98. It is unsuccessful; it has errors. I suspect that the cause of the error is that whoever packaged fbinst_prod_110300-2.exe assumed Windows NT.

(Previously I had installed ib_client on my machine, and found out the hard way that one must uninstall any InterBase package before installing Firebird; Firebird has an identity problem and gets confused. The errors here refer to after I thoroughly removed both Interbase and Firebird from my system and then tried to install Firebird again).

The install left an error message n a DOS window named "Finished - net": "Error 2185: The service name is invalid. Make sure you are specifying a valid service name, and then try again."

I ran IBConsole. When I try to contact the local server, it gives this error message: "Error logging into the requested server. Cannot attach to services manager". Ctrl+Alt+Del does not show a server running.

The documentation says "To start a server that has been shut down, run InterBase Server from the InterBase 5 Start menu." There is no Interase Server on the Windows 98 Start menu; under Programs / Firebird there are only IBConsole and License Agreement. MSCONFIG does not show any database server to be run upon startup. I presume that the command to start the server has been lost. I go to C:\Program Files\Filebird\bin and double click on ibserver.exe and now I have an icon on my task bar.

I run IBConsole again and try to log in to the local server; now things seem to be OK.

So the problem is that Firebird, in order to set the server to start automatically at boot, is trying to contact a services manager, which fails under Windows 98. The Firebird install needs to check the Windows version, and use an alternative method - e.g. add C:\Program Files\Firebird\bin\ibserver.exe to the StartUp menu - for Windows 95/98/ME.