Issue Details (XML | Word | Printable)

Key: CORE-1153
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Critical Critical
Assignee: Adriano dos Santos Fernandes
Reporter: Umberto Masotti
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Activating index change "STARTING" working as "LIKE" in join condition

Created: 05/Mar/07 05:13 AM   Updated: 16/Aug/07 02:06 PM
Component/s: None
Affects Version/s: 2.0.0, 2.0.1
Fix Version/s: 2.1 Beta 1, 2.0.2

Time Tracking:
Not Specified

Environment:
Intel P4, Windows XP, tested with IBExpert 2006.01.29 and ISQL
Server Version: WI-V2.0.1.12810 Firebird 2.0
Server Implementation: Firebird/x86/Windows NT
Service Version: 2
Issue Links:
Relate
 


 Description  « Hide
This query:

select distinct mm.MATRICOLA, mm.UTENTE, dd.facolta, dd.cognome, dd.nome,
       dd.matricola, dd.DESCRIZIONE
from matricole mm
left outer join docenti dd
  on upper(mm.utente) starting upper(dd.COGNOME)
where Upper(mm.UTENTE) between upper('la') and upper ('lz')
order by mm.utente

has different result sets if a particular index is activated:
Adopted plan when not activated:
PLAN SORT (SORT (JOIN (MM INDEX (MATRICOLE_IDX2), DD NATURAL)))
result set has 152 records.

Adopted plan when activated
PLAN SORT (SORT (JOIN (MM INDEX (MATRICOLE_IDX2), DD INDEX (DOCENTI_COG_DOCENTI))))
result set has 43 records.

In the lesser result set, the clause "STARTING" works as "like", "equal" or "NOT DISTINCT"
I don't know if this is a real issue or STARTING is not actually supported in JOIN condition.
I can upload database if needed (fdb is less that 20MB)

The tables are:

First table:
CREATE TABLE DOCENTI (
    ANNO D_ANNO NOT NULL /* D_ANNO = NUMERIC(4,0) default 0 NOT NULL */,
    CODICECORSO D_CORSO NOT NULL /* D_CORSO = INTEGER NOT NULL */,
    PERIODO D_PERIODO NOT NULL /* D_PERIODO = SMALLINT */,
    INSEGNAMENTO D_INSEGNAMENTO NOT NULL /* D_INSEGNAMENTO = INTEGER */,
    FACOLTA D_FACOLTA NOT NULL /* D_FACOLTA = CHAR(2) NOT NULL */,
    DESCRIZIONE D_DESCRIZIONI COLLATE PXW_INTL850 /* D_DESCRIZIONI = VARCHAR(91) */,
    COGNOME D_DESCRIZIONI /* D_DESCRIZIONI = VARCHAR(91) */,
    NOME D_DESCRIZIONI /* D_DESCRIZIONI = VARCHAR(91) */,
    MEDIA D_PUNTEGGIO /* D_PUNTEGGIO = DECIMAL(5,2) DEFAULT 0 */,
    LETTUREVALIDE D_NUMLETTURE /* D_NUMLETTURE = INTEGER CHECK (VALUE >= 0) */,
    MATRICOLA CHAR(6) CHARACTER SET ASCII DEFAULT 000000
);

ALTER TABLE DOCENTI ADD CONSTRAINT PK_DOCENTI PRIMARY KEY (ANNO, PERIODO, CODICECORSO, INSEGNAMENTO)
USING INDEX IDX_DOCENTI;

this is the problematic index:
CREATE INDEX DOCENTI_COG_DOCENTI ON DOCENTI COMPUTED BY (upper(cognome));

This is the second table in the query

CREATE TABLE MATRICOLE (
    UTENTE VARCHAR(40),
    MATRICOLA CHAR(6),
    DOCENTE CHAR(1),
    TROVATO D_SI_NO /* D_SI_NO = CHAR(1) CHECK (VALUE IN ( 'S', 'N', '?', ' ' )) */
);
CREATE INDEX MATRICOLE_IDX1 ON MATRICOLE (MATRICOLA);
CREATE INDEX MATRICOLE_IDX2 ON MATRICOLE COMPUTED BY (UPPER(UTENTE));



 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 05/Mar/07 06:45 AM
Please attach a database or its backup (zipped)

Umberto Masotti added a comment - 05/Mar/07 12:28 PM
In this zipped backup, index DOCENTI_COG_DOCENTI is already active.

Note that table DOCENTI has other indexes not involved (I think) in this issue.

Adriano dos Santos Fernandes added a comment - 06/Mar/07 06:56 AM
This simple patch should fix the problem, but I'm not sure if the expression index code for index matching is correct for others operators too.

Index: Optimizer.cpp
===================================================================
RCS file: /cvsroot/firebird/firebird2/src/jrd/Optimizer.cpp,v
retrieving revision 1.71
diff -u -r1.71 Optimizer.cpp
--- Optimizer.cpp 12 Sep 2006 11:36:18 -0000 1.71
+++ Optimizer.cpp 6 Mar 2007 12:13:56 -0000
@@ -2215,7 +2215,7 @@
  if (!OPT_expression_equal(tdbb, optimizer, indexScratch->idx, match, stream) ||
  (value && !OPT_computable(optimizer->opt_csb, value, stream, true, false)))
  {
- if (value &&
+ if (boolean->nod_type != nod_starts && value &&
  OPT_expression_equal(tdbb, optimizer, indexScratch->idx, value, stream) &&
  OPT_computable(optimizer->opt_csb, match, stream, true, false))
  {


Pavel Cisar added a comment - 07/Jul/07 03:18 PM
Fix for 2.1 Beta 1 verified, test added. I also raised the priority to Critical as it's about returning wrong results. As such, it should be also backported to 2.0.2.

Adriano dos Santos Fernandes added a comment - 08/Jul/07 10:28 AM
Fix is backported to V2.0.2.