MySQL mystery error condition

Posted on 2016-08-15
Last Modified: 2016-08-18
I'm working with rate plans for modems, trying to find the best rate plan given the table's values and actual usage.

I may not be going about this the most efficient way, but with only 5K records I'm not currently worried about performance.
That doesn't mean I'm adverse to a wiser way though!

intlplan_rate_charge and intlplan_overage_rate_per_MB are DECIMAL(10,4) ;
intlplan_limit_MB is SMALLINT UNSIGNED ;

Stepping up to calculate the best rate AND its associated code and wanting to set a field using SIGN() function to indicate:
-1 for plan too high, need to downgrade
0  for plan just right, and
+1 for plan too low, need to upgrade

Here's what I have:
  SELECT invoice_id INTO @var_latest_inv
  FROM   Invoice
  WHERE  date_charges_import_completed = (SELECT MAX(date_charges_import_completed) FROM Invoice );
  # --------------------------------------
  CREATE  TABLE `Modem_Usages` AS
  SELECT modem_config_id
       , intl_rate_plan_id
       , intl_data_usage
  FROM   ModemConfig mc
  JOIN   InvoicedModem im
  USING  (wireless_no)
  WHERE  im.invoice_id = @var_latest_inv ;
  # --------------------------------------
  CREATE  TABLE `Calcd_Charge` AS
  SELECT mu.modem_config_id
       , mu.intl_rate_plan_id
       , mu.intl_data_usage
       , irp.intlplan_code
       , irp.intlplan_limit_MB AS `current_threshold`
       , CASE WHEN mu.intl_data_usage/1000 <= intlplan_limit_MB THEN intlplan_rate_charge 
                                                        ELSE intlplan_rate_charge + (intl_data_usage/1000 - intlplan_limit_MB) * intlplan_overage_rate_per_MB END AS `calcd_charge`
  FROM IntlRatePlan   irp
  JOIN `Modem_Usages` mu
  USING (intl_rate_plan_id)  ;
  # --------------------------------------
  #REATE  TABLE `Enable_Best_Calc` AS
  SELECT  mu.modem_config_id
       ,  mu.intl_rate_plan_id
       , irp.intlplan_code
       ,   i.intlplan_limit_MB  AS 'OtherMBs'
       ,   i.intlplan_code      AS 'AvailPlans'
       ,  CASE WHEN mu.intl_data_usage/1000 <= i.intlplan_limit_MB THEN i.intlplan_rate_charge 
                                                        ELSE i.intlplan_rate_charge + (mu.intl_data_usage/1000 - i.intlplan_limit_MB) * i.intlplan_overage_rate_per_MB END AS `calcd_charge`
  FROM   `Modem_Usages`  mu
  JOIN   `IntlRatePlan` irp
  USING  (intl_rate_plan_id)
  JOIN   `IntlRatePlan` i
  WHERE  (i.intlplan_limit_MB <> irp.intlplan_limit_MB AND SUBSTRING(i.intlplan_code,1,4) = SUBSTRING(irp.intlplan_code,1,4)
    OR    i.intlplan_limit_MB =  irp.intlplan_limit_MB AND i.intlplan_code = irp.intlplan_code)
   AND   i.date_no_more_irp_contracts > CURRENT_DATE() 
   AND   i.intlplan_code NOT IN ('Unassigned', 'Suspended', 'NoRatePlan')
   ORDER BY 1, 4;
  # --------------------------------------
  SELECT modem_config_id,  MIN(ROUND(calcd_charge,4)) AS `LowestCharge` FROM `Enable_Best_Calc` GROUP BY 1  limit 6;

Open in new window

I intentionally did a cross join to get all available plans and best charge for each.
Output of Best_Plan_Calc table
Now to get the associated Codes of current and best, and to generate that tristate value, I join this on itself 3 times.
Piecemeal shown first, then what SHOULD WORK:
  SELECT modem_config_id,  MIN(ROUND(calcd_charge,4)) AS `LowestCharge` FROM `Enable_Best_Calc` GROUP BY 1  limit 6;
  SELECT  a.*
        , ' ' AS 'Separator'
        , b.LowestCharge
  FROM    `Enable_Best_Calc` a
  JOIN    (SELECT modem_config_id,  MIN(ROUND(calcd_charge,4)) AS `LowestCharge` FROM `Enable_Best_Calc` GROUP BY 1) b
    ON    a.modem_config_id = b.modem_config_id 
  WHERE   a.intlplan_code = a.AvailPlans limit 9;
  SELECT  a.*
        , ' ' AS 'Separator'
        , b.LowestCharge
        , ' ' AS 'Separator'
        , c.AvailPlans        AS preferred_plan_code
        , c.OtherMBs          AS preferred_limit
        , SIGN(c.OtherMBs - a.OtherMBs) as preferred_plan_tristate
  FROM    `Enable_Best_Calc` a
  JOIN    (SELECT modem_config_id,  MIN(ROUND(calcd_charge,4)) AS `LowestCharge` FROM `Enable_Best_Calc` GROUP BY 1) b
    ON    a.modem_config_id = b.modem_config_id 
  JOIN    `Enable_Best_Calc` c
    ON    a.modem_config_id = c.modem_config_id  AND ROUND(c.calcd_charge,4) = ROUND(b.LowestCharge,4)
  WHERE   a.intlplan_code = a.AvailPlans ;

Open in new window

Output of the middle of the three looks like:Shows lowest chargeThe larger / 3rd query merges tables a with b to show all of a.* with b's lowest charge,
then table c provides me the code and limit associated with the lowest charge.

This works fine AS LONG AS I LEAVE OUT THE SIGN((c.OtherMBs - a.OtherMBs).

I've tried ROUND(,0) on each of these arguments, but I still get:
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(ROUND(`c`.`OtherMBs`,0) - ROUND(`a`.`OtherMBs`,0))'

I don't get it because 'Other_MBs' is from intl_limit_MB, which is a SMALLINT UNSIGNED.
I'm not assigning the value, just subtracting them.

Must I CAST them to SIGNED?  I'm guessing so...

Well, that worked.  CAST(zyx AS SIGNED) for each argument worked.
Now to clean up to just what I need.

Still, if you can suggest a significantly smarter way to do this I'm all ears!  Love to learn it.
Question by:Ralph
  • 2
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 41757709
somewhere in the calculated you get a value that is "too big" of a integer
as you are doing maths with decimal and integer, at some point, it tries to convert the decimal to integer, and not the other way round.

so, as the only field you are using there, that is integer, is this one:
intlplan_limit_MB is SMALLINT UNSIGNED ;

either cast that one into a decimal before using it in the expression, or change the field type itself

also note that  / 1000 should be / 1000.0 to tell mysql to use it as a decimal directly

hence, try this:
i.intlplan_rate_charge + (mu.intl_data_usage/1000.0 - cast(i.intlplan_limit_MB as decimal(10,4)) ) * i.intlplan_overage_rate_per_MB

Author Closing Comment

ID: 41758417
Got it working when I changed it from UNSIGNED to SIGNED.
Seems even though I was just subtracting an UNSIGNED from another, that causes a hiccup when the result is negative.

I wouldn't have expected that.

Thanks Guy!

Author Comment

ID: 41760402
For above, the hiccup happens when the subtraction is inside a SIGN() function.
LVL 62

Expert Comment

ID: 41761453
Normally you select(min(formula)) instead of playin up down game before you know optimal option.
(or use statistics package like R or octave, 5000 rows is not much)

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Query - Return results based on minimum value 8 35
SubQuery link 4 35
TSQL Challenge... 7 35
Query for timesheet application 3 17
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question