String equal in JOIN

[mitsh]

Proposal:

Could you support string match in join queries?

I share my query just in case maybe I did something wrong in it.

SELECT
    *
FROM
    `programs`
    LEFT JOIN `program_infos` ON
        `programs`.id = `program_infos`.id AND
        `program_infos`.lang='en'
LIMIT 0,5

result:

Query 1 ERROR at Line 1: : P01: syntax error, unexpected string near ''en' AND `programs`.id = `program_infos`.id
LIMIT 0,
5'

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 - [ ] Changelog updated - [x] OpenAPI YAML updated and issue created to rebuild clients

[tomatolog]

could you provide MRE or upload your config along with indexes there this error pops up?

As this query should work fine.

[mitsh]

I don't get it whats MRE stands for (beside Meal Ready to Eat :D). here is the create statement of the tables;

CREATE TABLE `programs`(
  `id` bigint, `p_name` string attribute indexed, 
  `p_version` string attribute indexed, 
  `p_versions` int, `p_publisher` string, 
  `p_homepage` string, `p_seo` string attribute indexed, 
  `p_license` string, `p_os` string, 
  `p_req` string, `p_kats` bigint, `m_p_kats` bigint, 
  `p_size` string, `p_url` string, `p_rurl` string, 
  `p_hit` int, `p_rates` int, `p_ok` int, 
  `p_icon` int, `editor_add_id` int, 
  `editor_id` int, `editor_status` int, 
  `editor_date` timestamp, `p_up_kats` int, 
  `m_p_up_kats` int, `p_up_kat_seo` string attribute indexed, 
  `p_kat_seo` string attribute indexed, 
  `pimage` int, `p_screenshots` int, 
  `google_pr` int, `x_ok` int
)
docstore_block_size = '32K'
attr_update_reserve = '256K'
inplace_enable = '1'
min_prefix_len = '1'
min_infix_len = '3'
index_exact_words = '1'
dict = 'keywords'
expand_keywords = 'star,exact'
stopwords = '/usr/share/manticore/stopwords/en'
html_strip = '1'
index_sp = '1'
morphology = 'libstemmer_en'

CREATE TABLE `program_infos`(
  `id` bigint, `lang` string attribute indexed, 
  `p_sdesc` text, `p_desc` text, `p_tags` string attribute indexed, 
  `p_keywords` string attribute indexed, 
  `seo_title` string attribute indexed, 
  `seo_keywords` string attribute indexed, 
  `seo_description` string attribute indexed
)
docstore_block_size = '32K'
attr_update_reserve = '256K'
inplace_enable = '1'
min_prefix_len = '1'
min_infix_len = '3'
index_exact_words = '1'
dict = 'keywords'
expand_keywords = 'star,exact'
stopwords = '/usr/share/manticore/stopwords/en'
html_strip = '1'
index_sp = '1'
morphology = 'libstemmer_en'

now, I am going to delete all the datas and import with new create statements. maybe it'll work this. let you know.

if you need more information, just let me know.

[sanikolaev]

I believe the MRE (minimal reproducible example) is:

mysql> drop table if exists j; create table j; drop table if exists j2; create table j2(s string); insert into j values(1); insert into j2 values(1, 'a'); select * from j left join j2 on j2.id = j.id and j2.s='a';
--------------
drop table if exists j
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
create table j
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
drop table if exists j2
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
create table j2(s string)
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
insert into j values(1)
--------------

Query OK, 1 row affected (0.00 sec)

--------------
insert into j2 values(1, 'a')
--------------

Query OK, 1 row affected (0.00 sec)

--------------
select * from j left join j2 on j2.id = j.id and j2.s='a'
--------------

ERROR 1064 (42000): P01: syntax error, unexpected string near ''a''

[mitsh]

thanks for the explanation. your example looks like same as mine. if you still want me to create MRE, I can do it later.

[sanikolaev]

We are good @mitsh . Thank you.

[sanikolaev]

We've discussed it internally and it looks like there's no difference in terms of results for the user between AND inside ON and in the WHERE clause.

@mitsh what do you think?