Avatar of Ludwig Diehl
Ludwig DiehlFlag for Peru asked on

I cannot figure out why this behavior in MariaDB

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
* MariaDBDatabases

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
slightwv (䄆 Netminder)

My eyes are getting old but I'm not seeing the difference between those two statements.
ASKER
Ludwig Diehl

I am sorry I corrected it
slightwv (䄆 Netminder)

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
noci

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...
slightwv (䄆 Netminder)

>>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
noci

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

>>and the join looked odd to me.

What looked odd about it?
ASKER
Ludwig Diehl

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.
slightwv (䄆 Netminder)

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
noci

I was missing ON  with condition.
(and LEFT as there seems an intend to translate a statusid..)
slightwv (䄆 Netminder)

>>I was missing ON  with condition.

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

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?.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Ludwig Diehl

Thank you very much everyone to help me clear things up. I should better stick to SQL standards as much as possible.
slightwv (䄆 Netminder)

Glad to help and I too learned from this!