apache / apache/ignite

Unable to get correct result with view in 2.17

Open
#12,104 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
5.1k
Forks
1.9k
Avg merge
3d 2h
Merged PRs (30d)
46

Description

I describe the problem encountered below.
I create three tables and one view.

```
create table t_ClientInfo
(
Id char(12) not null,
Type char(1) not null,
Content char(20),
constraint pk_ClientInfo primary key(Id, type)
) with "cache_name=ClientInfo,affinity_key=Id";

insert into t_ClientInfo values ('1', 'a', 'testInfo1');
insert into t_ClientInfo values ('2', 'a', 'testInfo2');

create table t_ClientDep
(
Id char(12) not null,
ClientId char(15) not null,
isActive char(1),
constraint pk_ClientDep primary key(Id, ClientId)
) with "cache_name=ClientDep,affinity_key=Id";

insert into t_ClientDep values ('1', '00001', '1');
insert into t_ClientDep values ('2', '00002', '1');

create table t_ClientInfoDep
(
Id char(12) not null,
DepId char(12) not null,
isActive char(1),
constraint pk_ClientInfoDep primary key(Id, DepId)
) with "cache_name=ClientInfoDep,affinity_key=Id";

insert into t_ClientInfoDep values ('1', '00001', '1');
insert into t_ClientInfoDep values ('2', '00002', '1');

create or replace view v_test as
select a.ClientId, b.Id from
t_ClientDep a, t_ClientInfoDep b
where a.id = b.id and b.isactive = '1';
```

I can get two records while running below query sql.
`select t.* from t_ClientInfo t inner join v_test tv on t.id = tv.id;`

```
1 a testInfo1
2 a testInfo2
```

I can one record with the specified id.
`select t.* from t_ClientInfo t inner join v_test tv on t.id = tv.id where tv.id = '1';`

```
1 a testInfo1
```
I can not get any record with below sql.
`select t.* from t_ClientInfo t inner join v_test tv on t.id = tv.id where t.id = '1';`
This looks like a bug on view in 2.17 version.

Contributor guide

Open the contributing guide

Research direction

Run the supplied table, view, and join queries against Apache Ignite 2.17 and compare the unrestricted, tv.id-filtered, and t.id-filtered results. Trace the SQL view and join handling to identify why filtering on t.id returns no row; done means the final query returns the same matching record as the tv.id variant, with a regression test covering the case.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.