use lower(column) column is text data get error

[hongyu-presight]

hi dpgaspar, when i execute this sql, i get an error
SELECT news_ids FROM mediasense-entity-person-profile-2024 WHERE LOWER(entity_name) = 'martha patricia molina';
if entity_name index type is text, use LOWER will get this error

File "/Users/abc/miniconda3/lib/python3.12/site-packages/es/baseapi.py", line 36, in wrap return f(self, *args, **kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^ File "/Users/abc/miniconda3/lib/python3.12/site-packages/es/opendistro/api.py", line 278, in execute results = self.elastic_query(query) ^^^^^^^^^^^^^^^^^^^^^^^^^ File "/Users/abc/miniconda3/lib/python3.12/site-packages/es/baseapi.py", line 329, in elastic_query raise exceptions.ProgrammingError( es.exceptions.ProgrammingError: (Error occurred in OpenSearch engine: all shards failed): Shard[0]: [mediasense-entity-person-profile-2024/sjj6R8QtRDGB1un4NDz7GQ] QueryShardException[failed to create query: compile error]; nested: ScriptException[compile error]; nested: IllegalArgumentException[unexpected token [''%china%''] was expecting one of [{<EOF>, ';'}].];

but if index type is keyword , sql work well

i use this sql just to simulate ES match operation

can you help me take a look this problem, if you have some other method can implement ES match function , plz let me know , i am a new learner , thanks

[dpgaspar]

Hi,

Sorry for the delay, were you able to solve this issue?

Not seeing a LOWER function on OpenSearch here: https://opensearch.org/docs/latest/search-plugins/sql/functions/#string
Have you tried using upper?

Please share your OpenSearch version