| 1 |
set max_heap_table_size = 286720000; |
| 2 |
create table t1 (a int primary key) engine=memory; |
| 3 |
load data local infile 'suite/ndb/data/table_data10000.dat' into table t1 columns terminated by ' ' (a, @col2); |
| 4 |
insert into t1 select a + 10000 from t1;; |
| 5 |
insert into t1 select a + 10000 * 2 from t1;; |
| 6 |
insert into t1 select a + 10000 * 2 * 2 from t1;; |
| 7 |
insert into t1 select a + 10000 * 2 * 2 * 2 from t1;; |
| 8 |
select count(*) from t1; |
| 9 |
count(*) |
| 10 |
160000 |
| 11 |
alter table t1 engine=ndbcluster comment='NDB_TABLE=NOLOGGING' partition by key() partitions 1; |
| 12 |
create table t2 (a int primary key) engine=memory; |
| 13 |
insert into t2 select a from t1; |
| 14 |
the left join below should result in scanning t2 and do pk lookups in t1 |
| 15 |
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); |
| 16 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 17 |
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL # # Using temporary |
| 18 |
1 SIMPLE t1 p0 eq_ref PRIMARY PRIMARY 4 test.t2.a # # NULL |
| 19 |
Warnings: |
| 20 |
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) |
| 21 |
the result rows with missed equal to NULL should count all rows (160000) |
| 22 |
the other rows are the failed lookups and there should not be any such |
| 23 |
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); |
| 24 |
missed row_count |
| 25 |
NULL 160000 |
| 26 |
verify that it is not possible to reinsert all rows in t1 to itself |
| 27 |
affected rows should be zero |
| 28 |
set @save_max_error_count = @@max_error_count; |
| 29 |
set @@max_error_count = 0; |
| 30 |
insert ignore into t1 select a from t2 limit 0, 10000; |
| 31 |
affected rows: 0 |
| 32 |
info: Records: 10000 Duplicates: 10000 Warnings: 10000 |
| 33 |
insert ignore into t1 select a from t2 limit 10000, 10000; |
| 34 |
affected rows: 0 |
| 35 |
info: Records: 10000 Duplicates: 10000 Warnings: 10000 |
| 36 |
insert ignore into t1 select a from t2 limit 20000, 10000; |
| 37 |
affected rows: 0 |
| 38 |
info: Records: 10000 Duplicates: 10000 Warnings: 10000 |
| 39 |
insert ignore into t1 select a from t2 limit 30000, 10000; |
| 40 |
affected rows: 0 |
| 41 |
info: Records: 10000 Duplicates: 10000 Warnings: 10000 |
| 42 |
insert ignore into t1 select a from t2 limit 40000, 10000; |
| 43 |
affected rows: 0 |
| 44 |
info: Records: 10000 Duplicates: 10000 Warnings: 10000 |
| 45 |
insert ignore into t1 select a from t2 limit 50000, 10000; |
| 46 |
affected rows: 0 |
| 47 |
info: Records: 10000 Duplicates: 10000 Warnings: 10000 |
| 48 |
insert ignore into t1 select a from t2 limit 60000, 10000; |
| 49 |
affected rows: 0 |
| 50 |
info: Records: 10000 Duplicates: 10000 Warnings: 10000 |
| 51 |
insert ignore into t1 select a from t2 limit 70000, 10000; |
| 52 |
affected rows: 0 |
| 53 |
info: Records: 10000 Duplicates: 10000 Warnings: 10000 |
| 54 |
insert ignore into t1 select a from t2 limit 80000, 10000; |
| 55 |
affected rows: 0 |
| 56 |
info: Records: 10000 Duplicates: 10000 Warnings: 10000 |
| 57 |
insert ignore into t1 select a from t2 limit 90000, 10000; |
| 58 |
affected rows: 0 |
| 59 |
info: Records: 10000 Duplicates: 10000 Warnings: 10000 |
| 60 |
insert ignore into t1 select a from t2 limit 100000, 10000; |
| 61 |
affected rows: 0 |
| 62 |
info: Records: 10000 Duplicates: 10000 Warnings: 10000 |
| 63 |
insert ignore into t1 select a from t2 limit 110000, 10000; |
| 64 |
affected rows: 0 |
| 65 |
info: Records: 10000 Duplicates: 10000 Warnings: 10000 |
| 66 |
insert ignore into t1 select a from t2 limit 120000, 10000; |
| 67 |
affected rows: 0 |
| 68 |
info: Records: 10000 Duplicates: 10000 Warnings: 10000 |
| 69 |
insert ignore into t1 select a from t2 limit 130000, 10000; |
| 70 |
affected rows: 0 |
| 71 |
info: Records: 10000 Duplicates: 10000 Warnings: 10000 |
| 72 |
insert ignore into t1 select a from t2 limit 140000, 10000; |
| 73 |
affected rows: 0 |
| 74 |
info: Records: 10000 Duplicates: 10000 Warnings: 10000 |
| 75 |
insert ignore into t1 select a from t2 limit 150000, 10000; |
| 76 |
affected rows: 0 |
| 77 |
info: Records: 10000 Duplicates: 10000 Warnings: 10000 |
| 78 |
set @@max_error_count = @save_max_error_count; |
| 79 |
|
| 80 |
set ndb_use_transactions=0; |
| 81 |
drop table t1; |
| 82 |
create table t1 (a int primary key) |
| 83 |
engine=ndbcluster |
| 84 |
comment='NDB_TABLE=NOLOGGING' |
| 85 |
partition by key() partitions 1; |
| 86 |
insert into t1 select * from t2; |
| 87 |
drop table t1; |
| 88 |
select max(used_pages) into @data_memory_pages |
| 89 |
from ndbinfo.memoryusage where memory_type = 'Data memory'; |
| 90 |
create table t1 (a int primary key) |
| 91 |
engine=ndbcluster |
| 92 |
comment='NDB_TABLE=NOLOGGING' |
| 93 |
partition by key() partitions 1; |
| 94 |
insert into t1 select * from t2; |
| 95 |
drop table t1; |
| 96 |
create table t1 (a int primary key) |
| 97 |
engine=ndbcluster |
| 98 |
comment='NDB_TABLE=NOLOGGING' |
| 99 |
partition by key() partitions 1; |
| 100 |
insert into t1 select * from t2; |
| 101 |
drop table t1; |
| 102 |
create table t1 (a int primary key) |
| 103 |
engine=ndbcluster |
| 104 |
comment='NDB_TABLE=NOLOGGING' |
| 105 |
partition by key() partitions 1; |
| 106 |
insert into t1 select * from t2; |
| 107 |
drop table t1; |
| 108 |
drop table t2; |