Submitted by: creynolds (creynolds)

SFID: 483795#⁠
Submitted By: creynolds

Given the following table and view definition:

CREATE TABLE table1 (
keyfield INTEGER NOT NULL,
field1 INTEGER NOT NULL);

CREATE TABLE table2 (
keyfield INTEGER NOT NULL,
field2 INTEGER NOT NULL);

CREATE VIEW test1 ( keyfield, field1 ) AS
SELECT keyfield, field1
FROM table1
UNION
SELECT keyfield, field1
FROM table1;

CREATE VIEW test2 ( keyfield, field2 ) AS
SELECT keyfield, field2
FROM table2
UNION
SELECT keyfield, field2
FROM table2;

the following statement crashes the server:

SELECT *
FROM test1 i, test2 j
WHERE i.keyfield = j.keyfield
AND j.field2 = (SELECT MAX(x.field2) FROM test2 x)

Claudio's comments on this bug:

Finally I got to the right place. The problem happens
in the optimizer, in the routine gen_sort_merge(),
when it calls a routine named computable() during the
prepare phase. After that routine calls itself 4 times
recursively, it finds a null pointer and game over,
take your bags and go to home. A node pointer with the
value 0xb is no fun for the operating system, that
kills the process:

for (end = ptr + node->nod_count; ptr < end; ptr++)
if (!computable (csb, *ptr, stream, idx_use))
return FALSE;

ptr is okay, but *ptr is 0xb, hence it enter a new
recursive call with "node" argument being 0xb, and the
first line does DEV_BLKCHK (node, type_nod); that will
check node->nod_type == type_nod. Of course, 0xb is
not a valid address to be dereferenced, hence goodbye.

It seems that the server didn't like the total absence
of indices.
:-)

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

Original test:
https://github.com/FirebirdSQL/fbtcs/blob/master/GTCS/tests/CF_ISQL_24.script