Link to home
Start Free TrialLog in
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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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

ASKER

I am sorry I corrected it
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
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...
>>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
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
>>and the join looked odd to me.

What looked odd about it?
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.
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.
I was missing ON  with condition.
(and LEFT as there seems an intend to translate a statusid..)
>>I was missing ON  with condition.

USING can be used in place of ON in a join if the columns are the same name.
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?.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you very much everyone to help me clear things up. I should better stick to SQL standards as much as possible.
Glad to help and I too learned from this!