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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
combine an MS SQL string in Idera DM 9 48
Convert char to decimal in a SQL Server View 14 41
setup wamp server for first time 2 41
Make query more efficient 1 16
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

932 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