Left join no longer works as expected after the upgrade from version 6.3.8 to 9.3.2

[giocondo]

Bug Description:

Steps to reproduce the error:

create table `t1` (

`id` bigint,
`type` string indexed attribute,
`content` text

) morphology = 'libstemmer_it' index_exact_words = '1' min_word_len = '1' min_prefix_len = '3';

create table `t2` (

`id` bigint,
`from_id` bigint,
`to_id` bigint

) morphology = 'libstemmer_it' index_exact_words = '1' min_word_len = '1' min_prefix_len = '3';

insert into `t1` values(1, 'typeA', 'row 1 text');
insert into `t1` values(2, 'typeA', 'row 2 text');
insert into `t1` values(3, 'typeB', 'row 3 text');
insert into `t1` values(4, 'typeC', 'row 4 text');

insert into `t2` values(1, 1, 2);
insert into `t2` values(2, 2, 3);
insert into `t2` values(3, 2, 4);
insert into `t2` values(4, 3, 4);

This query no longer works:

select t1.`type` as `type`, `from_id` from t2 left join t1 on t2.from_id = t1.id where `from_id` = 2 and `type` = 'typeA';

Empty set (0.001 sec)

This modified query works:

select t1.`type` as `t1_type`, `from_id` from t2 left join t1 on t2.from_id = t1.id where `from_id` = 2 and `t1_type` = 'typeA';
+---------+---------+
| t1_type | from_id |
+---------+---------+
| typeA   |       2 |
| typeA   |       2 |
+---------+---------+

This query behaves oddly, producing a null value for the "t1.content" field:

select t1.`content`, t1.`type` from t2 left join t1 on t2.from_id = t1.id where `from_id` = 2 and t1.`type` = 'typeA';
+--------------+-----------+
| t1.`content` | t1.`type` |
+--------------+-----------+
|              | typeA     |
|              | typeA     |
+--------------+-----------+

this without backticks works:

select t1.content, t1.`type` from t2 left join t1 on t2.from_id = t1.id where `from_id` = 2 and t1.`type` = 'typeA';
+------------+-----------+
| t1.content | t1.`type` |
+------------+-----------+
| row 2 text | typeA     |
| row 2 text | typeA     |
+------------+-----------+

Moreover, just to avoid opening another task, this standard SQL syntax should be supported but as of now it isn't in any Manticore version:

select `t1`.`type` as `type` from `t2` left join `t1` on `t2`.`from_id` = `t1`.`id` where `type` = 'typeA';

Thank you.

Manticore Search Version:

9.3.2

Operating System Version:

Debian 10 (5.10.237-1)

Have you tried the latest development version?

No

Internal Checklist:

^{To be completed by the assignee. Check off tasks that have been completed or are not applicable.}

• Implementation completed
• Tests developed
• Documentation updated
• Documentation reviewed