Databases
StackOverflow2013
Postsdbo
POBIGINT shifting in MySQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBIGINT shifting in MySQL
    primarykey
    data
    text
    <p>Hey all, I think this might be a bug, but it's killing me. I am using MySQL 5.1.41 on an Ubuntu Linux server. I'm trying to write a function to create a random signed BIGINT value. Because the precision of RAND() is too small to generate the full range of possible BIGINT values, I decided to try to combine four 32-bit words using bit operators.</p> <p>I fired up MySQL Workbench, and tried the following to see if bit shift operators work correctly with negative numbers:</p> <pre><code>SELECT HEX((0x1ACE - 0x8000) &lt;&lt; 0x10); </code></pre> <p>0x1ACE - 0x8000 is -25906, so if I shift 16 bits to the left, I should be multiplying by 65536, right? The answer I got back was 0xFFFFFFFF9ACE0000, which is the signed representation of -1697775616, or -25906 * 65536. Wunderbar, it works!!!</p> <p>So my plan was to use that to generate the first 32-bit word of the random signed BIGINT, and use a simple loop to add three more 32-bit words to the value, shifting the bits over four bytes at a time. Excitedly, I started out by putting the following code in my function, using a hard-coded value to test my plan with:</p> <pre><code>DECLARE x BIGINT; SET x = (0x1ACE - 0x8000) &lt;&lt; 0x10; </code></pre> <p>If I set the value so that the value being shifted is positive, everything works fine. However, after performing this calculation with a shifted negative value (-25906 in this case), I kept getting that x was 0x7FFFFFFFFFFFFFFF, which is the maximum positive value of a signed 64-bit integer. I'm totally baffled. The <em>exact same operation</em> is generating a totally different result depending on whether it is in a SET operation in a function or a SELECT statement.</p> <p>So I started messing around with whether x was signed or unsigned and things got really weird. I tried making x unsigned and tried the following:</p> <pre><code>DECLARE x BIGINT UNSIGNED; SET x = (0x1ACE - 0x8000); </code></pre> <p>When I did that, I got x equal to zero. Not surprising, since x is unsigned and the result is negative. However, on a lark, I tried this:</p> <pre><code>DECLARE x BIGINT UNSIGNED; SET x = (0x1ACE - 0x8000) &lt;&lt; 0; </code></pre> <p>To my surprise, x was set to 0xFFFFFFFFFFFF9ACE!</p> <p>Can someone please help? I've been working for hours on a function that does nothing more than efficiently generates a random signed BIGINT, I'm tired, and the more I look at this stuff, the more frustrated I get and the less sense I can make of it. Any help, either in explaining what's going on here or advice in writing this function so that it consistently works now and, if this is a bug, in later version if it gets fixed, would be greatly appreciated!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USKing Skippus
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POBIGINT shifting in MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI think the problem here may be that MySql can use different data format for internal operations (substraction and shifting), try to write operations step by step: You can change SET x = (0x1ACE - 0x8000) << 0x10; to SET x=0x1ACE; SET x=x-0x8000; SET x=x<<0x10; or something like it. Or try "SELECT (0x1ACE - 0x8000) << 0x10 INTO x;" syntax http://dev.mysql.com/doc/refman/5.0/en/select-into-statement.html .
      singulars
      1. POBIGINT shifting in MySQL
      1. USXzKto
    2. CONo dice. When I split it up into line-by-line operations, everything is correct until I SET x = x << 0x10; It still gives me 0x7FFFFFFFFFFFFFFF. If I SELECT x << 0x10 INTO x; it gives me the same result. This is so frustrating!
      singulars
      1. POBIGINT shifting in MySQL
      1. USKing Skippus
    3. COOkay, now I'm just getting pissed off. I just tried doing a SET guid = 0x1ABC - 0x8000 and got 0xFFFFFFFFFFFF9ABC. So far, so good. Then if I do a SET guid = guid << 0, I get 0x7FFFFFFFFFFFFFFF. This is clearly wrong, and there's something not kosher going on behind the scenes.
      singulars
      1. POBIGINT shifting in MySQL
      1. USKing Skippus
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload