Select with dash doesn't work

[JTechnik]

SELECT * FROM index WHERE field LIKE '%-%' - finds nothing although the field contains the character '-'. Possibly due to the interpretation of the dash, but I cannot say more precisely.

[dai-chen]

@JTechnik Thanks for reporting the issue! Will have a look.

[chloe-zh]

I was not able to reproduce this issue locally, but the like predicate with dash within the regex works fine, for example:

sample data:

PUT userdata/_mapping
{
  "properties": {
    "firstname": {
      "type": "keyword"
    },
    "lastname": {
      "type": "keyword"
    }
  }
}

PUT userdata/_bulk
{"index":{"_id":"1"}}
{"firstname":"Amber-JOHnny","lastname":"Duke-Willmington"}

query example:

POST _opendistro/_sql
{
  "query": "select * from userdata where lastname like '%-%'"
}

response:

{
  "schema": [
    {
      "name": "firstname",
      "type": "keyword"
    },
    {
      "name": "lastname",
      "type": "keyword"
    }
  ],
  "total": 1,
  "datarows": [[
    "Amber-JOHnny",
    "Duke-Willmington"
  ]],
  "size": 1,
  "status": 200
}

Could you provide some sample data and the query example you used when this issue came up, that would be much helpful to figure out the problem. Thanks!

[JTechnik]

 "_source": {
    "userId": "131",
    "userLastName": "Borys-25",
    "userFirstName": "Borys - 25",
}

My request goes through JdbcTemplate which is Bean and configured by Spring. Key points look like this:

@Slf4j
@Repository
@RequiredArgsConstructor
public class ElasticRepositoryImpl implements ElasticRepository {

@Qualifier("elasticTemplate")
 private final JdbcTemplate jdbcTemplate;

 public List<User> elasticMethod() {
       select2 = SELECT * FROM index WHERE userLastName LIKE '%Borys%' // this works fine
       select = SELECT * FROM index WHERE userLastName LIKE '%-%' // this not
       select3 = SELECT * FROM index WHERE userLastName LIKE '%Borys-25%' // and this not
       select4 = SELECT * FROM index WHERE userLastName LIKE '%25%' // this works fine
       List<User> result = jdbcTemplate.query(select, userMapper);
    }
}

@Configuration
public class WebConfig {

@Bean(name = "elasticTemplate")
    public JdbcTemplate jdbcTemplate(@Qualifier("elastic") DataSource ds) {
        return new JdbcTemplate(ds);
    }

}

[JTechnik]

I found a solution. If you search in field userLastName.keyword, everything works fine, but it is a little confusing. Why by field userLastName, letters and numbers are searched normally, and special characters need to be searched by field userLastName.keyword. How then to make SQL queries? Always on the userLastName.keyword field...? I haven't seen this in the documentation.

[dai-chen]

@JTechnik Thanks for your info! Looks strange and need more investigation.