Non-versioned fields should display NULL value for historical queries #60
Comments
midenok
added this to the
0.4 AS OF TRX_ID (directly specify transaction id in AS OF) milestone
|
Test case which doesn't work. create table t(
a int,
b int without system versioning
) engine innodb with system versioning;
insert into t values(1, 2);
select * from t;
select * from t for system_time as of timestamp now(6);
drop table t;Second query should return NULL instead of 2. |
|
Probably, it is better to detect whether query is really historical. Do you have any suggestions how to do it? |
|
If query has "for system_time" we need to replace non-versioned fields with NULL literals as soon as possible. And probably issue a warning to user. |
|
This is for sure. I mean by really historical that timestamp now() is not historical since it selects latest data. The code should detect if the query selects not latest data. |
create table t(
a int,
b int without system versioning
) with system versioning;
insert into t values(1, 2);
insert into t values(1, 3);
select count(*) from t group by b for system_time as of now(6);Should warn and return one row with count(*) = 2 because all b becomes NULL in this case. |
create table t(
a int,
b int without system versioning
) with system versioning;
insert into t values(1, 2);
insert into t values(3, 4);
select count(*) from t for system_time as of now(6) order by b asc;
select count(*) from t for system_time as of now(6) order by b desc;Order should be the same as if we would sort by NULL literal. Also warn. |
|
Can you just create special field item |
|
This is minor task and with risk of depreciation, so it should be fixed in a quick way. |
|
Item_null already does it. But we need to walk through all Item_field in query and replace it with Item_null if they're not versioned. Item_fields are created by parser but they're actually initialized in JOIN::prepare(). Specifically, we're interested in Item_field->field which we need to check. Another option is to replace nothing but check for unversioned fields and warn user that query results may be incorrect. What do you think? |
|
Where |
Fields of Those struct from parser are just names. We need to find column names in table and it happens here After all this stuff initialized with Field references it becomes possible to check where their corresponding fields are optimized. And it's time to replace them with Item_null. |
|
Checking whether Item_field::context initialized in a different places:
|
select a, b from t for system_time as of timestamp now(6)Watching |
select count(*) from t group by b for system_time as of timestamp now(6)Watching |
select * from t group by a having a=2 for system_time as of timestamp now(6)Watching |
select x from t1; |
|
I've considered two ways to solve this:
The first method is simple. It requires using a simple check over SELECT list, GROUP BY list, ORDER BY list, HAVING and WHERE list. Rewriting of a tree could be done using The second way to fix that is alter Internally
|
|
Pls, try the following:
|
|
Suggested solution works Fixed in 4f893dc |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
As continuation of discussion in #51 this is the least we can do to maintain value correctness.
The text was updated successfully, but these errors were encountered: