We help IT Professionals succeed at work.

I cannot figure out why this behavior in MariaDB

Ludwig Diehl
Ludwig Diehl asked
on
62 Views
Last Modified: 2019-01-15
Hello everyone. I was trying to understand why this is happening but still can't figure it out. I try this in MariaDb 10.1. I have attached the sqldump file.
So given this query:

SELECT 
	  CONCAT(
	    LPAD((IFNULL(MAX(a.test), 0) + 1), 4, "0"),
	    " - ",
	    YEAR(NOW()),
	    " - ",	    
	    IFNULL(b.description,'')
	  ) AS numero 
FROM
	tbl_test a 
        JOIN tbl_status b USING(id_status)
WHERE 	
       a.id_type='1'
	AND a.id_status ='2'
	AND a.year = YEAR(NOW())	
	

Open in new window


I get "0001 - 2019 - "

However for this one:

SELECT 
	  CONCAT(
	    LPAD((IFNULL(MAX(a.test), 0) + 1), 4, "0"),
	    " - ",
	    YEAR(NOW()),
	    " - ",	    
	    IFNULL(b.description,'')
	  ) AS numero 
FROM
	tbl_test a 
        JOIN tbl_status b USING(id_status)	
WHERE 	
        a.id_type='1'
	AND a.id_status =2
	AND a.year = YEAR(NOW())	
	

Open in new window


I get "0001 - 2019 - status2"


Is there any explanation for this?
database.sql
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
My eyes are getting old but I'm not seeing the difference between those two statements.
Ludwig DiehlSystems Architect

Author

Commented:
I am sorry I corrected it
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I've done very little with MariaDB.  I've done more with MySQL.

I find it weird that you want IFNULL data back with a MAX that doesn't have a 'GROUP BY' but technically it is allowed syntax.

That said:  Seems like a bug.

If you run it against MySQL 8, you get what you might expect.  As soon as you select a MariaDb, you get bad data.

Interesting that if you remove the primary key on tbl_status it magically removes 'status2' in the description.

My experiments:
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3389d6758a72594ff367a2c5bd175293
nociSoftware Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
The difference is a.id_status = 2 vs. a.id_status='2'
comparing an int to a character (string) should not match...   hence no description or null description.
and you do a join with out join expression...

SO from one query you will get only get NULL values getting filled out to use the current year & number one, no status.
The other one  provides null values and status 2 (joined to nothing...).

try:

SELECT 
        *
FROM
	tbl_test a 
        JOIN tbl_status b USING(id_status)	
WHERE 	
        a.id_type='1'
	AND a.id_status =2
	AND a.year = YEAR(NOW())

Open in new window

vs.
SELECT 
        *
FROM
	tbl_test a 
        JOIN tbl_status b USING(id_status)	
WHERE 	
        a.id_type='1'
	AND a.id_status ='2'
	AND a.year = YEAR(NOW())

Open in new window


intended? code:

SELECT 
        *
FROM
	tbl_test a 
        LEFT JOIN tbl_status b ON (a.id_status = b.id_status) 	
WHERE 	
        a.id_type='1'
	AND a.id_status =2
	AND a.year = YEAR(NOW())

Open in new window


providing a more stable query...
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>comparing an int to a character (string) should not match...

I first thought it was an implicit data type conversion as well but it didn't seem to be the case.

With MySQL and MariaDb:
Both these return 1:
select 1='1';
select 1=1;

Both these return 0:
select 1=2;
select 1='2';

https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=3bb0306c26aecd679e19b90c9be31eb8
nociSoftware Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Ah ok. didn't verify that in the end, i just omitted formatting and the join looked odd to me.
Forgot to remove that from the message
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>and the join looked odd to me.

What looked odd about it?
Ludwig DiehlSystems Architect

Author

Commented:
At first it seems Mariadb makes some sort of data type conversion only when it does not find the value in the left table but if you try with a non-existing value in status like "a.id_status =3" you get the same result.
It is unexpected because it is supposed  to be a join but looks like aggregate functions in these cases give unexpected results.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I don't think it is the aggregate per say.  If you remove the IFNULL's you don't get records back.

If you add the group by that should be there, you also don't get rows back.

The MySQL docs comment on it:
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

If ONLY_FULL_GROUP_BY is disabled ... In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want.
nociSoftware Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
I was missing ON  with condition.
(and LEFT as there seems an intend to translate a statusid..)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>I was missing ON  with condition.

USING can be used in place of ON in a join if the columns are the same name.
Ludwig DiehlSystems Architect

Author

Commented:
I don't think it is the aggregate per say.  If you remove the IFNULL's you don't get records back.

If you add the group by that should be there, you also don't get rows back.

That's true!. I never thought about it, however, it is still unexpected how results are calculated isn't it?.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Ludwig DiehlSystems Architect

Author

Commented:
Thank you very much everyone to help me clear things up. I should better stick to SQL standards as much as possible.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Glad to help and I too learned from this!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.