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
Comments
Commented by: @dyemanov Please attach a database or its backup (zipped) |
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. |
Modified by: Umberto Masotti (umasotti)Attachment: STUDENTI_ANNI.zip [ 10291 ] |
Commented by: Umberto Masotti (umasotti) Changing data, result doesn't change. Please administrators, delete previous attached database, because of privacy problems in data. |
Modified by: Umberto Masotti (umasotti)Attachment: STUDENTI_2.zip [ 10300 ] |
Modified by: @dyemanovAttachment: STUDENTI_ANNI.zip [ 10291 ] => |
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.cppRCS file: /cvsroot/firebird/firebird2/src/jrd/Optimizer.cpp,v |
Modified by: @asfernandesstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 2.1.0 [ 10041 ] assignee: Adriano dos Santos Fernandes [ asfernandes ] |
Modified by: @pcisarAttachment: STUDENTI_2.zip [ 10300 ] => |
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. |
Commented by: @asfernandes Fix is backported to V2.0.2. |
Modified by: @asfernandesVersion: 2.0.0 [ 10091 ] |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @pcisarWorkflow: jira [ 11662 ] => Firebird [ 15349 ] |
Modified by: @pavel-zotovQA Status: No test |
Modified by: @pavel-zotovQA Status: No test => Done successfully |
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
The text was updated successfully, but these errors were encountered: