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 142

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 61

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now