Problematic queries when SubQueryConversion = true

[pavel-zotov]

This ticket likely will contain several examples related to mismatches that can be observed during comparison of QA test results when they are launched with SubQueryConversion = true vs default (false).

Don't forget to set SubQueryConversion = true before running following script(s).

Example-1.

set bail on;
shell if exist r:\temp\tmp4test.fdb del /q /f r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb';

create domain dm_emp_id smallint;
create domain dm_dep_id smallint;
create domain dm_name varchar(20);

create table department (
    dept_no dm_dep_id not null,
    dept_name dm_name not null,
    constraint dept_key primary key (dept_no)
);

create table employee (
    emp_no dm_emp_id not null,
    last_name dm_name not null, 
    dept_no dm_dep_id not null constraint ref_key references department(dept_no),
    constraint emp_key primary key (emp_no)
);
commit;
insert into department( dept_no, dept_name) values (1, 'd1');
insert into department( dept_no, dept_name) values (2, 'd2');
insert into department( dept_no, dept_name) values (3, 'd3');
insert into employee( emp_no, last_name, dept_no) values (1, 'e1', 1);
insert into employee( emp_no, last_name, dept_no) values (2, 'e2', 2);
insert into employee( emp_no, last_name, dept_no) values (3, 'e3', 3);
insert into employee( emp_no, last_name, dept_no) values (4, 'e4', 1);
insert into employee( emp_no, last_name, dept_no) values (5, 'e5', 1);
insert into employee( emp_no, last_name, dept_no) values (6, 'e6', 1);
insert into employee( emp_no, last_name, dept_no) values (7, 'e7', 2);
insert into employee( emp_no, last_name, dept_no) values (8, 'e8', 3);
insert into employee( emp_no, last_name, dept_no) values (9, 'e9', 3);
commit;

alter table employee drop constraint ref_key;
alter table department drop constraint dept_key;
commit;

update /* TRACE_ME */ department d set dept_no = -dept_no where exists(select * from employee e where e.dept_no = d.dept_no) rows 1;
insert /* TRACE_ME */  into employee( emp_no, last_name, dept_no) values (12, 'e12', -(select max(dept_no)+1 from department) );

set count on;
set echo on;
select * from department d where exists(select * from employee e where e.dept_no = d.dept_no) order by dept_no rows 1;
set explain on;
set plan on;
delete /* TRACE_ME */ from department d where exists(select * from employee e where e.dept_no = d.dept_no) order by dept_no rows 1;

On 6.0.0.442 its output will be:

select * from department d where exists(select * from employee e where e.dept_no = d.dept_no) order by dept_no rows 1;

DEPT_NO DEPT_NAME
======= ====================
      2 d2

Records affected: 1
set explain on;
set plan on;
delete /* TRACE_ME */ from department d where exists(select * from employee e where e.dept_no = d.dept_no) order by dept_no rows 1;

Sub-query
    -> Filter
        -> Table "EMPLOYEE" as "E" Full Scan
Select Expression
    -> First N Records
        -> Sort (record length: 28, key length: 8)
            -> Filter
                -> Table "DEPARTMENT" as "D" Full Scan
Records affected: 1

(and this is expected: exactly one record must be deleted from 'department' table)

On 5.0.2.1479 this script will not find record that has to be deleted:

select * from department d where exists(select * from employee e where e.dept_no = d.dept_no) order by dept_no rows 1;
Records affected: 0
set explain on;
set plan on;
delete /* TRACE_ME */ from department d where exists(select * from employee e where e.dept_no = d.dept_no) order by dept_no rows 1;

Select Expression
    -> Filter
        -> Hash Join (semi)
            -> First N Records
                -> Sort (record length: 28, key length: 8)
                    -> Table "DEPARTMENT" as "D" Full Scan
            -> Record Buffer (record length: 25)
                -> Table "EMPLOYEE" as "E" Full Scan
Records affected: 0

[sim1984]

It seems this ticket has the same reason as https://github.com/FirebirdSQL/firebird/issues/8224 , see my comment.

[dyemanov]

The ticket may be reopened if more issues are discovered.