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
Indexes for COMPUTED BY columns [CORE1212] #1636
Comments
Commented by: @ibaseru I suggest to disable any select statements in expression indices. So this feature request will not raise. |
Commented by: Alexander Tyutik (tut) But such bug exists in current indicies. You can run such command: CREATE INDEX TABLE1_IDX1 ON TABLE1 COMPUTED BY ((SELECT FIRST 1 ID FROM TABLE2)); and it will be compiled without problems. So, this problem also exists without COMPUTED BY fields |
Modified by: @pcisarWorkflow: jira [ 11884 ] => Firebird [ 15548 ] |
Commented by: Philip Williams (unordained) create table zang A few notes from my quick testing on FB 2.1 RC1 However, it seems that current_timestamp is evaluating to NULL (or worse, throwing an exception silently?) when the indexer runs and you wind up not being able to find these rows when it uses the index -- the only rows that will be found are "is_current = 0" (eff was not null and expr was not null) and "is_current = 1" (eff was null and expr was null); anything else can't be found by index, even looking for "is_current is null". I had test data with eff null and expr not null, etc. and those rows would only turn up if I tricked the optimizer into not using the index. This is despite the fact that with iif(), nulls in the expression should have resulted in a computed value of zero, and been indexed as such. It almost seems like the only reason rows can be found is the short-circuit evaluation of OR, but that's not right either; "false or ..." is not obvious, yet those rows can be found. select * from zang where is_current = 0; -- 10102 rows I'm not saying that current_timestamp is a good idea in a computed-by index (I really do know better, I was just testing the limits of 2.1 RC1) but it would be good for the documentation to say how this is handled and maybe why rows can't be found in my example; it may wind up being disallowed, but other cases involving custom UDFs wouldn't be so easy to disable. (I'm thinking about Oracle's "deterministic" flag on functions; you could hard-code that for current_timestamp, but *every* UDF? There are bound to be holes.) |
Commented by: Timo Partanen (partim) What makes the need for creating an index for a COMPUTED BY field more relevant is composite indexes (i.e. indexes that are made up of more than one field/column). Consider the following table. Is there any way to create a composite index for the PropertyDef column and an expression that computes the lowercase value of the Data column? CREATE TABLE PV_TEXT If the table contained a computed column Data_Lower such as "DATA_LOWER COMPUTED BY ( MF_UTF8LOWER( DATA ) )" creating a composite index for PropertyDef and Data_Lower should be possible. However, this fails: CREATE ASC INDEX IX_PV_TEXT_PD_DATA_LOWER (The error is "unsuccessful metadata update attempt to index COMPUTED BY column in INDEX IX_PV_TEXT_PD_DATA_LOWER".) |
Commented by: Sean Leyne (seanleyne) The most significant issue that I can see is that not all COMPUTED BY columns can/should be indexable. Consider that you have 2 tables: CREATE Product( CREATE SalesDetail( In the above case an index on SAD_PRO_Description would be invalid/should not be allowed. |
Commented by: Eric Bole-Feysot (qwench) I reported an issue with index on 'computed by' field. |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: Alexander Tyutik (tut)
Is related to CORE1173
Votes: 9
It will be very nice to have ability to create index for COMPUTED BY fields.
This is the same as index by expression, but expression in this case stored in COMPUTED BY column and programmer should not write it again and again.
The text was updated successfully, but these errors were encountered: