MySQL mystery error condition

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RalphAuthor Commented:
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!
RalphAuthor Commented:
For above, the hiccup happens when the subtraction is inside a SIGN() function.
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)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.