Troubleshooting Truths in MySQL and WFA

I was getting some slightly weird results from a WFA Filter (SQL Query) and after a good hour or so’s troubleshooting, realized it was because I had not got correctly cast variables in a greater than expression.

One thing to remember about SQL queries is that you do not just have to return stuff from tables, you can return your own troubleshooting stuff too. Look at my example image below. 4 of the columns - ‘OMB’, ‘RMB’, ‘truth1’, and ‘truth2’ - are there purely for troubleshooting purposes.

Image: Troubleshooting an SQL Query

The actual SQL query I was working on is below. I have shaded the section I used for troubleshooting. Highlighted at the bottom is the bit that was not working. A stupid mistake really, SQL did not know I wanted to find if one integer is greater than another integer. To make SQL know I am talking about unsigned (i.e. positive) integers, it needs the CAST(‘YOUR_VARIABLE’ AS UNSIGNED).

SELECT

 volume.name AS 'name',

 vserver.name AS 'vserver.name',

 cluster.primary_address AS 'vserver.cluster.primary_address',

 volume.available_size_mb - SUM( COALESCE( qtree.disk_limit_mb,0 ) ) AS 'quota_available_mb' ,

 volume.size_mb AS 'vol_size_mb' ,

 ('quota_available_mb' - '${requested_mb}' >= 'vol_size_mb' * (1-  '${vol_full_threshold}')) AS truth1,

 (CAST('${original_mb}'  AS UNSIGNED) >= CAST('${requested_mb}' AS UNSIGNED)) AS truth2,

 '${original_mb}' AS OMB,

 '${requested_mb}' AS RMB

FROM

    cm_storage.cluster    

JOIN

    cm_storage.vserver AS vserver                                            

        ON cluster.id = vserver.cluster_id    

JOIN

    cm_storage.volume                                             

        ON vserver.id = volume.vserver_id     

JOIN

    cm_storage.qtree                                            

        ON qtree.volume_id = volume.id    

WHERE

    vserver.name = '${vserver}'                        

    AND (

        cluster.primary_address = '${cluster_address}'                                            

        OR cluster.name = '${cluster_address}'                        

    )           

    AND volume.name  = '${volume}'    

    AND qtree.name != '${qtree}' 

GROUP BY

    volume.id    

HAVING

    IF(quota_available_mb - '${requested_mb}' > vol_size_mb * (1-  '${vol_full_threshold}'),TRUE,FALSE) OR IF('${original_mb}' > '${requested_mb}' ,TRUE,FALSE)