RANGE_HASHED works unnatural with overlapped ranges (open dates)

[den-crane]

Imagine we insert rows into a table with open end date. Today currencies, prices, discounts. We don't know the future.

create table prices(id UInt64, begin Date, end Date, price Float64) Engine= Memory;

insert into prices values(1, '2020-01-01', '2100-01-01', 1.0);
insert into prices values(1, '2020-01-02', '2100-01-01', 2.0);
insert into prices values(1, '2020-01-03', '2100-01-01', 3.0);

select * from prices　;

┌─id─┬──────begin─┬────────end─┬─price─┐
│  1 │ 2020-01-01 │ 2100-01-01 │     1 │
└────┴────────────┴────────────┴───────┘
┌─id─┬──────begin─┬────────end─┬─price─┐
│  1 │ 2020-01-02 │ 2100-01-01 │     2 │
└────┴────────────┴────────────┴───────┘
┌─id─┬──────begin─┬────────end─┬─price─┐
│  1 │ 2020-01-03 │ 2100-01-01 │     3 │
└────┴────────────┴────────────┴───────┘

CREATE DICTIONARY prices_dict
( id UInt64, begin Date, end Date, price Float64 )
PRIMARY KEY id
SOURCE(CLICKHOUSE(host 'localhost' port 9000 user 'default' password '' db 'default' table 'prices'
))
LAYOUT(RANGE_HASHED())
RANGE(MIN begin MAX end)
LIFETIME(1);


SELECT * FROM prices_dict
┌─id─┬──────begin─┬────────end─┬─price─┐
│  1 │ 2020-01-01 │ 2100-01-01 │     1 │
│  1 │ 2020-01-02 │ 2100-01-01 │     1 │ ---- I would expect 2
│  1 │ 2020-01-03 │ 2100-01-01 │     1 │ ---- I would expect 3
└────┴────────────┴────────────┴───────┘

select dictGet('default.prices_dict', 'price', toUInt64(1), toDate('2020-01-02')) x;
┌─x─┐
│ 1 │  --- expected 2
└───┘

It would be nice to have that behavior. Unfortunately some users can rely on the current behavior.
Maybe you should implement a new layout RANGE_HASHED_2. @alexey-milovidov ?

IT NOT A BUG. It's a feature of the implementation.

-- try to insert NULL dates as an ending
truncate table prices;
insert into prices values(1, '2020-01-01', toDate(0), 1.0);
insert into prices values(1, '2020-01-02', toDate(0), 2.0);
insert into prices values(1, '2020-01-03', toDate(0), 3.0);
SELECT * FROM prices_dict;
┌─id─┬──────begin─┬────────end─┬─price─┐
│  1 │ 2020-01-01 │ 1970-01-01 │     1 │
│  1 │ 2020-01-02 │ 1970-01-01 │     1 │
│  1 │ 2020-01-03 │ 1970-01-01 │     1 │
└────┴────────────┴────────────┴───────┘

-- try to insert NULL dates as a beginning
truncate table prices; 
insert into prices values(1, toDate(0), '2020-01-01', 1.0);
insert into prices values(1, toDate(0), '2020-01-02', 2.0);
insert into prices values(1, toDate(0), '2020-01-03', 3.0);
SELECT * FROM prices_dict;
┌─id─┬──────begin─┬────────end─┬─price─┐
│  1 │ 1970-01-01 │ 2020-01-01 │     1 │   
│  1 │ 1970-01-01 │ 2020-01-02 │     2 │
│  1 │ 1970-01-01 │ 2020-01-03 │     3 │
└────┴────────────┴────────────┴───────┘

-- try to insert open dates but not Null as a beginning
truncate table prices; 
insert into prices values(1, '2000-01-01', '2020-01-01', 1.0);
insert into prices values(1, '2000-01-01', '2020-01-02', 2.0);
insert into prices values(1, '2000-01-01', '2020-01-03', 3.0);

SELECT * FROM prices_dict;
┌─id─┬──────begin─┬────────end─┬─price─┐
│  1 │ 2000-01-01 │ 2020-01-01 │     1 │    here  
│  1 │ 2000-01-01 │ 2020-01-02 │     1 │        something
│  1 │ 2000-01-01 │ 2020-01-03 │     1 │                   unexpected
└────┴────────────┴────────────┴───────┘

[alexey-milovidov]

So, we need to choose the most precise (narrow) range in case of ambiguity?

[alexey-milovidov]

The most narrow range or just a range with the latest start point?

[den-crane]

......++++++++++........
.........+++++++++++++..    <----- the winner
...............+++++++++
            ^
            |
        searched 
          point