Proposal table view query error

[mutambaraf]

What is the problem and why is it a problem

We have noted the following errors which are related to proposal_table_view table.

{"message":"QUERY ERROR select *, count(*) OVER() AS full_count from \"proposal_table_view\" inner join \"users\" on \"users\".\"user_id\" = \"proposal_table_view\".\"principal_investigator\" where \"proposal_pk\" in (select \"proposal_pk\" from \"proposal_table_view\" inner join \"call_has_instruments\" as \"chi\" on \"chi\".\"call_id\" = \"proposal_table_view\".\"call_id\" inner join \"instruments\" as \"in\" on \"in\".\"instrument_id\" = \"chi\".\"instrument_id\" left join \"instrument_has_scientists\" as \"ihs\" on \"ihs\".\"instrument_id\" = \"chi\".\"instrument_id\" where (\"proposal_table_view\".\"call_id\" not in ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16) and \"ihs\".\"user_id\" = $17 or \"in\".\"manager_user_id\" = $18)) and (\"title\" ilike $19 or \"proposal_id\" ilike $20 or \"proposal_status_name\" ilike $21 or \"users\".\"email\" ilike $22 or \"users\".\"firstname\" ilike $23 or \"users\".\"lastname\" ilike $24 or 1 = $25 or jsonb_path_exists(instruments, '$[*].name 276 (@.type() == \"string\" && @ like_regex \"bam\".\"de\" flag \"i\")')) and \"proposal_status_id\" not in ($26) order by \"proposal_pk\" desc limit $27 - syntax error at or near \".\" of jsonpath input","error":{"length":118,"name":"error","severity":"ERROR","code":"42601","position":"905","file":"jsonpath_scan.l","line":"294","routine":"jsonpath_yyerror"},"obj":{"__knexUid":"__knexUid2524","__knexTxId":"__knexUid2524","method":"select","options":{},"timeout":false,"cancelOnTimeout":false,"bindings":[276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,8861,8861,"%bam.de%","%bam.de%","%bam.de%","%bam.de%","%bam.de%","%bam.de%",0,9,200],"__knexQueryUid":"3SDMiVU8cAvplAMZgwws3","sql":"select *, count(*) OVER() AS full_count from \"proposal_table_view\" inner join \"users\" on \"users\".\"user_id\" = \"proposal_table_view\".\"principal_investigator\" where \"proposal_pk\" in (select \"proposal_pk\" from \"proposal_table_view\" inner join \"call_has_instruments\" as \"chi\" on \"chi\".\"call_id\" = \"proposal_table_view\".\"call_id\" inner join \"instruments\" as \"in\" on \"in\".\"instrument_id\" = \"chi\".\"instrument_id\" left join \"instrument_has_scientists\" as \"ihs\" on \"ihs\".\"instrument_id\" = \"chi\".\"instrument_id\" where (\"proposal_table_view\".\"call_id\" not in ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16) and \"ihs\".\"user_id\" = $17 or \"in\".\"manager_user_id\" = $18)) and (\"title\" ilike $19 or \"proposal_id\" ilike $20 or \"proposal_status_name\" ilike $21 or \"users\".\"email\" ilike $22 or \"users\".\"firstname\" ilike $23 or \"users\".\"lastname\" ilike $24 or 1 = $25 or jsonb_path_exists(instruments, '$[*].name ? (@.type() == \"string\" && @ like_regex \"bam\".\"de\" flag \"i\")')) and \"proposal_status_id\" not in ($26) order by \"proposal_pk\" desc limit $27"},"QueryName":"select *, count(*) OVER() AS full_count from \"proposal_table_view\" inner join \"users\" on \"users\".\"user_id\" = \"proposal_table_view\".\"principal_investigator\" where \"proposal_pk\" in (select \"proposal_pk\" from \"proposal_table_view\" inner join \"call_has_instruments\" as \"chi\" on \"chi\".\"call_id\" = \"proposal_table_view\".\"call_id\" inner join \"instruments\" as \"in\" on \"in\".\"instrument_id\" = \"chi\".\"instrument_id\" left join \"instrument_has_scientists\" as \"ihs\" on \"ihs\".\"instrument_id\" = \"chi\".\"instrument_id\" where (\"proposal_table_view\".\"call_id\" not in ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16) and \"ihs\".\"user_id\" = $17 or \"in\".\"manager_user_id\" = $18)) and (\"title\" ilike $19 or \"proposal_id\" ilike $20 or \"proposal_status_name\" ilike $21 or \"users\".\"email\" ilike $22 or \"users\".\"firstname\" ilike $23 or \"users\".\"lastname\" ilike $24 or 1 = $25 or jsonb_path_exists(instruments, '$[*].name ? (@.type() == \"string\" && @ like_regex \"bam\".\"de\" flag \"i\")')) and \"proposal_status_id\" not in ($26) order by \"proposal_pk\" desc limit $27","level":"error","timestamp":"2025-08-13T13:25:55.220+01:00"}

Steps to reproduce (if it's a bug).

1. Login with instrument scientist role.
2. Searching anything with a full stop (.) such as bam.de

[ellen-wright]

Sprint planning: @YufanKambang @mutambaraf Testing edge case to keep an eye on - validation needed on frontend

[simonfernandes]

I'm not sure that frontend validation to restrict . characters is the best way forward, it seems reasonable that a user should be allowed to search with it. When we looked into this previously we found that the root cause is that the backend isn't properly handling . characters here. It would be good to make sure that using a . in the search box is covered by an e2e test.