Solved

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`T`.`rn` - `T`.`LicenseNumber`)' in MYsql

Posted on 2014-11-25
9
239 Views
Last Modified: 2014-12-05
I have the following table:
+---------------+-------------+---------------------+------------+----------------+-------------+--------------------+
| LicenseNumber | LicenseType | LicenseNumberStatus | EntryDate  | PADL_Agents_ID | AgentStatus | GNC_CompanyData_ID |
+---------------+-------------+---------------------+------------+----------------+-------------+--------------------+
|          1487 | YearlyR     | 1                   | 2014-11-03 |             48 | 1           |                 38 |
|          1488 | YearlyR     | 1                   | 2014-11-08 |             48 | 1           |                 38 |
|          1489 | YearlyR     | 1                   | 2014-11-09 |             48 | 1           |                 38 |
|          1490 | YearlyR     | 1                   | 2014-11-15 |             48 | 1           |                 38 |
|          1491 | YearlyR     | 1                   | 2014-11-17 |             48 | 1           |                 38 |
|          1492 | YearlyR     | 1                   | 2014-11-25 |             48 | 1           |                 38 |
|          2989 | YearlyR     | 1                   | 2014-11-05 |             49 | 1           |                 38 |
|          2990 | YearlyR     | 1                   | 2014-11-12 |             49 | 1           |                 38 |
|          2991 | YearlyR     | 1                   | 2014-11-17 |             49 | 1           |                 38 |
+---------------+-------------+---------------------+------------+----------------+-------------+--------------------+
when I ran the following query I receive the following error:

SELECT MIN(LicenseNumber) as BeginLicenseNumber, MAX(LicenseNumber) as EndLicenseNumber FROM( SELECT LicenseNumber, LicenseType, (SELECT COUNT(*) FROM PADL_LicenseNumber2 t2 WHERE t2.LicenseNumber <= t1.LicenseNumber AND t2.PADL_Agents_ID = t1.PADL_Agents_ID and t2.GNC_CompanyData_ID = t1.GNC_CompanyData_ID  and t2.LicenseType=t1.LicenseType and t2.LicenseNumberStatus=t1.LicenseNumberStatus) AS rn FROM PADL_LicenseNumber2 t1 WHERE (t1.GNC_CompanyData_ID = '38' and t1.LicenseNumberStatus=1 and t1.EntryDate between '20141101' and '20141130' and LicenseType='yearlyR') OR  (t1.GNC_CompanyData_ID = '38' and t1.LicenseNumberStatus=8 and t1.EntryDate between '20141101' and '20141130' and LicenseType='yearlyR') OR (t1.GNC_CompanyData_ID = '38' and t1.LicenseNumberStatus=9 and t1.EntryDate between '20141101' and '20141130' and LicenseType='yearlyR')) T GROUP BY rn - LicenseNumber ORDER BY 1

ERROR

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`T`.`rn` - `T`.`LicenseNumber`)'.

this server is mysql 5.5.40 and only have 9 records

It worked fine on MYSQL 4.01 with thousands of records.

Any help would be appreciated.

ePay
0
Comment
Question by:epay
  • 3
  • 3
  • 2
9 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 40466897
<wild guess>

>GROUP BY rn - LicenseNumber
unsigned = positive numbers only, so if the above equates to a negative number it may throw the above error.  

Also, as a public service announcement, next time you post T-SQL please use some decent indenting so it doesn't look like a big pile of goo that is difficult to read.   Hope you understand.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 40467098
More to the point, the value for rn will almost always be less than LicenseNumber.  The query has a few oddities in it...

The WHERE clause is the first subquery can be reduced.  All of the terms are the same, except the value of LicenseNumberStatus.  Reduce the clause to read: t1.GNC_CompanyData_ID = '38' and t1.EntryDate between '20141101' and '20141130' and LicenseType='yearlyR' and t1.LicenseNumberStatus IN (1,8,9)

The rn calculated field is a count of how many licenses exist with license numbers less than the current t1 row.  Given that your LicenseNumber field is always >0 (an assumption), then (rn < LicenseNumber) should always be true.  It also means that the calculation (rn - LicenseNumber) will be less than zero, and, if LicenseNumber is unsigned, will generate the error you are reporting 100% of the time.

Your final field selection does not make sense in the context of the GROUP BY or the internal logic of the query.  Of course, I'm unfamiliar with your database and its purpose, so I may be off-base here.  Still, why select LicenseType in the first subquery?  It is never used.  Why GROUP by the number of licenses per agentid/companydata/licensetype/numberstatus?  Why do you need MIN/MAX of groups with members which will very likely be non-contiguous?
0
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 250 total points
ID: 40467104
Also, for any other experts joining the party, the original query with some formatting:
SELECT 
  MIN(LicenseNumber) as BeginLicenseNumber, 
  MAX(LicenseNumber) as EndLicenseNumber 
FROM
  ( SELECT 
      LicenseNumber, 
      LicenseType, 
      (SELECT COUNT(*) FROM PADL_LicenseNumber2 t2 
        WHERE t2.LicenseNumber <= t1.LicenseNumber 
              AND t2.PADL_Agents_ID = t1.PADL_Agents_ID 
              and t2.GNC_CompanyData_ID = t1.GNC_CompanyData_ID  
              and t2.LicenseType=t1.LicenseType 
              and t2.LicenseNumberStatus=t1.LicenseNumberStatus) AS rn 
    FROM PADL_LicenseNumber2 t1 
    WHERE 
      (t1.GNC_CompanyData_ID = '38' 
        and t1.LicenseNumberStatus=1 
        and t1.EntryDate between '20141101' and '20141130' 
        and LicenseType='yearlyR') 
      OR  (t1.GNC_CompanyData_ID = '38' 
        and t1.LicenseNumberStatus=8 
        and t1.EntryDate between '20141101' and '20141130' 
        and LicenseType='yearlyR') 
      OR (t1.GNC_CompanyData_ID = '38' 
        and t1.LicenseNumberStatus=9 
        and t1.EntryDate between '20141101' and '20141130' 
        and LicenseType='yearlyR')
  ) T 
GROUP BY rn - LicenseNumber ORDER BY 1

Open in new window

0
 

Accepted Solution

by:
epay earned 0 total points
ID: 40467583
Hi Steve,

First, I would like to thank you for posting a clearer version of the query without being....

After many hours, I figured out the issue. I had to change BIGINT() UNSIGNED to INT() and it worked.

Somehow the BIGINT() UNSIGNED was working fine in Mysql 4.1 but it doesn't in 5.5 version.

I changed and it worked fined but I will still give you the total points for being a gentleman about the messy code.

Thx
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:epay
ID: 40467605
Sorry about the misspelled word only few hours of sleep!!!

I changed the code and it worked fine but I will still give you the total points for being a gentleman about the messy code.

Thx
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 40467728
I think you may have taken some offense where none was intended.  Jim Horn is one of the most gentlemanly experts around.  His advice regarding the use of a code section was on the mark, and is the reason I reposted the query with formatting.  I took his choice of phrasing as an attempt at humor.

Even more, his <wild guess> regarding the UNSIGNED/SIGNED issue was actually the source of the error - his remark should have garnered points for having posted it first.  I merely followed his lead while adding my two cents of some other aspects of the query.

If you change your mind, you can use the "Request Attention" link to have the question re-opened so you can distribute the points more appropriately.

Good luck!
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40467854
Steve can keep the points.   My experience is SQL Server and not MySQL, and the only reason I posted here is because I had an answer that might translate to MySQL, and a neglected question is usually fair game for any attempt at an answer.   Beats having a question ignored.

Just to set expectations, I wasn't directly trying to slam the asker, but if this can be taken in a constructive way it's in the best interest of askers to make the most efficient use of experts' time, and making code readable falls into that category.  

If you have time for a few laughs check out my article on Requirements Document Template for a Reporting Project, which is the same line of thinking but for various requirements docs I've received over the years.

The data mockup was helpful.

Glad you found a solution.
Jimbo
0
 

Author Closing Comment

by:epay
ID: 40482492
Steve, Jim,

I thank both of you for taking the time and responding to my question. My reply was also on the humor side with no offense intended just to light in UP the issue of trying to welcome NEW COMERS.

Again thx for everything.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 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

22 Experts available now in Live!

Get 1:1 Help Now