subject

https://github.com/OceanBase-Partner/lectures-on-dbms-implementation/blob/main/miniob-topics.md
debugging
Less record breakpoint set --file condition_filter.cpp --line 388 breakpoint set --file condition_filter.cpp --line 270 breakpoint set --file bplus_tree.cpp --line 2467 Query index IndexScanner *Table::find_index_for_scan IndexScanner *BplusTreeIndex::create_scanner BplusTreeScanner::open find_first_index_satisfied breakpoint set --file bplus_tree.cpp --line 190 } Table::scan_record scan_record_by_index next_entry get_next_idx_in_memory filter->filter bool BplusTreeScanner::satisfy_condition breakpoint set --file bplus_tree.cpp --line 2283 breakpoint set --file tuple.cpp --line 531 breakpoint set --file tuple.cpp --line 602 breakpoint set --file bplus_tree.cpp --line 2274 breakpoint set --file bplus_tree.cpp --line 2309 breakpoint set --file bplus_tree.cpp --line 190 breakpoint set --file tuple.cpp --line 531 breakpoint set --file condition_filter.cpp --line 105 breakpoint set --file execute_stage.cpp --line 811 breakpoint set --file execute_stage.cpp --line 722 case SCF_INSERT: breakpoint set --file default_storage_stage.cpp --line 180 breakpoint set --file execute_stage.cpp --line 340 breakpoint set --file tuple.cpp --line 586 breakpoint set --file condition_filter.cpp --line 143 breakpoint set --file condition_filter.cpp --line 105 breakpoint set --file execute_stage.cpp --line 811 breakpoint set --file execute_stage.cpp --line 722 IndexScanner *Table::find_index_for_scan(const DefaultConditionFilter &filter) breakpoint set --file bplus_tree.cpp --line 190 breakpoint set --file table.cpp --line 1006 bool BplusTreeScanner::satisfy_condition(const char *pkey) breakpoint set --file bplus_tree.cpp --line 2270 breakpoint set --file bplus_tree.cpp --line 1991
difficulty
1.5
Q: multi table query, case: select t12.num,t13.num,t12.birthday from t12,t13 where t12.num=t13.num; select t12.num,t13.num,t12.birthday when displaying. How do you handle it? The default code is displayed according to the table filter field?
f7327f5bb81fada1d222df3b9e8a82e6.png
1.4gdb compare the search difference between ordinary index and null index
Tip 1: how do operators solve case less before faster full set search_ EQUAL: || case NOT_EQUAL: ||
Tip 2: difference of comparison operator select * from t12 where num =14; select * from t12 where num is null;
Query index IndexScanner *Table::find_index_for_scan IndexScanner *BplusTreeIndex::create_scanner BplusTreeScanner::open find_first_index_satisfied breakpoint set --file bplus_tree.cpp --line 190 } Table::scan_record scan_record_by_index next_entry get_next_idx_in_memory filter->filter bool BplusTreeScanner::satisfy_condition breakpoint set --file bplus_tree.cpp --line 2283
I gdb track errors: why is the data wrong. It was found that:
breakpoint set --file condition_filter.cpp --line 388 breakpoint set --file condition_filter.cpp --line 270 breakpoint set --file bplus_tree.cpp --line 2467
bool DefaultConditionFilter::filter(const Record &rec) const
1.3 comparison with index and comparison without index
- bool DefaultConditionFilter::filter(const Record &rec) const
- CompareKey
- bool BplusTreeScanner::satisfy_condition(const char *pkey)
void TupleRecordConverter::add_record(const char *record)
Subtext: what do these three functions do,
1.2 null and value, operator, null comparison
- Null and null true
- null and other values false
1.3 how to store null instead of 999
- 0 == strcmp(s, "999")
- How to store null in memory address? Do you use null ptr? Can't resolve is null value?
Train of thought analysis
19th test
- No index, no filter condition, query
bool DefaultConditionFilter::filter(const Record &rec) const
execute_stage.cpp breakpoint set --file execute_stage.cpp --line 342 breakpoint set --file condition_filter.cpp --line 388 breakpoint set --file condition_filter.cpp --line 388 breakpoint set --file condition_filter.cpp --line 270 breakpoint set --file bplus_tree.cpp --line 2467
8c2f3f2f0768518da9706a4dcf8cac6d.png
- select NUM from NULL_TABLE3;
breakpoint set --file bplus_tree.cpp --line 2467
- Key issues:
Query no record select * from NULL_TABLE3;
I began to think that it was null and there was no need to query [error]
- Case: there is a problem with the avg function in the case of pure null
select * from NULL_TABLE3;
breakpoint set --file execute_stage.cpp --line 342
breakpoint set --file tuple.cpp --line 355
breakpoint set --file tuple.cpp --line 834
839
SELECT NUM FROM NULL_TABLE3;
null: result file difference(`-` is yours and `+` is base) CREATE TABLE NULL_TABLE3(ID INT, NUM INT NULLABLE); SUCCESS INSERT INTO NULL_TABLE3 VALUES (1, NULL); SUCCESS INSERT INTO NULL_TABLE3 VALUES (2, NULL); SUCCESS SELECT COUNT(NUM) FROM NULL_TABLE3; COUNT(NUM) 0 SELECT MIN(NUM) FROM NULL_TABLE3; +MIN(NUM) +NULL +SELECT max(num) FROM null_table3; +MAX(NUM) +NULL +SELECT avg(num) FROM null_table3; +AVG(NUM) +NULL
8f306a2f07ef2203d29b6045fdb169ad.png
18th test
2359ac666e468f2b549276eeb91efa28.png
Test 17: multi meter situation
insert into t13 values(1,20,"2021-01-30"); insert into t12 values(2,20,"2021-01-01"); select t12.num,t13.num,t12.birthday from t12,t13 where t12.num=t13.num;
breakpoint set --file execute_stage.cpp --line 308
selects
[0] = (relation_name = "t12", attribute_name = "birthday", funtype = FUN_NO) [1] = (relation_name = "t13", attribute_name = "num", funtype = FUN_NO) [2] = (relation_name = "t12", attribute_name = "num", funtype = FUN_NO)
Q: multi table query, case: select t12.num,t13.num,t12.birthday from t12,t13 where t12.num=t13.num;
select t12.num,t13.num,t12.birthday when displaying. How do you handle it? The default code is displayed according to the table filter field?
select t12.num,t13.num,t12.birthday from t12,t13 where t12.num=t13.num;
4f12ecdddfc3c75ec196989c93fe0313.png
breakpoint set --file execute_stage.cpp --line 307
breakpoint set --file execute_stage.cpp --line 270
breakpoint set --file execute_stage.cpp --line 340
breakpoint set --file execute_stage.cpp --line 362
1fdce6189431146436486957ee986a74.png
schema_ = { fields_ = size=2 { [0] = { type_ = INTS table_name_ = "t12" field_name_ = "num" visible_ = true function_type = FUN_NO nullable_ = 1 field_name_count_number_ = "" } [1] = { type_ = DATES table_name_ = "t12" field_name_ = "birthday" visible_ = true function_type = FUN_NO nullable_ = 1 field_name_count_number_ = "" } } realTabeNumber = -1 } [0] = { tuples_ = size=1 { [0] = { values_ = size=1 { [0] = std::__1::shared_ptr<TupleValue>::element_type @ 0x00007fc448604080 strong=1 weak=1 { __ptr_ = 0x00007fc448604080 } } sp1 = nullptr { __ptr_ = nullptr } sp2 = nullptr { __ptr_ = nullptr } sp3 = nullptr { __ptr_ = nullptr } selectComareIndex = -1 } } schema_ = { fields_ = size=1 { [0] = { type_ = INTS table_name_ = "t13" field_name_ = "num" visible_ = true function_type = FUN_NO nullable_ = 1 field_name_count_number_ = "" } } realTabeNumber = -1 }
- Judge whether to adjust
- adjustment
select t12.num,t13.num,t12.birthday from t12,t13 where t12.num=t13.num; create_selection_executor null: result file difference(`-` is yours and `+` is base) SELECT NULL_TABLE.NUM,NULL_TABLE2.NUM,NULL_TABLE.BIRTHDAY FROM NULL_TABLE,NULL_TABLE2 WHERE NULL_TABLE.NUM=NULL_TABLE2.NUM; -18 | 2020-01-01 | 18 -NULL_TABLE.NUM | NULL_TABLE.BIRTHDAY | NULL_TABLE2.NUM +18 | 18 | 2020-01-01 +NULL_TABLE.NUM | NULL_TABLE2.NUM | NULL_TABLE.BIRTHDAY
16 tests:
- When there is an index: non null, null is false compared with the value, and no record can be queried? How to handle this? select * from t12 where num is not null--- There should be a record
insert into t12 values(6,null,null); //0k
select * from t12 where num =14; select * from t12 where num is null; select * from t12 where num is not null;
15 wrong
- Modify: bool DefaultConditionFilter::filter
How do normal types compare to int s
- gdb debugging
breakpoint set --file execute_stage.cpp --line 270 breakpoint set --file execute_stage.cpp --line 397
select * from t12 where num is not null; 1 | 2 | 2020-01-01 Lack of records
- Look at the log: DefaultConditionFilter::filter IS_NOT_NULL
void TupleRecordConverter::add_record(const char *record)
Index query:
Line 1999
tmp = CompareKey(node->keys + i * file_header_.key_length, key, file_header_.attr_type, file_header_.attr_length);
case LESS_THAN: switch (attr_type) { case INTS: flag = (i1 < i2); break; case FLOATS: flag = (f1 < f2); break; case CHARS: flag = (strncmp(s1, s2, attr_length) < 0); break; case DATES: flag = (i1 < i2); break; default: LOG_PANIC("Unknown attr type: %d", attr_type); } break;
There is an error. It is wrong to compare the conditions with and without index
- According to the test, I wrote the wrong code
int CompareKey(const char *pdata, const char *pkey, AttrType attr_type, int attr_length)
- 4 comparison code
* thread #9, stop reason = breakpoint 1.1 * frame #0: 0x0000000109e572e4 observer`CompareKey(pdata="\U00000002", pkey="999", attr_type=INTS, attr_length=4) at bplus_tree.cpp:191:3 [opt] frame #1: 0x0000000109e5d05a observer`BplusTreeHandler::find_first_index_satisfied(this=0x00007ff5b1407788, compop=IS_NULL, key="\xff\xff\xff\xff", page_num=<unavailable>, rididx=0x00007ff5b2b04804) at bplus_tree.cpp:1955:13 [opt] frame #2: 0x0000000109e5d3cf observer`BplusTreeScanner::open(this=0x00007ff5b2b044c0, comp_op=IS_NULL, value=<unavailable>) at bplus_tree.cpp:2081:23 [opt] frame #3: 0x0000000109e60445 observer`BplusTreeIndex::create_scanner(this=0x00007ff5b14076e0, comp_op=IS_NULL, value="999") at bplus_tree_index.cpp:98:31 [opt] frame #4: 0x0000000109e76550 observer`Table::find_index_for_scan(this=0x00007ff5b1605160, filter=<unavailable>) at table.cpp:1067:31 [opt] frame #5: 0x0000000109e75f54 observer`Table::scan_record(this=0x00007ff5b1605160, trx=0x00007ff5b1606eb0, filter=0x00007000065b1818, limit=2147483647, context=0x00007000065b17e0, record_reader=(observer`scan_record_reader_adapter(Record*, void*) at table.cpp:502))(Record*, void*)) at table.cpp:531:33 [opt]
- result:
[2021-11-13 18:44:3 pid:18738 tid:7000065b5000 INFO: /Users/wangchuanyi/code/miniob/src/observer/sql/parser/parse.cpp condition_init 195]>>left_is_attr ........... [2021-11-13 18:44:3 pid:18738 tid:7000065b5000 INFO: /Users/wangchuanyi/code/miniob/src/observer/storage/common/bplus_tree.cpp CompareKey 197]>>CompareKey int >>>: 1=2,2=3750201 [2021-11-13 18:44:3 pid:18738 tid:7000065b5000 INFO: /Users/wangchuanyi/code/miniob/src/observer/storage/common/bplus_tree.cpp CompareKey 197]>>CompareKey int >>>: 1=2,2=3750201 [2021-11-13 18:44:3 pid:18738 tid:7000065b5000 INFO: /Users/wangchuanyi/code/miniob/src/observer/storage/common/bplus_tree.cpp CompareKey 197]>>CompareKey int >>>: 1=2,2=3750201 [2021-11-13 18:44:3 pid:18738 tid:7000065b5000 INFO: /Users/wangchuanyi/code/miniob/src/observer/storage/common/bplus_tree.cpp CompareKey 197]>>CompareKey int >>>: 1=3750201,2=3750201 [2021-11-13 18:44:3 pid:18738 tid:7000065b5000 INFO: /Users/wangchuanyi/code/miniob/src/observer/storage/common/bplus_tree.cpp CompareKey 197]>>CompareKey int >>>: 1=3750201,2=3750201 [2021-11-13 18:44:3 pid:18738 tid:7000065b5000 INFO: /Users/wangchuanyi/code/miniob/src/observer/storage/common/bplus_tree.cpp CompareKey 197]>>CompareKey int >>>: 1=3750201,2=3750201 [2021-11-13 18:44:3 pid:18738 tid:7000065b5000 INFO: /Users/wangchuanyi/code/miniob/src/observer/storage/common/bplus_tree.cpp CompareKey 197]>>CompareKey int >>>: 1=3750201,2=3750201 [2021-11-13 18:44:3 pid:18738 tid:7000065b5000 INFO: /Users/wangchuanyi/code/miniob/src/observer/sql/executor/execute_stage.cpp do_select 681]>> query resault. id | num | birthday
- Why?
- Null is stored according to 999 when inserting. But during the search. I didn't modify the resolution. Null is filtered according to null
//Compare the size of two key s int CompareKey(const char *pdata, const char *pkey, AttrType attr_type, int attr_length)
bool DefaultConditionFilter::filter(const Record &rec) const
No, where does it fall
Find by index:
RC Table::scan_record(
null is represented by 999
IndexScanner *index_scanner = find_index_for_scan(filter); if (index_scanner != nullptr) { return scan_record_by_index(trx, index_scanner, filter, limit, context, record_reader); }
1faecc37091dddb49caa28920dbbaaf6.png
2.11 - null is the opposite of null comparison rules and bought on the Internet
- Modify filter conditions:
bool DefaultConditionFilter::filter(const Record &rec) const
2.10 there is an error
Title: support NULL type
sql: select * from t where null is null; -- Note = difference from is
Question: is null null true or false?
The test results are incorrect
d4eb03443d80d58b67138d2bab9c486f.png
- Error code location: null is not a column and an error is reported directly
- Why has NULLL changed???
e9f6fd4ac322baa76fb9d1189723bc41.png
right_is_attr = 1 right_attr = (relation_name = 0x0000000000000000, attribute_name = "NULLL", funtype = FUN_MAX | 0xe2f300a8)
24d1a4919d6dbd8c241f423396d1bf3d.png
- Error sql:
select * from t12 WHERE NULL IS NULLL;
right.is_attr = true; const FieldMeta *field_right = table_meta.field(condition.right_attr.attribute_name); if (nullptr == field_right) { LOG_WARN("No such field in condition. %s.%s", table.name(), condition.right_attr.attribute_name); return RC::SCHEMA_FIELD_MISSING; } void condition_init(Condition *condition, CompOp comp, int left_is_attr, RelAttr *left_attr, Value *left_value, int right_is_attr, RelAttr *right_attr, Value *right_value) {
- |value comOp value
2.9 both the comparison operator and null comparison are false
Comparison Functions and Operators
https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html
- 12.4.2 Comparison Functions and Operators
https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_equal
https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL. To demonstrate this for yourself, try the following query:
Because the result of any arithmetic comparison with NULL is also NULL, you cannot obtain any meaningful results from such comparisons. https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html
Voice over:
- If any value is compared with NULL, the result is FALSE.
- If any comparison operation is NULL, the result is FALSE.
code:
- bool DefaultConditionFilter::filter(const Record &rec) const
select * from t12 where 1<>NULL; select * from t12 where NULL<>1;
- filter->filter(record)))
RC Table::scan_record_by_index(
class ConditionFilter { public: virtual ~ConditionFilter(); /** * Filter one record * @param rec * @return true means match condition, false means failed to match. */ virtual bool filter(const Record &rec) const = 0; };
- [] view the select filter criteria completely
do_select->create_selection_executor->
class DefaultConditionFilter : public ConditionFilter
execute
//sql execution RC SelectExeNode::execute(TupleSet &tuple_set) { CompositeConditionFilter condition_filter; condition_filter.init((const ConditionFilter **)condition_filters_.data(), condition_filters_.size()); tuple_set.clear(); tuple_set.set_schema(tuple_schema_); tuple_set.old_schema =old_tuple_schema; TupleRecordConverter converter(table_, tuple_set); return table_->scan_record(trx_, &condition_filter, -1, (void *)&converter, record_reader); }
- RC Table::scan_record(Trx *trx, ConditionFilter *filter, int limit, void *context, RC (*record_reader)(Record *record, void *context))
- IndexScanner *index_scanner = find_index_for_scan(filter);
- rc = scanner.open_scan(*data_buffer_pool_, file_id_, filter);
- condition_filter_->filter(current_record))
- bool DefaultConditionFilter::filter(const Record &rec) const
Summary:
If any value (reserved operator) is compared with NULL, the result is FALSE
https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html
2.8 modifying index filter conditions
- bool BplusTreeScanner::satisfy_condition(const char *pkey)
AttrType attr_type = index_handler_.file_header_.attr_type;
case IS_NULL: { flag =false; } break; case IS_NOT_NULL: { flag =true; } break;
2.7 why is the implementation fruitless
select * from t12 where id is null; select * from t12 where id is not null;
select * from t12 where 1<>NULL; select * from t12 where 1 IS NOT NULL;
Task: RC defaultconditionfilter:: init (Table & table, const condition & condition)
- [] task:
bool DefaultConditionFilter::filter(const Record &rec) const
1 IS NOT NULL; //true case IS_NOT_NULL: return cmp_result != 0;
2.6 execution error
select * from t12 where id is null;
std::vector<DefaultConditionFilter *> condition_filters;
RC DefaultConditionFilter::init(Table &table, const Condition &condition) private: ConDesc left_; ConDesc right_; AttrType attr_type_ = UNDEFINED; CompOp comp_op_ = NO_OP; };
Task: adjust RC DefaultConditionFilter::init function
2.5 null judgment when adding query
where: condition:
2.4 create: add NOT NULL keyword
2.3 modifying aggregate functions
bool TupleSet::avg_print(std::ostream &os) const
- TupleField contains aggregate functions
- std::vector<std::shared_ptr> values_;
2.2 display Tuple tuple;
- Is null display the same as normal type display?
- Consider the case of a single line here
- //Single table aggregation: only one row
- Understand class: class Tuple
code implementation
2.3 where conditions
- Generate filter condition: STD:: vector < defaultconditionfilter * > condition_ filters;
RC DefaultConditionFilter::init(Table &table, const Condition &condition) private: ConDesc left_; ConDesc right_; AttrType attr_type_ = UNDEFINED; CompOp comp_op_ = NO_OP; };
- Execute filter condition
- bool DefaultConditionFilter::filter(const Record &rec) const
bool BplusTreeScanner::satisfy_condition(const char *pkey)
- Use 999 code
comOp: EQ { CONTEXT->comp = EQUAL_TO; } | LT { CONTEXT->comp = LESS_THAN; } | GT { CONTEXT->comp = GREAT_THAN; } | LE { CONTEXT->comp = LESS_EQUAL; } | GE { CONTEXT->comp = GREAT_EQUAL; } | NE { CONTEXT->comp = NOT_EQUAL; } ;
2.2 display Tuple tuple;
void TupleRecordConverter::add_record(const char *record)
test result
case 3
null: result file difference(`-` is yours and `+` is base) 3. SELECT WITH CONSTANT SELECT * FROM NULL_TABLE WHERE 1 IS NULL; -FAILURE +ID | NUM | PRICE | BIRTHDAY SELECT * FROM NULL_TABLE WHERE 1 IS NOT NULL; -FAILURE +1 | 18 | 10 | 2020-01-01 +2 | NULL | 20 | 2010-01-11 +3 | 12 | 30 | NULL +4 | 15 | 30 | 2021-01-31 +ID | NUM | PRICE | BIRTHDAY SELECT * FROM NULL_TABLE WHERE NULL=1; -FAILURE +ID | NUM | PRICE | BIRTHDAY SELECT * FROM NULL_TABLE WHERE 1=NULL; -FAILURE +ID | NUM | PRICE | BIRTHDAY SELECT * FROM NULL_TABLE WHERE 1<>NULL; -FAILURE +ID | NUM | PRICE | BIRTHDAY SELECT * FROM NULL_TABLE WHERE 1<NULL; -FAILURE +ID | NUM | PRICE | BIRTHDAY SELECT * FROM NULL_TABLE WHERE 1>NULL; -FAILURE +ID | NUM | PRICE | BIRTHDAY
CREATE TABLE NULL_TABLE(ID INT, NUM INT NULLABLE, PRICE FLOAT NOT NULL, BIRTHDAY DATE NULLABLE);
CREATE TABLE NULL_TABLE2(ID INT, NUM INT NULLABLE, PRICE FLOAT NOT NULL, BIRTHDAY DATE NULLABLE);
CREATE INDEX INDEX_NUM ON NULL_TABLE(NUM);
CREATE TABLE NULL_TABLE(ID INT, NUM INT NULLABLE, PRICE FLOAT NOT NULL, BIRTHDAY DATE NULLABLE); -FAILURE +SUCCESS CREATE TABLE NULL_TABLE2(ID INT, NUM INT NULLABLE, PRICE FLOAT NOT NULL, BIRTHDAY DATE NULLABLE); -FAILURE +SUCCESS CREATE INDEX INDEX_NUM ON NULL_TABLE(NUM); -FAILURE +SUCCESS
3298bb857adc370ead1071015fc2060b.png
- core ok
: line 1: 1355 Segmentation fault: 11 ./observer -f ./observer.ini
0x0000000107d8ef8d Table::make_record(int, _Value const*, char*&) + 1357 (table.cpp:430)
- No, ok
f93af99ea8021b6cb8df592825469889.png
null: result file difference(`-` is yours and `+` is base) 3. SELECT WITH CONSTANT SELECT * FROM NULL_TABLE WHERE 1 IS NULL; ID | NUM | PRICE | BIRTHDAY SELECT * FROM NULL_TABLE WHERE 1 IS NOT NULL; 1 | 18 | 10 | 2020-01-01 2 | NULL | 20 | 2010-01-11 3 | 12 | 30 | NULL 4 | 15 | 30 | 2021-01-31 ID | NUM | PRICE | BIRTHDAY SELECT * FROM NULL_TABLE WHERE NULL=1; -FAILURE +ID | NUM | PRICE | BIRTHDAY SELECT * FROM NULL_TABLE WHERE 1=NULL; ID | NUM | PRICE | BIRTHDAY SELECT * FROM NULL_TABLE WHERE 1<>NULL; -1 | 18 | 10 | 2020-01-01 -2 | NULL | 20 | 2010-01-11 -3 | 12 | 30 | NULL -4 | 15 | 30 | 2021-01-31 ID | NUM | PRICE | BIRTHDAY SELECT * FROM NULL_TABLE WHERE 1<NULL; -1 | 18 | 10 | 2020-01-01 -2 | NULL | 20 | 2010-01-11 -3 | 12 | 30 | NULL -4 | 15 | 30 | 2021-01-31 ID | NUM | PRICE | BIRTHDAY SELECT * FROM NULL_TABLE WHERE 1>NULL; ID | NUM | PRICE | BIRTHDAY