MySQL error: # 1690 - BIGINT UNSIGNED value is out of range in

Question

MySQL error: # 1690 - BIGINT UNSIGNED value is out of range in

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

After making the following query:

SELECT CP.code
FROM cart_products AS CP
    INNER JOIN products AS P
        ON P.code = CP.code
WHERE CP.cartID = 22207
    AND (P.stock - CP.quantity) > 0

I got the following error:

# 1690 - BIGINT UNSIGNED value is out of range in ('DB_NAME'. 'p'. 'stock' - 'DB_NAME'. 'cp'. 'quantity') '

The structure of the tables is as follows:

CREATE TABLE 'products' (
  'productID' int(10) UNSIGNED NOT NULL,
  'code' tinytext COLLATE latin1_general_ci NOT NULL,
  'stock' mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE 'cart_products' (
  'cartID' int(10) UNSIGNED NOT NULL,
  'code' tinytext COLLATE latin1_general_ci NOT NULL,
  'quantity' mediumint(8) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

How can I do the query without getting this error?

mysql sql

asked by Marcos 29.12.2017 в 21:33

source

2 answers

1

The error is caused by an " unsigned integer overflow "

The solution I found was to convert ( CAST ) unsigned integers ( eg: UNSIGNED ) a signed ( eg: SIGNED )

Solution:

SELECT CP.code
FROM cart_products AS CP
    INNER JOIN products AS P
        ON P.code = CP.code
WHERE CP.cartID = 22207
    AND (CAST(P.stock AS SIGNED) - CAST(CP.quantity AS SIGNED)) > 0

answered by 29.12.2017 в 21:33

How to remove spaces in bootstrap? Problems with max use after count

score 2 · Accepted Answer

The error occurs because the calculation between 2 unsigned integers is also expected to produce an unsigned integer. But if the calculation can result in a negative number (which is your case), obviously, this is not valid.

One solution is the CAST that you mention in your answer.

But it would be simpler to use the following equivalent condition to avoid the calculation and the risk of a negative number:

AND P.stock > CP.quantity