Hello All

I have two tables:

TABLE H_TIMESHEETS_H (
ID INTEGER NOT NULL
, WORK_DATE DATE
, CONTRACT_ID INTEGER
, WORKER_ID VARCHAR( 100 )
, HOURS NUMERIC ( 9, 2 )

CONSTRAINT PK_H_TIMESHEETS_H PRIMARY KEY (ID);

INDEX H_TIMESHEETS_H_C ON H_TIMESHEETS_H (CONTRACT_ID);
INDEX H_TIMESHEETS_H_DATE ON H_TIMESHEETS_H (WORK_DATE);


TABLE H_CONTRACTS (
CONTRACT_ID INTEGER
, CONTRACT_NAME VARCHAR( 50 )
, CONTRACT_STATUS INTEGER
, SUPERVISOR_ID VARCHAR( 100 )
, WORK_SITE_ID VARCHAR( 100 )

CONSTRAINT PK_H_CONTRACTS PRIMARY KEY (CONTRACT_ID);
INDEX H_CONTRACTS_SS ON H_CONTRACTS (SUPERVISOR_ID, CONTRACT_STATUS);

Firebird is 2.5.0

For the statement:

WITH CONTRACT_HOURS
AS
(SELECT C.CONTRACT_ID, C.CONTRACT_NAME, SUM(TSH.HOURS) S_HOURS
FROM H_TIMESHEETS_H TSH
INNER JOIN H_CONTRACTS C
ON (C.CONTRACT_ID = TSH.CONTRACT_ID
AND C.SUPERVISOR_ID = :S
AND C.CONTRACT_STATUS >= 0)
WHERE TSH.WORK_DATE BETWEEN :D1 AND :D2
GROUP BY 1,2)
SELECT LIST(CH.CONTRACT_NAME || '=ID=' || CH.CONTRACT_ID || ';L=' ||
CH.S_HOURS, ASCII_CHAR(13)||ASCII_CHAR(10))
FROM CONTRACT_HOURS CH

Firebird returns plan:
PLAN SORT (JOIN (CH TSH INDEX (H_TIMESHEETS_H_DATE), CH C INDEX
(PK_H_CONTRACTS)))


For the statement:

WITH CONTRACT_HOURS
AS
(SELECT C.CONTRACT_ID, C.CONTRACT_NAME, SUM(TSH.HOURS) S_HOURS
FROM H_TIMESHEETS_H TSH
INNER JOIN H_CONTRACTS C
ON (C.CONTRACT_ID = TSH.CONTRACT_ID
AND C.SUPERVISOR_ID >= '' <- modified!!!
AND C.WORK_SITE_ID = :WS <- added!!!!
AND C.CONTRACT_STATUS >= 0)
WHERE TSH.WORK_DATE BETWEEN :D1 AND :D2
GROUP BY 1,2)
SELECT LIST(CH.CONTRACT_NAME || '=ID=' || CH.CONTRACT_ID || ';L=' ||
CH.S_HOURS, ASCII_CHAR(13)||ASCII_CHAR(10))
FROM CONTRACT_HOURS CH

Firebird returns plan
PLAN SORT (JOIN (CH C INDEX (H_CONTRACTS_SS), CH TSH INDEX
(H_TIMESHEETS_H_C, H_TIMESHEETS_H_DATE)))

which seems to proper for both statements.

When I extract 'SELECT...GROUP BY' part and execute this separately I
always get 'first' plan returned - for both versions.

Does anybody have a clue how to 'make' Firebird to use proper indexes?

TIA
Marcin