109 lines
4.7 KiB
Plaintext
109 lines
4.7 KiB
Plaintext
set max_heap_table_size = 286720000;
|
|
create table t1 (a int primary key) engine=memory;
|
|
load data local infile 'suite/ndb/data/table_data10000.dat' into table t1 columns terminated by ' ' (a, @col2);
|
|
insert into t1 select a + 10000 from t1;;
|
|
insert into t1 select a + 10000 * 2 from t1;;
|
|
insert into t1 select a + 10000 * 2 * 2 from t1;;
|
|
insert into t1 select a + 10000 * 2 * 2 * 2 from t1;;
|
|
select count(*) from t1;
|
|
count(*)
|
|
160000
|
|
alter table t1 engine=ndbcluster comment='NDB_TABLE=NOLOGGING' partition by key() partitions 1;
|
|
create table t2 (a int primary key) engine=memory;
|
|
insert into t2 select a from t1;
|
|
the left join below should result in scanning t2 and do pk lookups in t1
|
|
explain select if(isnull(t1.a),t2.a,NULL) missed, count(*) row_count from t2 left join t1 on t1.a=t2.a group by if(isnull(t1.a),t2.a,NULL);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL # # Using temporary
|
|
1 SIMPLE t1 p0 eq_ref PRIMARY PRIMARY 4 test.t2.a # # NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select if((`test`.`t1`.`a` is null),`test`.`t2`.`a`,NULL) AS `missed`,count(0) AS `row_count` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = `test`.`t2`.`a`)) where true group by if((`test`.`t1`.`a` is null),`test`.`t2`.`a`,NULL)
|
|
the result rows with missed equal to NULL should count all rows (160000)
|
|
the other rows are the failed lookups and there should not be any such
|
|
select if(isnull(t1.a),t2.a,NULL) missed, count(*) row_count from t2 left join t1 on t1.a=t2.a group by if(isnull(t1.a),t2.a,NULL);
|
|
missed row_count
|
|
NULL 160000
|
|
verify that it is not possible to reinsert all rows in t1 to itself
|
|
affected rows should be zero
|
|
set @save_max_error_count = @@max_error_count;
|
|
set @@max_error_count = 0;
|
|
insert ignore into t1 select a from t2 limit 0, 10000;
|
|
affected rows: 0
|
|
info: Records: 10000 Duplicates: 10000 Warnings: 10000
|
|
insert ignore into t1 select a from t2 limit 10000, 10000;
|
|
affected rows: 0
|
|
info: Records: 10000 Duplicates: 10000 Warnings: 10000
|
|
insert ignore into t1 select a from t2 limit 20000, 10000;
|
|
affected rows: 0
|
|
info: Records: 10000 Duplicates: 10000 Warnings: 10000
|
|
insert ignore into t1 select a from t2 limit 30000, 10000;
|
|
affected rows: 0
|
|
info: Records: 10000 Duplicates: 10000 Warnings: 10000
|
|
insert ignore into t1 select a from t2 limit 40000, 10000;
|
|
affected rows: 0
|
|
info: Records: 10000 Duplicates: 10000 Warnings: 10000
|
|
insert ignore into t1 select a from t2 limit 50000, 10000;
|
|
affected rows: 0
|
|
info: Records: 10000 Duplicates: 10000 Warnings: 10000
|
|
insert ignore into t1 select a from t2 limit 60000, 10000;
|
|
affected rows: 0
|
|
info: Records: 10000 Duplicates: 10000 Warnings: 10000
|
|
insert ignore into t1 select a from t2 limit 70000, 10000;
|
|
affected rows: 0
|
|
info: Records: 10000 Duplicates: 10000 Warnings: 10000
|
|
insert ignore into t1 select a from t2 limit 80000, 10000;
|
|
affected rows: 0
|
|
info: Records: 10000 Duplicates: 10000 Warnings: 10000
|
|
insert ignore into t1 select a from t2 limit 90000, 10000;
|
|
affected rows: 0
|
|
info: Records: 10000 Duplicates: 10000 Warnings: 10000
|
|
insert ignore into t1 select a from t2 limit 100000, 10000;
|
|
affected rows: 0
|
|
info: Records: 10000 Duplicates: 10000 Warnings: 10000
|
|
insert ignore into t1 select a from t2 limit 110000, 10000;
|
|
affected rows: 0
|
|
info: Records: 10000 Duplicates: 10000 Warnings: 10000
|
|
insert ignore into t1 select a from t2 limit 120000, 10000;
|
|
affected rows: 0
|
|
info: Records: 10000 Duplicates: 10000 Warnings: 10000
|
|
insert ignore into t1 select a from t2 limit 130000, 10000;
|
|
affected rows: 0
|
|
info: Records: 10000 Duplicates: 10000 Warnings: 10000
|
|
insert ignore into t1 select a from t2 limit 140000, 10000;
|
|
affected rows: 0
|
|
info: Records: 10000 Duplicates: 10000 Warnings: 10000
|
|
insert ignore into t1 select a from t2 limit 150000, 10000;
|
|
affected rows: 0
|
|
info: Records: 10000 Duplicates: 10000 Warnings: 10000
|
|
set @@max_error_count = @save_max_error_count;
|
|
|
|
set ndb_use_transactions=0;
|
|
drop table t1;
|
|
create table t1 (a int primary key)
|
|
engine=ndbcluster
|
|
comment='NDB_TABLE=NOLOGGING'
|
|
partition by key() partitions 1;
|
|
insert into t1 select * from t2;
|
|
drop table t1;
|
|
select max(used_pages) into @data_memory_pages
|
|
from ndbinfo.memoryusage where memory_type = 'Data memory';
|
|
create table t1 (a int primary key)
|
|
engine=ndbcluster
|
|
comment='NDB_TABLE=NOLOGGING'
|
|
partition by key() partitions 1;
|
|
insert into t1 select * from t2;
|
|
drop table t1;
|
|
create table t1 (a int primary key)
|
|
engine=ndbcluster
|
|
comment='NDB_TABLE=NOLOGGING'
|
|
partition by key() partitions 1;
|
|
insert into t1 select * from t2;
|
|
drop table t1;
|
|
create table t1 (a int primary key)
|
|
engine=ndbcluster
|
|
comment='NDB_TABLE=NOLOGGING'
|
|
partition by key() partitions 1;
|
|
insert into t1 select * from t2;
|
|
drop table t1;
|
|
drop table t2;
|