Solved

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

Posted on 2014-11-25
9
236 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

757 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

21 Experts available now in Live!

Get 1:1 Help Now