MOD() generates Float value but creates LargeInt field type

[sdaberle]

I'm using Devart components in Delphi XE10.2 VCL. I have a TMyQuery component with SQL code that includes the following:

Code: Select all

SELECT ... MOD((CellNbr - 1), :Columns) + 1 AS ColumnNbr FROM ... 

where :Columns is an Integer input parameter. When I use the Field editor to "Add all fields", it adds ColumnNbr as a TLargeIntField, as I would expect, since MOD should return an integer result. The query executes in design mode, but when I run the app and activate it, I get a type mismatch error for field ColumnNbr: expecting 'LargeInt', actual: 'Float'. Why would this expression be returning a Float value instead of an integer?

Anticipating the question: yes, there are times when :Columns will be zero. In these cases, the expression returns NULL, which should be an acceptable value for a TLargeIntField.

If I manually change the ColumnNbr field to make it a TFloatField, the query works, as it does if I change the expression to read

Code: Select all

CAST(MOD((CellNbr - 1), :Columns) + 1 AS Integer) AS ColumnNbr

-- so I can work around the issue, but I'm curious to understand why it crops up in the first place.

[ViktorV]

The MOD function can return both integer and fractional values: https://dev.mysql.com/doc/refman/5.7/en ... nction_mod
MyDAC creates a field based on the information passed by the server. Please make sure that in the runtime and design time you connect to the same database and execute the queries with the same conditions and do not use data mappings. If so, please compose a small sample demonstrating the issue and send it to us via the e-support form: http://devart.com/company/contactform.html including scripts for creating database objects. Also, please specify the exact version of the used MySQL server.