Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Activating index change "STARTING" working as "LIKE" in join condition [CORE1153] #1574

Closed
firebird-automations opened this issue Mar 5, 2007 · 19 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Umberto Masotti (umasotti)

Is related to QA47

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));

Commits: 99f976f 032de38

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Please attach a database or its backup (zipped)

@firebird-automations
Copy link
Collaborator Author

Commented by: Umberto Masotti (umasotti)

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: Umberto Masotti (umasotti)

Attachment: STUDENTI_ANNI.zip [ 10291 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Umberto Masotti (umasotti)

Changing data, result doesn't change. Please administrators, delete previous attached database, because of privacy problems in data.

@firebird-automations
Copy link
Collaborator Author

Modified by: Umberto Masotti (umasotti)

Attachment: STUDENTI_2.zip [ 10300 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Attachment: STUDENTI_ANNI.zip [ 10291 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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))
{

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.1.0 [ 10041 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Fix Version: 2.1 Beta 1 [ 10141 ]

Fix Version: 2.1.0 [ 10041 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA47 [ QA47 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Attachment: STUDENTI_2.zip [ 10300 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

priority: Major [ 3 ] => Critical [ 2 ]

Fix Version: 2.0.2 [ 10130 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Fix is backported to V2.0.2.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Version: 2.0.0 [ 10091 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 11662 ] => Firebird [ 15349 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test => Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment