Same query behaves different in MariaDB 10.1.37 and MySQL 5.7.27

[fumeeptc]

Hello!

I made query for a while loop in my test environment (with MariaDB 10.1.3) and all worked good!
Now i moved same code to main website (with MySQL 5.7.27) and this is where it gets weird

Result now given on every refresh changes. …

Query has multiple LEFT JOIN's (some of them are union)

Query:

SELECT l.id               AS lid,
       l.docNr            AS receive_docNr,
       l.thisTransport    AS receive_transport,
       l.productNr        AS receive_productNr,
       l.activityDate     AS receive_date,
       l.issueDate,
       h.ladingNr         AS receive_ladingNr,
       l.place_count      AS receive_place_count,
       h.cargo_status     AS receive_cargo_status,
       h.clientCode       AS clientCode,
       e.id               AS eid,
       e.docNr            AS docNr,
       e.deliverydate     AS deliveryDate,
       e.activitydate     AS activityDate,
       e.assistant_amount AS assistant_amount,
       e.amount           AS amount,
       e.productnr        AS productNr,
       e.productumo       AS productUmo,
       e.status           AS status,
       e.orgline,
       ni.name1,
       ni.name2,
       h.agreements       AS agreements,
       nc.Name,
       a.uom              AS uom,
       au.uom             AS auuom,
       ez.status          AS istatus,
       ez.place_count     AS iplace_count,
       ez.activityDate    AS iactivityDate,
       ez.place_count     AS iassistant_amount
FROM   cargo_line AS l

LEFT JOIN (

	(
		SELECT id,
		docNr,
		deliverydate,
		activitydate,
		assistant_amount,
		amount,
		productnr,
		productumo,
		status,
		orgLine,
		resource,
		cargoLine AS mold
		FROM   item_ledger_entry
	)
	UNION
   (
		SELECT id,
		docNr,
		deliverydate,
		activitydate,
		assistant_amount,
		amount,
		productnr,
		productumo,
		status,
		orgLine,
		resource,
		orgLine AS mold
		FROM   item_ledger_entry
	)
	
) AS e
ON e.mold = l.id
		
LEFT JOIN (
	
	(
		SELECT status,
		orgLine,
		place_count,
		activityDate,
		cargoLine AS mold
		FROM   item_ledger_entry
	)
	UNION
	(
		SELECT status,
		orgLine,
		place_count,
		activityDate,
		orgLine AS mold
		FROM   item_ledger_entry
	)
	
) AS ez
ON ez.mold = l.id

LEFT JOIN cargo_header AS h
ON l.docNr = h.docNr

LEFT JOIN agreements_lines AS a
ON h.agreements = a.contractnr
AND e.productnr = a.item
AND e.resource = a.service

LEFT JOIN additional_uom AS au
ON e.productnr = au.productnr
AND au.status = 1
AND au.convert_from = 1

LEFT JOIN n_customers AS nc
ON h.clientCode = nc.Code

LEFT JOIN n_items AS ni
ON l.productNr = ni.code

WHERE  h.clientCode = '1234567890'
AND h.agreements = 'AGR00001'
#AND DATE_FORMAT(l.issueDate, '%Y-%m-%d') BETWEEN '2020-06-01' AND '2020-06-30'

GROUP  BY l.id
ORDER  BY l.activityDate ASC, l.docNr

I could not find useful info about why this could why does it happen. …

Anyone has any ideas?
Thanks in advance!

[fumeeptc]

if i remove union part it does not changes so it is union that does it! But why?

[m_hutley]

The query is going to be required. It would seem you have some ambiguous code…

[fumeeptc]

Query added!

[r937]

@fumeeptc

Result now given on every refresh changes. …

please describe what changes… number of rows?

is new data being added to the tables between refreshes?

also, please explain what you need GROUP BY for, and how you managed to turn off the group by errors this query will produce

[m_hutley]

GROUP BY would generally be used to pull an aggregate of the group in some way - COUNTs, SUMs, MAX, etc.

The database should be screaming at you that it would produce unpredicable results given that you’re saying the equivalent of:

“Take all the Hondas we sold. Now tell me the singular name of the owner.”

(The group does not have a singular property ‘owner’. It has many owners. The only way to produce an answer is to select a member of the group.)

[fumeeptc]

@r937 @m_hutley If i do not use GROUP BY i get like 4 times more rows

[r937]

@fumeeptc

If i do not use GROUP BY i get like 4 times more rows

that means your joins are producing rows you don’t want

what about my other questions?

please describe what changes… number of rows?

is new data being added to the tables between refreshes?

[fumeeptc]

@r937

that means your joins are producing rows you don’t want

What is wrong in my query? How can i make it show me what i want without GROUP BY

@r937

please describe what changes… number of rows?

Row count seems to be correct! values also are correct but EUR values are not correct (calculated before output based on date from query result). On heidisql data seems does not change . …

@r937

is new data being added to the tables between refreshes?

No there are no new data!

[m_hutley]

@fumeeptc

How can i make it show me what i want without GROUP BY

We’d need to know what you actually want it to show you.

For example, you are pulling “nc.Name” - which is a customer name.

But which customer name? You’re grouping by cargo_line ID - does a cargo line only have 1 customer in it? (Your statement that “I get like 4 times more rows” when not grouping tells us that 1 cargo line has approximately 4 things going on at a time) - if they’re not all for the same customer, which of the 4 names do you want to pull out of your data?

@fumeeptc

values also are correct but EUR values are not correct

This line means nothing to me, as your query makes no mention of an “EUR” and i’m not familiar with your database.

[r937]

@fumeeptc

What is wrong in my query?

please understand, i don’t mean this as disparaging, but… lots

@fumeeptc

How can i make it show me what i want without GROUP BY

rewrite your joins

[fumeeptc]

@m_hutley @r937 What I want thees joins to do is get extra columns from other tables not rows

where i wrote it wrong?

As you can see in this answer: https://stackoverflow.com/a/7669215/5459942 he suggest to use GROUP BY and it seems to be same situation
same here: https://stackoverflow.com/a/7711436/5459942

EDIT: i figured out that e and ez are the joins that gives extra rows. … so how could i rewrite it to show me correct amount without using GROUP BY?

[r937]

@fumeeptc

As you can see in this answer: https://stackoverflow.com/a/7669215/5459942 he suggest to use GROUP BY

this proves you cannot rely on stuff you read on the internet

[m_hutley]

To be fair, the stackoverflow post is at least accurate (or one way of doing it) - for that particular use case (where the user wanted to COUNT the number of comments).

Let’s… try this.
Run the query without a group by, pick a cargo_line ID, and show us:
A: All of the resulting rows for that cargo_line ID,
B: What you think the result should look like when you’re done.

[SpacePhoenix]

What’s the structure of each table in use?

Could you provide an example of the required output (and maybe sample data)?

[fumeeptc]

I can not post that data. … It is sensitive data. As i know e and ez joins makes rows to create new rows (basically same rows) if i use GROUP BY i get correct number of rows but union in these left joins does something weird. …

I changed e and ez left joins

LEFT JOIN (

	SELECT * FROM (

		(
		
			SELECT id,
			docNr,
			deliverydate,
			activitydate,
			assistant_amount,
			amount,
			productnr,
			productumo,
			status,
			orgLine,
			resource,
			cargoLine AS mold
			FROM   item_ledger_entry
		
		)
	UNION
	(
	
			SELECT id,
			docNr,
			deliverydate,
			activitydate,
			assistant_amount,
			amount,
			productnr,
			productumo,
			status,
			orgLine,
			resource,
			orgLine AS mold
			FROM   item_ledger_entry
			
		)
	
	) e
	WHERE e.mold>0 AND e.status=40 AND DATE_FORMAT(activityDate, '%Y-%m-%d') BETWEEN '".date('Y-m-d', strtotime($from))."' AND '".date('Y-m-d', strtotime($to))."'
	order by activityDate DESC, docNr, id
	
	
	
) AS e
ON e.mold = l.id
				
LEFT JOIN (

	SELECT * FROM (

		(
		
			SELECT id, status,
			orgLine,
			place_count,
			activityDate,
			docNr,
			cargoLine AS mold
			FROM   item_ledger_entry
			
		)
		UNION
		(
		
			SELECT id, status,
			orgLine,
			place_count,
			activityDate,
			docNr,
			orgLine AS mold
			FROM   item_ledger_entry
			
		)
	
	) ez
	WHERE ez.mold>0 AND ez.status=40 AND DATE_FORMAT(activityDate, '%Y-%m-%d') BETWEEN '".date('Y-m-d', strtotime($from))."' AND '".date('Y-m-d', strtotime($to))."'
	order by activityDate DESC, docNr, id
	
		
	
) AS ez
ON ez.mold = l.id

and for main query order by i added e.id DESC and it seems to show correct numbers (for now) I believe query is still wrong coded but it works. .

I do not know. … I m not a MySQL specialist yet and i do not know best tips or tricks or best practices. … Came here to get help not to ask to do it for me. … Also i asked why it works on MariaDB and not in MySQL

Sad that no one could offer atleast guessing example of query as that happens in stackoverflow. …

But for now. … Thanks @r937 @r937 for your time!

[r937]

@fumeeptc

if i use GROUP BY i get correct number of rows but union in these left joins does something weird. …

we cannot help diagnose a data problem without seeing some data

if it’s sensitive, obfuscate it

but please dump a few rows of sample data in each table, so we can run the query ourselves

this means provide us with the CREATE TABLE statements, along with enough INSERT statements of sample data to form a representative test case

as for the difference between database platforms, that might be due to how they handle invalid grouping queries

if you don’t know what that means, please read https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html

look at all the non-aggregated columns in your SELECT clause, then look at your GROUP BY clause, only 1 column – that’s invalid, and i don’t care what stackoverflow says

i mean, look at this crap –

SELECT blogs.*,Count(comments.comment_ID) 
FROM blogs 
LEFT JOIN comments 
ON blogs.blog_id=comments.blog_id WHERE blogs.blog_id = $active 
ORDER BY comments.comment_id
GROUP By Blog.BlogID

not only does this use the dreaded evil “select star” along with GROUP BY, it doesn’t even have the GROUP BY and ORDER BY clauses in the right order

stackexchange is suspect at the best of times