I am trying to sum an INT column as was getting Arithmetic overflow errors.

This thread http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=23137&HighLight=1 has some code that solves that, which works fine on SQL Server. I am also running against MySQL (actually MariaDB 10.3.11). The problem is the code fails on MySQL. This is using LLBLGen Pro v5.5.1 with the build from 27-Nov-2018.

        private static long GetSum(IDataAccessAdapter dataAccessAdapter, IEntityField2 field, IPredicate filter = null)
        {
            var qf = new MyData.FactoryClasses.QueryFactory();
            var sq = qf.Create().Select(ConvertFunctions.ToInt64(field).Sum());
            if (filter != null)
            {
                sq.Where(filter);
            }
            return dataAccessAdapter.FetchScalar<long?>(sq) ?? 0;
        }

The offending SQL is:

SELECT SUM(CAST(`table`.`field` AS BIGINT)) AS `ExF`
FROM `table`
WHERE (((`table`.`field` IS NOT NULL)))
LIMIT 1

Per https://stackoverflow.com/questions/4660383/how-do-i-cast-a-type-to-a-bigint-in-mysql it is not legal to CAST as BIGINT. However, it does work to CAST as SIGNED (or UNSIGNED). I think the problem is ConvertFunctions.ToInt64 does a CAST as BIGINT.

I worked around this for now by changing the code to use ConvertFunctions.ToDouble and FetchScalar<double?>. It loses some precision but generates valid MySQL queries. If you agree with my analysis, please consider fixing this in a future release.

Thank you!

• What is the real type of the column in DB (int, signed, unsigned...)?
• What is the type of the field in your model?
• Please show us the complete exception message and stack trace.

MySQL... never a dull moment.

https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html#function_convert

You're right. The cast should be CAST(... AS UNSIGNED), why 'BIGINT' doesn't work is beyond me, likely because the person who wrote that code was as drunk as the person who wrote the documentation page linked above.

We'll look into correcting this.

Excellent--I agree that MySQL is odd. Thank you!

Fixed in next build. (TinyInt/SmallInt/Int/Varchar/Real conversions were broken too We've added workarounds for those). Hotfix build is available tomorrow.

(edit) fix for this is now available in hotfix builds 5.4.4 and 5.5.1