gcol_select_ndb_extra_tests.inc

# set default_storage_engine = 'ndbcluster';


-- echo TESTS FROM gcol_select_ndb_extra_tests.inc

# t1 has an auto-increment pk
create table t1
  (t1_seq_pk int auto_increment not null primary key,
   t1_base_col int,
   t1_vir_gcol int generated always as (-t1_base_col) virtual,
   t1_sto_gcol int generated always as (-t1_base_col) stored,
   index (t1_sto_gcol));

insert into t1 (t1_base_col) values (2), (1), (1), (3), (NULL);

# t2 has a visible pk and a unique index on a stored gcol
create table t2
  (t2_pk int primary key,
   t2_vir_gcol int generated always as (-t2_pk) virtual,
   t2_sto_gcol_unique int generated always as (-t2_pk) stored unique);

insert into t2 (t2_pk) values (1),(2),(3),(4),(5),(6);

# t3 has a hidden pk, and the virtual column is after the stored column
create table t3
 (t3_base_col int,
  t3_sto_gcol int generated always as (-t3_base_col) stored,
  t3_vir_gcol int generated always as (-t3_base_col) virtual,
  index(t3_sto_gcol)
 );

insert into t3 (t3_base_col) values (2), (1), (1), (3), (NULL);

# t4 is like t1 but changes relative order of stored & virtual gcols
create table t4
  (t4_seq_pk int auto_increment not null primary key,
   t4_base_col int,
   t4_sto_gcol int generated always as (-t4_base_col) stored,
   t4_vir_gcol int generated always as (-t4_base_col) virtual,
   index (t4_sto_gcol));

insert into t4 (t4_base_col) values (2), (1), (1), (3), (NULL);

# t5 is like t4 but moves base_col relative to gcols
create table t5
  (t5_seq_pk int auto_increment not null primary key,
   t5_sto_gcol int generated always as (-t5_base_col) stored,
   t5_vir_gcol int generated always as (-t5_base_col) virtual,
   t5_base_col int,
   index (t5_sto_gcol));

insert into t5 (t5_base_col) values (2), (1), (1), (3), (NULL);


--echo # TABLE SCAN
--sorted_result
select * from t2;

--echo # TABLE SCAN WITH MASK
--sorted_result
select t2_pk, t2_vir_gcol from t2;
--sorted_result
select t2_sto_gcol_unique from t2;

--echo # RANGE SCANS
--sorted_result
select * from t2 where t2_pk = 2 or t2_pk = 4;
--sorted_result
select t2_vir_gcol from t2 where t2_pk = 2 or t2_pk = 4;
--sorted_result
select * from t2 where t2_sto_gcol_unique = -2 or t2_sto_gcol_unique = -4;
--sorted_result
select t2_vir_gcol from t2 where t2_sto_gcol_unique = -2 or t2_sto_gcol_unique = -4;

--echo # Join t1 to t2
# set debug = 'O,/Users/jdd/Desktop/TraceFiles/tf-mtr:t:N:d';
--sorted_result
select t1_base_col from t1
  join t2 on (t1_sto_gcol = t2_sto_gcol_unique)
  where t2_sto_gcol_unique between -2 and -1 ;
# set debug = '';

--echo # As above but join t3 to t2
--sorted_result
select t3_base_col from t3
  join t2 on (t3_sto_gcol = t2_sto_gcol_unique)
  where t2_sto_gcol_unique between -2 and -1 ;

--echo # Join t4 to t2
--sorted_result
select t4_base_col from t4
  join t2 on (t4_sto_gcol = t2_sto_gcol_unique)
  where t2_sto_gcol_unique between -2 and -1 ;

--echo # Join t5 to t2
--sorted_result
select t5_base_col from t5
  join t2 on (t5_sto_gcol = t2_sto_gcol_unique)
  where t2_sto_gcol_unique between -2 and -1 ;


--echo # Subquery transformed to semi-join
--sorted_result
select * from t1
where t1_sto_gcol in
  (select t2_sto_gcol_unique from t2
   where t2_sto_gcol_unique between -2 and -1);



# Extra cleanup
drop table t4;
drop table t5;