nan/inf in mysql handler

[youngsofun]

https://dev.mysql.com/doc/refman/5.7/en/problems-with-float.html
its nan` here

python driver seems to like "nan"? @ariesdevil

mysql client display "NULL"

mysql> select 1/0
    -> ;
+------+
| 1/0  |
+------+
| NULL |

mysql jdbc not accept 'inf':
#8929 (comment)

[youngsofun]

cc @sandflee

[sundy-li]

mysql has no inf or nan for float value, it can only accepts number or null.

[youngsofun]

ok, so we should output NULL (without quote)?

[youngsofun]

and do not need to consider "NULL" as valid input for float not null?

[youngsofun]

mysql jdbc accept NaN
https://github.com/mysql/mysql-connector-j/blob/27603148f10a5f47467bec7ad26a5ca28da63c72/src/main/protocol-impl/java/com/mysql/cj/protocol/a/MysqlTextValueDecoder.java#L248

https://stackoverflow.com/questions/10750975/parse-string-to-double-nan

[youngsofun]

https://docs.oracle.com/javase/7/docs/api/java/lang/Double.html#valueOf%28java.lang.String%29

[youngsofun]

because MySQL client seems to print what the server sent it and it print NULL

[FANNG1]

yes, null is reasonable. but in our env, we prefer to keep consistent with hive-jdbc. how about output Nan Infinity if sql_dialect is Hive , otherwise output NULL ?

[youngsofun]

python, java , and golang is mostly used,
They all support "NaN" & "Infinity"
we can just use them for MySQL?

[youngsofun]

cc @sundy-li

[youngsofun]

@sundy-li @ariesdevil

in logic_test, MySQL connector convert string to python float then we print the python float

(venv) ➜  python 
Type "help", "copyright", "credits" or "license" for more information.
>>> float("1.1")
1.1
>>> float("nan")
nan
>>> float("NaN")
nan
>>> float("Inf")
inf
>>> float("infinity")
inf
>>> float("infinity")

[sundy-li]

So we can wrap the result to be NaN or disable the tests

[youngsofun]

like python, golang ParseFloat support multi form toohttps://pkg.go.dev/strconv#ParseFloat

[youngsofun]

https://pkg.go.dev/strconv#ParseFloat