search

FirebirdSQL / firebird

Firebird server, client and tools
https://www.firebirdsql.org/
1.26k stars 216 forks source link

Index made on unedited column doesn't work [CORE5556] #5823

Open firebird-automations opened 7 years ago

firebird-automations commented 7 years ago

Submitted by: Přemysl Šťastný (stastnypremysl)

I had defined this table, which has a few row: create table ODB_DOC_PRODUKTY_PARAMETRY ( PKID PKID, PARAMETR PARAMETR, HODNOTA TEXT5000_UTF, ZAVISLOST_PARAMETR PARAMETR, ZAVISLOST_HODNOTA TEXT5000_UTF, EDITOVATELNOST LOGICAL3, constraint ODB_DOC_PRODUKTY_PARAMETRY_PKID primary key (PKID))

I executed this script: set term ^; create domain DOCSKUPINA varchar(50) default 'ALL' not null^ alter table ODB_DOC_PRODUKTY_PARAMETRY add SKUPINA DOCSKUPINA^ create index ODB_DOC_SKUP_PARAM_PARAM on ODB_DOC_PRODUKTY_PARAMETRY (SKUPINA, PARAMETR)^ create index ODB_DOC_SKUP_PARAM_ZAVISLOST on ODB_DOC_PRODUKTY_PARAMETRY (SKUPINA, ZAVISLOST_PARAMETR)^ commit^ set term ;^

Now, when I try to: select A.SKUPINA from ODB_DOC_PRODUKTY_PARAMETRY A where A.SKUPINA = 'ALL';

I get nothing.

When I insert into ODB_DOC_PRODUKTY_PARAMETRY(PARAMETR) values ('para')

I get only the inserted row.

But when I deactivate indexes or update ODB_DOC_PRODUKTY_PARAMETRY A set A.SKUPINA = 'ALL';

I get all the rows.

_________________________________________________________________________________________ Better example: set term ^; create table TRASH ( PKID int )^ commit^

insert into TRASH(PKID) values (1)^ insert into TRASH(PKID) values (2)^ insert into TRASH(PKID) values (3)^ insert into TRASH(PKID) values (4)^ insert into TRASH(PKID) values (5)^ commit^

create domain CCSKUPINA varchar(50) default 'ALL' not null^ alter table TRASH add SKUPINA CCSKUPINA^ create index TRASH_PARAM on TRASH (SKUPINA, PKID)^ commit^

select*from TRASH A where A.SKUPINA='ALL'^

set term ;^

I should get all 5 rows, but I get nothing.

firebird-automations commented 7 years ago
Modified by: Přemysl Šťastný (stastnypremysl) description: I had defined this table, which has a few row: create table ODB\_DOC\_PRODUKTY\_PARAMETRY \( PKID PKID, PARAMETR PARAMETR, HODNOTA TEXT5000\_UTF, ZAVISLOST\_PARAMETR PARAMETR, ZAVISLOST\_HODNOTA TEXT5000\_UTF, EDITOVATELNOST LOGICAL3, constraint ODB\_DOC\_PRODUKTY\_PARAMETRY\_PKID primary key \(PKID\)\) I executed this script: set term ^; create domain DOCSKUPINA varchar\(50\) default 'ALL' not null^ alter table ODB\_DOC\_PRODUKTY\_PARAMETRY add SKUPINA DOCSKUPINA^ create index ODB\_DOC\_SKUP\_PARAM\_PARAM on ODB\_DOC\_PRODUKTY\_PARAMETRY \(SKUPINA, PARAMETR\)^ create index ODB\_DOC\_SKUP\_PARAM\_ZAVISLOST on ODB\_DOC\_PRODUKTY\_PARAMETRY \(SKUPINA, ZAVISLOST\_PARAMETR\)^ commit^ set term ;^ Now, when I try to: select A\.SKUPINA from ODB\_DOC\_PRODUKTY\_PARAMETRY A where A\.SKUPINA = 'ALL'; I get nothing\. When I insert into ODB\_DOC\_PRODUKTY\_PARAMETRY\(PARAMETR\) values \('para'\) I get only the inserted row\. But when I deactivate indexes or update ODB\_DOC\_PRODUKTY\_PARAMETRY A set A\.SKUPINA = 'ALL'; I get all the rows\. =\> I had defined this table, which has a few row: create table ODB\_DOC\_PRODUKTY\_PARAMETRY \( PKID PKID, PARAMETR PARAMETR, HODNOTA TEXT5000\_UTF, ZAVISLOST\_PARAMETR PARAMETR, ZAVISLOST\_HODNOTA TEXT5000\_UTF, EDITOVATELNOST LOGICAL3, constraint ODB\_DOC\_PRODUKTY\_PARAMETRY\_PKID primary key \(PKID\)\) I executed this script: set term ^; create domain DOCSKUPINA varchar\(50\) default 'ALL' not null^ alter table ODB\_DOC\_PRODUKTY\_PARAMETRY add SKUPINA DOCSKUPINA^ create index ODB\_DOC\_SKUP\_PARAM\_PARAM on ODB\_DOC\_PRODUKTY\_PARAMETRY \(SKUPINA, PARAMETR\)^ create index ODB\_DOC\_SKUP\_PARAM\_ZAVISLOST on ODB\_DOC\_PRODUKTY\_PARAMETRY \(SKUPINA, ZAVISLOST\_PARAMETR\)^ commit^ set term ;^ Now, when I try to: select A\.SKUPINA from ODB\_DOC\_PRODUKTY\_PARAMETRY A where A\.SKUPINA = 'ALL'; I get nothing\. When I insert into ODB\_DOC\_PRODUKTY\_PARAMETRY\(PARAMETR\) values \('para'\) I get only the inserted row\. But when I deactivate indexes or update ODB\_DOC\_PRODUKTY\_PARAMETRY A set A\.SKUPINA = 'ALL'; I get all the rows\. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Better example: set term ^; create table TRASH \( PKID int \)^ commit^ insert into TRASH\(PKID\) values \(1\)^ insert into TRASH\(PKID\) values \(2\)^ insert into TRASH\(PKID\) values \(3\)^ insert into TRASH\(PKID\) values \(4\)^ insert into TRASH\(PKID\) values \(5\)^ commit^ create domain CCSKUPINA varchar\(50\) default 'ALL' not null^ alter table TRASH add SKUPINA CCSKUPINA^ create index TRASH\_PARAM on TRASH \(SKUPINA, PKID\)^ commit^ select\*from TRASH A where A\.SKUPINA='ALL'^ set term ;^
firebird-automations commented 7 years ago
Modified by: Přemysl Šťastný (stastnypremysl) description: I had defined this table, which has a few row: create table ODB\_DOC\_PRODUKTY\_PARAMETRY \( PKID PKID, PARAMETR PARAMETR, HODNOTA TEXT5000\_UTF, ZAVISLOST\_PARAMETR PARAMETR, ZAVISLOST\_HODNOTA TEXT5000\_UTF, EDITOVATELNOST LOGICAL3, constraint ODB\_DOC\_PRODUKTY\_PARAMETRY\_PKID primary key \(PKID\)\) I executed this script: set term ^; create domain DOCSKUPINA varchar\(50\) default 'ALL' not null^ alter table ODB\_DOC\_PRODUKTY\_PARAMETRY add SKUPINA DOCSKUPINA^ create index ODB\_DOC\_SKUP\_PARAM\_PARAM on ODB\_DOC\_PRODUKTY\_PARAMETRY \(SKUPINA, PARAMETR\)^ create index ODB\_DOC\_SKUP\_PARAM\_ZAVISLOST on ODB\_DOC\_PRODUKTY\_PARAMETRY \(SKUPINA, ZAVISLOST\_PARAMETR\)^ commit^ set term ;^ Now, when I try to: select A\.SKUPINA from ODB\_DOC\_PRODUKTY\_PARAMETRY A where A\.SKUPINA = 'ALL'; I get nothing\. When I insert into ODB\_DOC\_PRODUKTY\_PARAMETRY\(PARAMETR\) values \('para'\) I get only the inserted row\. But when I deactivate indexes or update ODB\_DOC\_PRODUKTY\_PARAMETRY A set A\.SKUPINA = 'ALL'; I get all the rows\. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Better example: set term ^; create table TRASH \( PKID int \)^ commit^ insert into TRASH\(PKID\) values \(1\)^ insert into TRASH\(PKID\) values \(2\)^ insert into TRASH\(PKID\) values \(3\)^ insert into TRASH\(PKID\) values \(4\)^ insert into TRASH\(PKID\) values \(5\)^ commit^ create domain CCSKUPINA varchar\(50\) default 'ALL' not null^ alter table TRASH add SKUPINA CCSKUPINA^ create index TRASH\_PARAM on TRASH \(SKUPINA, PKID\)^ commit^ select\*from TRASH A where A\.SKUPINA='ALL'^ set term ;^ =\> I had defined this table, which has a few row: create table ODB\_DOC\_PRODUKTY\_PARAMETRY \( PKID PKID, PARAMETR PARAMETR, HODNOTA TEXT5000\_UTF, ZAVISLOST\_PARAMETR PARAMETR, ZAVISLOST\_HODNOTA TEXT5000\_UTF, EDITOVATELNOST LOGICAL3, constraint ODB\_DOC\_PRODUKTY\_PARAMETRY\_PKID primary key \(PKID\)\) I executed this script: set term ^; create domain DOCSKUPINA varchar\(50\) default 'ALL' not null^ alter table ODB\_DOC\_PRODUKTY\_PARAMETRY add SKUPINA DOCSKUPINA^ create index ODB\_DOC\_SKUP\_PARAM\_PARAM on ODB\_DOC\_PRODUKTY\_PARAMETRY \(SKUPINA, PARAMETR\)^ create index ODB\_DOC\_SKUP\_PARAM\_ZAVISLOST on ODB\_DOC\_PRODUKTY\_PARAMETRY \(SKUPINA, ZAVISLOST\_PARAMETR\)^ commit^ set term ;^ Now, when I try to: select A\.SKUPINA from ODB\_DOC\_PRODUKTY\_PARAMETRY A where A\.SKUPINA = 'ALL'; I get nothing\. When I insert into ODB\_DOC\_PRODUKTY\_PARAMETRY\(PARAMETR\) values \('para'\) I get only the inserted row\. But when I deactivate indexes or update ODB\_DOC\_PRODUKTY\_PARAMETRY A set A\.SKUPINA = 'ALL'; I get all the rows\. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Better example: set term ^; create table TRASH \( PKID int \)^ commit^ insert into TRASH\(PKID\) values \(1\)^ insert into TRASH\(PKID\) values \(2\)^ insert into TRASH\(PKID\) values \(3\)^ insert into TRASH\(PKID\) values \(4\)^ insert into TRASH\(PKID\) values \(5\)^ commit^ create domain CCSKUPINA varchar\(50\) default 'ALL' not null^ alter table TRASH add SKUPINA CCSKUPINA^ create index TRASH\_PARAM on TRASH \(SKUPINA, PKID\)^ commit^ select\*from TRASH A where A\.SKUPINA='ALL'^ set term ;^ I should get all 5 rows, but I get nothing\.
firebird-automations commented 7 years ago

Commented by: @asfernandes

Please test FB 3.0.