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-issue-importer opened this issue Mar 5, 2007 · 19 comments

Comments

@firebird-issue-importer
Copy link

@firebird-issue-importer firebird-issue-importer commented Mar 5, 2007

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 5, 2007

Commented by: @dyemanov

Please attach a database or its backup (zipped)

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 5, 2007

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 5, 2007

Modified by: Umberto Masotti (umasotti)

Attachment: STUDENTI_ANNI.zip [ 10291 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 6, 2007

Commented by: Umberto Masotti (umasotti)

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 6, 2007

Modified by: Umberto Masotti (umasotti)

Attachment: STUDENTI_2.zip [ 10300 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 6, 2007

Modified by: @dyemanov

Attachment: STUDENTI_ANNI.zip [ 10291 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 6, 2007

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 1, 2007

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 2.1.0 [ 10041 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 26, 2007

Modified by: @pcisar

Fix Version: 2.1 Beta 1 [ 10141 ]

Fix Version: 2.1.0 [ 10041 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 17, 2007

Modified by: @pcisar

Link: This issue is related to QA47 [ QA47 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 7, 2007

Modified by: @pcisar

Attachment: STUDENTI_2.zip [ 10300 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 7, 2007

Modified by: @pcisar

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

Fix Version: 2.0.2 [ 10130 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 7, 2007

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 8, 2007

Commented by: @asfernandes

Fix is backported to V2.0.2.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 8, 2007

Modified by: @asfernandes

Version: 2.0.0 [ 10091 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 16, 2007

Modified by: @pcisar

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 28, 2008

Modified by: @pcisar

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2016

Modified by: @pavel-zotov

QA Status: No test

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2016

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
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants