Unable to get correct result with view in 2.17
- 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
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