Redash: Number-based alerts are broken

[susodapop]

Issue Summary

It looks like the alert always sees a query result as a string and not a number. So an alert configured to trigger when field = 0 never triggers because "0" does not equal 0.

The alerts seems to evaluate fine for targets that are obviously strings. If the alert should trigger if field = boyhowdy then the alert triggers when field is "boyhowdy".

Steps to Reproduce

1. Save a query that returns 0: SELECT 0 "value"
2. Make an alert for the query that triggers when value = 0
3. Run the query
4. Observe the alert does not trigger.

The converse is also true, by the way.

1. Same as above
2. Make an alert for the query that triggers when value != 0
3. Run the query
4. Observe the alert triggers

Technical details:

• Redash Version: 9.0.0-alpha+b348b58b (348b58b)
• Browser/OS: Firefox on MacOS
• How did you install Redash: SaaS

[ranbena]

I believe this is happens cause the threshold input field has been changed to accept texts (to accommodate text comparisons) so all new alerts yield a string from the threshold value.

@rauchy @kravets-levko wdyt of handling this in the backend - casting both args to string for ==/!= and to int for the rest?
https://github.com/getredash/redash/blob/5d585036239e40973adebceadc0dfcceb82e2250/redash/models/__init__.py#L826-L847

[kravets-levko]

I think the solution could be something like: if alert's threshold value contains numeric string and query result's column type is number - cast both to number; otherwise cast both to string:

| Query result column type | Alert threshold value | Cast both to |
|--|--|--|
| number | string containing number | number |
| number | non-numeric string | string |
| other types | string containing number | string |
| other types | non-numeric string | string |

[arikfr]

I think that if query result is a number, then we should try to cast both to number. In other cases: use a string.

Because the operation options we show are based on the query result type, the threshold should adhere. We might want to enforce this in the UI.

[ranbena]

Not sure I understand why casting-by-value would be preferable to casting-by-operation.

[arikfr]

Not sure I understand why casting-by-value would be preferable to casting-by-operation.

Because strings and numbers have different behavior, for example:

In case of a number 0.0 == 0, but in case of strings '0.0' != '0'.

[ranbena]

Aha. So I propose the following:

|Operator| Cast |
|--|--|
|== / !=|Threshold to result value type |
| Other | Both to number |

[ranbena]

Not sure about this, but perhaps we can hint at string values with apostrophes.

[arikfr]

@susodapop reopening as the fix wasn't pushed to this repo yet.