How hard can it be to let sql just multiply some values and sum the results, right? As it turns out, damn hard!

I hear you thinking, surely you can just do select SUM(price*amount) AS total right? Nope! I mean, yes you can, but it fucks up. Oddly. It always ends up giving me wrong results. Always. Wtf sql? And it's not like I'm running a massive dataset or anything, it's like 100 records at most?

@epse

Please tell me that you didn't use floating type numbers to store monetary data

@IntrusionCM integers 😉 some parts of the code base use double's when it doesn't matter at all and the calculation will be done properly afterwards anyway, but the DB uses ints

@IntrusionCM is there something fucked with floats and sql perhaps?

@epse the complete float thing is fucked up.

@epse https://0.30000000000000004.com/

@stop yeah that I know, but even float inaccuracy wouldn't explain the results I'm getting. Not even a single precision float would be off by this much

@epse more Info needed. Although I do not comprehend why an Integer is used. Decimal is standard.

Two possible things:
SUM is an aggregate function. Group By clause missing? Or possibly wrong?

Integer overflows?

@epse for example?

@IntrusionCM integer values of cents are standard when working with money, as not all languages have a decimal type. I'll fuck around with it more, it 'should be fine' as per my coworker

@epse Sounds pretty archaic....

What RDBMs?

@IntrusionCM nahh js doesn't have a proper decimal, neither does php and most decimal types are just integers with a wrapper on top. An the db is MySQL (not my preference, but it generally does the job)

@epse ah. The devil might lie in the detail.

https://dev.mysql.com/doc/refman/...

Two ints in, BigInt out. It might be better to try a cast and see If the typing bites ur arse

https://dev.mysql.com/doc/refman/...

Beware of only full group by... If disabled, it can lead to severe confusion when utilizing aggregate function

@IntrusionCM I am very much suspecting a config issue as I cannot for the life of me get it to break on my (Linux) test environments, but it breaks on the (Windows) prod host

@epse share a test case that reproduces the problem

Also, you use Linux in test and windows in production environment? Why not have the same setup throughout the environments, unless you're referring to your workstation as the test environment

@epse

SHOW GLOBAL VARIABLES

Should be ur friend then ...

With special attention to sql_mode / version ... Hm... Interesting.

(SHOW SESSION VARIABLES for runtime / client connection overrides)

Keep me updated If possible, I'm curious

@asgs yeah my workstation is the test enviro and some random PC somewhere is the prod enviro.. Shit is weird here

@asgs also, can't possibly make a test case, because it never fucking breaks on the testing machines. Only on prod and I am not allowed to touch that because customer information

query is pretty much this:

SELECT SUM(receipt_items.price * receipt_items.amount) FROM receipts INNER JOIN receipt_items ON receipt_id=receipts.id WHERE deleted_at = NULL AND WHERE receipts.created_at BETWEEN ? AND ?;

@IntrusionCM This will be my life next time I'm there... After I finish convincing bossman to actually let me on the machine

@epse do you need to group by receipt id or the item id? I don't know your schema

are the test data same in both the environments? Can you run explain plan and see if there is a difference in the (expected) rows scanned

Last but not least, check the actual rows scanned in the mysql general log

@asgs Nope it needs to do everything where that one where clause matches, no grouping. Test data is not the same (I wish, not allowed customer data, not even just the invoices with the names redacted) and maaayyyybeee I can convince bossman to let me run some explain's on the machine and check the logs, once he gets frustrated enough

@epse glad it is not just the windows vs linux being the only suspect. Otherwise, it would be disappointing and infuriating to know. I strongly feel it is a data thing

@asgs my tests feed some seriously garbled data, but the real world is always worse than your worst imagination I guess

@epse wow. either your Boss thinks a developer is an omnipotent being. Or he's dumb. Like in brain dead dumb.

*shakes head*

@IntrusionCM I'd rather say he's paranoid

@epse well... Depending on the country you live in, there are many legal possibilities that exactly prevent this problem. And as a Boss you should know them - or have a good lawyer.

"Faulty" work contracts are not uncommon and a huge risk to the company...

"Good" work contracts do the opposite - while not always in the favor of the employee, they should include a non disclosure agreement and a clear definition of intellectual property / copyright...

And this - IS - doable in every country I know...

Of course you can violate the NDA / license agreement... But depending on country, this would be a - major - crime.

Sorry for venting, but I really hate this topic... I discuss it with every apprentice I work with at the beginning - and usually it ends with the (imho very sad) recommendation to take a look at law insurance and let someone take a look at the working contracts who knows the wormholes...

Out of experience: nearly half of my work contracts were partially wrong... ;)

While they protect the company mostly, work contracts protect the employee, too. Especially from too paranoid people....

Oh yes my contract is very much a bad one. It even fits on a single side of an a4! The only real upside is that because of the lazy contract , I keep the intellectual property

@stop this is due to the IEEE754 standard and not a problem with sql, it effects many languages