Cache numeric comparison via ODBC something is not working

Question

Question

Mark Runyan · Jul 6, 2021

How strange this is to me. I'm querying a table against a numeric(15,2) column call max_client_resp_service. When I query max_client_resp_service > 0 or any number, I get results, but they are not always right, for example where max_client_resp_service > 5 returns values less than 5, e.g. 2.00. When I query < 110 of any higher number I get zero results. It's as though the comparison operator, especially less than function is not working. See attachments for screen shots. I'm stumped. Ok looking at this further, it appears ">" returns all records, "<" returns no results.

Product version: Caché 2017.1

$ZV: Cache 2017.2.2 (Build 867_4_20245)

cache_numeric_operator_problem_less_than.pdf

Discussion (6)2

Log in or sign up to continue

Sergei Shutov · Jul 6, 2021

Did you try to rebuild indices and purge cached queries?

Also, have a look at the execution plan in the Management portal -- it will tell you which indices it uses and how it builds a result.

0 0

Sergei Shutov · Jul 7, 2021

You can use %IGNOREINDEX and %NOINDEX keywords, have a look here in documentation

https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...

0 0

Mark Runyan · Jul 30, 2021

I found a work around, using to_number function around the column query, e.g. where to_number(max_client_resp_service) < 110 yields correct results. So strange it's as if the numeric(15,2) column is being stored or queried as an varchar string. Perhaps there's a wrong typed index on this column, but I can't seem to determine the index type if any on this column from INFORMATION_SCHEMA.INDEXES.

0 0

score 0 · Answer 1 · 2021-07-07T02:03:29-04:00

Excellent suggestions Sergei. Yes I believe the index does need to be rebuilt! But I'll have to ask my hosting company to do that. I wonder if there's a query hint to to suppress the index temporarily.

score 0 · Answer 2 · 2021-07-07T11:44:39-04:00

Thanks. I used %IGNOREINDEX and %NOINDEX optimizer hints and got the same results. And looking at my Cache Monitor generated DDL it looks like there's not an index on this column. There appears to be one clustered index on the table and it doesn't include the max_client_resp_service field.

score 0 · Answer 3 · 2021-07-08T02:30:56-04:00

I don't know the answer but you could try to query the max_cover_partdays or other numeric without decimal part to check if it causes issues. Or try to add it to your query, something like <109.99

It looks to me like there's some casting or parseing type ocurring somehow and greater than X just checks if the value is not null?

I never had any issue like this, besides dealing with decimal separator between systems or dealing with null and empty value conversions/replacements to avoid runtime exceptions.