Description
This query:
SELECT t_2.* FROM multiple_table_db_492_simple_table_10 t_4 INNER JOIN multiple_table_db_492_simple_table_4 t_2 ON ( NOT ((t_4.char_field1 = t_2.char_field1 AND t_4.int_field1 = t_2.int_field1)) ) WHERE (NOT ((t_2.bool_field1 = true AND t_2.decimal_field1 < 4878))) OR ((((t_2.bool_field1 != true AND t_4.int_field1 IN [ 0 , 12 , 21 , 32 , 39 ])) OR ((t_2.decimal_field1 != 4878) OR (t_4.decimal_field1 >= 4821))) AND (NOT (((NOT (t_4.bool_field1) OR t_2.char_field1 LIKE "N")) AND ((t_2.decimal_field1 BETWEEN 5 and 9985 AND t_2.varchar_field1 IS NULL AND t_2.bool_field1 = false))))) LIMIT 100
Returns this error:
[
{
"code": 4330,
"msg": "No index available for ANSI join term t_2",
"query_from_user": "SELECT t_2.* FROM multiple_table_db_492_simple_table_10 t_4 INNER JOIN multiple_table_db_492_simple_table_4 t_2 ON ( NOT ((t_4.char_field1 = t_2.char_field1 AND t_4.int_field1 = t_2.int_field1)) ) WHERE (NOT ((t_2.bool_field1 = true AND t_2.decimal_field1 < 4878))) OR ((((t_2.bool_field1 != true AND t_4.int_field1 IN [ 0 , 12 , 21 , 32 , 39 ])) OR ((t_2.decimal_field1 != 4878) OR (t_4.decimal_field1 >= 4821))) AND (NOT (((NOT (t_4.bool_field1) OR t_2.char_field1 LIKE \"N\")) AND ((t_2.decimal_field1 BETWEEN 5 and 9985 AND t_2.varchar_field1 IS NULL AND t_2.bool_field1 = false))))) LIMIT 100"
}
]
However I have an index on t_2.char_field1 and t_2.int_field1, is the NOT somehow interfering here? Other indexes are t_4 each field individually as well as each field in one big index, t_2 each field individually as well as each field in one big index. You can use the backup I gave in the last bug filed, it should be the same environment more or less. Logs attached