Tuesday, July 1, 2014

Mysql - cautious with formatted numbers!

WATCH IT - when using formatted numbers in mysql. This was keeping me busy for a little while, I did not expect this to happen and had do do some searches and tries to find the root cause:

The query for these two is the same , but the first calculation is with formatted numbers, the second with unformatted. What a difference (and what a "curious" behavior in mysql): -172306 vs 97

mysql calculation with 'formatted' number

mysql calculation with not formatted numbers



Just to show the queries in case:
set @one=(select format(count(distinct(Target_URL)),0) from supportdellcom_inbound_links_urls_T);
set @two=(select format(count(distinct(Target_URL)),0) from supportdellcom_backlinks_t);
set @three=(select count(*) from (select Target_URL from supportdellcom_backlinks_t
union
select Target_URL from supportdellcom_inbound_links_urls_T) x)  ;
select (@one + @two - @three);

set @four=(select count(distinct(Target_URL)) from supportdellcom_inbound_links_urls_T);
set @five=(select count(distinct(Target_URL)) from supportdellcom_backlinks_t);
set @six=(select count(*) from (select Target_URL from supportdellcom_backlinks_t
union
select Target_URL from supportdellcom_inbound_links_urls_T) x)  ; # why the x at the end of the line?
select concat("number of the same target urls majestic to google is: ", (@four + @five - @six);

No comments:

Post a Comment

Bookmark and Share