Critical Description
On the standard rqg ansi dataset (will attach a backup, called ansijoins) this query returns different results in mysql vs n1ql. I have also attatched the mysql database (it is called multiple_table_db_298...)
sql query is SELECT t_2.* FROM simple_table_1 t_3 LEFT JOIN simple_table_4 t_2 ON ( t_3.decimal_field1 = t_2.decimal_field1 ) LEFT JOIN simple_table_2 t_1 ON ( (t_2.primary_key_id = t_1.primary_key_id AND t_2.int_field1 = t_1.int_field1) ) WHERE (t_3.bool_field1 != false OR (t_3.char_field1 IN ( "A" , "B" , "C" , "D" , "E" )) AND (t_1.primary_key_id IS NULL)) ORDER BY t_2.int_field1 , t_2.decimal_field1 , t_1.primary_key_id , t_1.char_field1 - 628 results
n1ql query is SELECT t_2.* FROM multiple_table_db_29807922_simple_table_1 t_3 LEFT JOIN multiple_table_db_29807922_simple_table_4 t_2 ON ( t_3.decimal_field1 = t_2.decimal_field1 ) LEFT JOIN multiple_table_db_29807922_simple_table_2 t_1 ON ( (t_2.primary_key_id = t_1.primary_key_id AND t_2.int_field1 = t_1.int_field1) ) WHERE (t_3.bool_field1 != false OR (t_3.char_field1 IN [ "A" , "B" , "C" , "D" , "E" ]) AND (t_1.primary_key_id IS NULL)) ORDER BY t_2.int_field1 , t_2.decimal_field1 , t_1.primary_key_id , t_1.char_field1 - 511 results
Explain for n1ql query:
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "UnionScan",
"scans": [
{
"#operator": "IndexScan3",
"as": "t_3",
"index": "simple_table_1_bool_field1",
"index_id": "6708b6ed19591f08",
"index_projection": {
"primary_key": true
},
"keyspace": "multiple_table_db_29807922_simple_table_1",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "false",
"inclusion": 0,
"low": "null"
}
]
}
],
"using": "gsi"
},
{
"#operator": "IndexScan3",
"as": "t_3",
"index": "simple_table_1_bool_field1",
"index_id": "6708b6ed19591f08",
"index_projection": {
"primary_key": true
},
"keyspace": "multiple_table_db_29807922_simple_table_1",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"inclusion": 0,
"low": "false"
}
]
}
],
"using": "gsi"
},
{
"#operator": "IndexScan3",
"as": "t_3",
"index": "simple_table_1_char_field1",
"index_id": "c05c200da2c993a3",
"index_projection": {
"primary_key": true
},
"keyspace": "multiple_table_db_29807922_simple_table_1",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"A\"",
"inclusion": 3,
"low": "\"A\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"B\"",
"inclusion": 3,
"low": "\"B\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"C\"",
"inclusion": 3,
"low": "\"C\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"D\"",
"inclusion": 3,
"low": "\"D\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"E\"",
"inclusion": 3,
"low": "\"E\""
}
]
}
],
"using": "gsi"
}
]
},
{
"#operator": "Fetch",
"as": "t_3",
"keyspace": "multiple_table_db_29807922_simple_table_1",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "NestedLoopJoin",
"alias": "t_2",
"on_clause": "((`t_3`.`decimal_field1`) = (`t_2`.`decimal_field1`))",
"outer": true,
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"as": "t_2",
"index": "simple_table_4_decimal_field1",
"index_id": "5ba33392097cb601",
"index_projection": {
"primary_key": true
},
"keyspace": "multiple_table_db_29807922_simple_table_4",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "(`t_3`.`decimal_field1`)",
"inclusion": 3,
"low": "(`t_3`.`decimal_field1`)"
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "t_2",
"keyspace": "multiple_table_db_29807922_simple_table_4",
"namespace": "default",
"nested_loop": true
}
]
}
},
{
"#operator": "NestedLoopJoin",
"alias": "t_1",
"on_clause": "(((`t_2`.`primary_key_id`) = cover ((`t_1`.`primary_key_id`))) and ((`t_2`.`int_field1`) = cover ((`t_1`.`int_field1`))))",
"outer": true,
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"as": "t_1",
"covers": [
"cover ((`t_1`.`primary_key_id`))",
"cover ((`t_1`.`bool_field1`))",
"cover ((`t_1`.`char_field1`))",
"cover ((`t_1`.`datetime_field1`))",
"cover ((`t_1`.`decimal_field1`))",
"cover ((`t_1`.`int_field1`))",
"cover ((`t_1`.`varchar_field1`))",
"cover ((meta(`t_1`).`id`))"
],
"index": "simple_table_2",
"index_id": "2ddc776049302b41",
"index_projection": {
"entry_keys": [
0,
2,
5
],
"primary_key": true
},
"keyspace": "multiple_table_db_29807922_simple_table_2",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "(`t_2`.`primary_key_id`)",
"inclusion": 3,
"low": "(`t_2`.`primary_key_id`)"
}
]
},
{
"range": [
{
"high": "(`t_2`.`primary_key_id`)",
"inclusion": 3,
"low": "(`t_2`.`primary_key_id`)"
}
]
}
],
"using": "gsi"
}
]
}
},
{
"#operator": "Filter",
"condition": "((not ((`t_3`.`bool_field1`) = false)) or (((`t_3`.`char_field1`) in [\"A\", \"B\", \"C\", \"D\", \"E\"]) and (cover ((`t_1`.`primary_key_id`)) is null)))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "`t_2`",
"star": true
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"sort_terms": [
{
"expr": "(`t_2`.`int_field1`)"
},
{
"expr": "(`t_2`.`decimal_field1`)"
},
{
"expr": "cover ((`t_1`.`primary_key_id`))"
},
{
"expr": "cover ((`t_1`.`char_field1`))"
}
]
},
{
"#operator": "FinalProject"
}
]
},
"text": "SELECT t_2.* FROM multiple_table_db_29807922_simple_table_1 t_3 LEFT JOIN multiple_table_db_29807922_simple_table_4 t_2 ON ( t_3.decimal_field1 = t_2.decimal_field1 ) LEFT JOIN multiple_table_db_29807922_simple_table_2 t_1 ON ( (t_2.primary_key_id = t_1.primary_key_id AND t_2.int_field1 = t_1.int_field1) ) WHERE (t_3.bool_field1 != false OR (t_3.char_field1 IN [ \"A\" , \"B\" , \"C\" , \"D\" , \"E\" ]) AND (t_1.primary_key_id IS NULL)) ORDER BY t_2.int_field1 , t_2.decimal_field1 , t_1.primary_key_id , t_1.char_field1"
}