Home » SQL & PL/SQL » SQL & PL/SQL » Binary Integer Division (Oracle 11g R2)
Binary Integer Division [message #675039] Wed, 06 March 2019 07:49 Go to next message
jagman
Messages: 8
Registered: March 2019
Junior Member
I'm trying to perform a right-bit-shift on a binary integer in PL/SQL.
This involves simply dividing the integer by a power of 2.
The problem is, the integer arithmetic is not performed as expected.

In C, the following snippet prints 55, which is what I would expect.
Integers don't have a decimal portion, so the result is effectively truncated, i.e 55.5 becomes 55.

#include <stdio.h>
void main ( void ) {
    unsigned int x = 111;
    unsigned int y = x / 2;
    printf("%i\n", y);
}
55


But a similar snippet in PL/SQL prints 56!

DECLARE
    x binary_integer := 111;
    y binary_integer;
BEGIN
    y := x / 2;
    dbms_output.put_line( y );
END;
/
56


An implicit conversion to a real must be happening somewhere.

How can I get around this?

One solution would be to subtract 1 from the value before performing the division (providing its not 0), but this is ugly.

Does anyone know of a better solution?


Re: Binary Integer Division [message #675040 is a reply to message #675039] Wed, 06 March 2019 07:58 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
No idea why you expected it to be truncated. I would expect it to be rounded, which is what happened.
If you want the result truncated then apply the trunc or floor functions to it.
SQL> DECLARE
  2      x binary_integer := 111;
  3      y binary_integer;
  4  BEGIN
  5      y := trunc(x / 2);
  6      dbms_output.put_line( y );
  7      y := FLOOR(x / 2);
  8      dbms_output.put_line( y );
  9  END;
 10  /
55
55
Re: Binary Integer Division [message #675041 is a reply to message #675040] Wed, 06 March 2019 08:30 Go to previous messageGo to next message
jagman
Messages: 8
Registered: March 2019
Junior Member
"No idea why you expected it to be truncated" - Did you not see the C snippet above?



Re: Binary Integer Division [message #675042 is a reply to message #675041] Wed, 06 March 2019 08:41 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I did - but that's C.

In oracle you do the math and then work out what you can fit in the variable.
Which is to say it doesn't make any difference what type x is unless you are trying to put decimal data in there as well.
I guess C just works differently.

Anyway - floor or trunc is what you need.
Re: Binary Integer Division [message #675046 is a reply to message #675042] Wed, 06 March 2019 10:01 Go to previous messageGo to next message
jagman
Messages: 8
Registered: March 2019
Junior Member
Thanks for the reply.
I did use your solution in the end, but being a C programmer, this behaviour seems completely and utterly wrong!

I'm implementing a simple linear-feedback shift-register that generates seemingly random sequences.
This is trivial in C because performing a bitwise shift is as simple as dividing or multiplying by a power of 2.

In C there is no concept of rounding a binary integer. They are whole numbers.
Any remainder in an integer division is lost. There is nothing left to round.
This is binary integer arithmetic. Its fundamental computer science!

It just seems strange that PL/SQL does not perform binary integer arithmetic on two binary integers...

Re: Binary Integer Division [message #675047 is a reply to message #675046] Wed, 06 March 2019 10:04 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Less strange if you realise that binary_interger is basically a mask over number.
Re: Binary Integer Division [message #675050 is a reply to message #675047] Wed, 06 March 2019 10:48 Go to previous messageGo to next message
jagman
Messages: 8
Registered: March 2019
Junior Member
I was fooled by reading this excerpt from
https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/datatypes.htm#LNPLS319

Its clearly put there to deceive C programmers like me... Razz

Quote:

PLS_INTEGER and BINARY_INTEGER Data Types
The PL/SQL data types PLS_INTEGER and BINARY_INTEGER are identical. For simplicity, this document uses PLS_INTEGER to mean both PLS_INTEGER and BINARY_INTEGER.

The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits.

The PLS_INTEGER data type has these advantages over the NUMBER data type and NUMBER subtypes:

PLS_INTEGER values require less storage.

PLS_INTEGER operations use hardware arithmetic, so they are faster than NUMBER operations, which use library arithmetic.

For efficiency, use PLS_INTEGER values for all calculations in its range.
Re: Binary Integer Division [message #675052 is a reply to message #675046] Wed, 06 March 2019 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Any remainder in an integer division is lost. There is nothing left to round.
This is binary integer arithmetic. Its fundamental computer science!
This is not correct, DIV hardware instruction returns the quotient AND the remainder. The language you use upon them chooses what it wants to do with the later.
C chooses to ignore it.
PL/SQL chooses to it into account to give the result.
It's a language choice, it's not fundamental computer science.

Re: Binary Integer Division [message #675055 is a reply to message #675052] Wed, 06 March 2019 15:44 Go to previous messageGo to next message
Bill B
Messages: 1968
Registered: December 2004
Senior Member
Depending on your version oracle has a number of bit manipulation routine. see

https://docs.oracle.com/cd/E87306_01/DR/Bit_Binary_Functions.html
Re: Binary Integer Division [message #675056 is a reply to message #675052] Wed, 06 March 2019 16:17 Go to previous messageGo to next message
jagman
Messages: 8
Registered: March 2019
Junior Member
I stand corrected as to the remainder not being lost, nevertheless, integer arithmetic states that an arithmetic shift right is equivalent to a division by a power of 2.
And this is not the case with PL/SQL!

Why would the language designers possibly think that doing this differently would be an advantage?
Re: Binary Integer Division [message #675057 is a reply to message #675055] Wed, 06 March 2019 16:19 Go to previous messageGo to next message
jagman
Messages: 8
Registered: March 2019
Junior Member
Thanks for the pointer to the bit/binary functions.
I did come across this list before.
Unfortunately, the version of Oracle I'm using only has BITAND!
I had to write my own BITOR and BITXOR functions.
Re: Binary Integer Division [message #675058 is a reply to message #675057] Wed, 06 March 2019 23:18 Go to previous messageGo to next message
jagman
Messages: 8
Registered: March 2019
Junior Member
This issue was still bothering me when I went to bed last night, so I got up and tried the same integer arithmetic on a few of the systems and languages I have at my disposal.

I fired up a Linux shell and tried
echo $(( 111 / 2 ))
55

I tried it in Perl
perl -Minteger -e 'print 111/2, "\n"'
55

I logged into an OpenVMS system and tried the DCL command
$ write sys$output 111/2
55

I opened the Windows 10 calculator in "Programmer mode", keyed in 111/2 and got 55.

The calculator app on my phone also returned 55.


So, it is my humble opinion that PL/SQL, returning 56 for 111/2, is not performing integer arithmetic as would be expected by anyone other than a PL/SQL developer.

It is wrong!

Looking forward to someone convincing me otherwise...


Re: Binary Integer Division [message #675060 is a reply to message #675058] Thu, 07 March 2019 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
that PL/SQL [...] is not performing integer
This is correct they chose to perform NUMBER arithmetic to have no difference between PLS_INTEGER/BINARY_INTEGER and INTEGER types.
What would you then say if a language has 2 (or 3) integer types (for history reason) with different results? Smile

I once wrote, in the previous millennium, a package for bit manipulation on Oracle INTEGER type (not hardware one so slower but can use bits strings/integers larger than 32/64 bits):
SQL> exec dbms_output.put_line(bit_functions.bit_rsh(111));
55

PL/SQL procedure successfully completed.
Here it is:
create or replace package bit_functions as 
   function int_to_bits (in_val in integer)                return varchar2; 
   function bits_to_int (in_val in varchar2)               return integer; 
   function bit_and     (in_1 in integer, in_2 in integer) return integer; 
   function bit_or      (in_1 in integer, in_2 in integer) return integer; 
   function bit_xor     (in_1 in integer, in_2 in integer) return integer; 
   function bit_lsh     (in_num in integer,
                         in_shft in integer default 1)     return integer; 
   function bit_rsh     (in_num in integer,
                         in_shft in integer default 1)     return integer; 
   end; 
/
show errors; 

create or replace package body bit_functions as 
   function int_to_bits (in_val in integer) return varchar2
   is 
      work_1 number; 
      work_2 number; 
      work_3 number; 
      digit  varchar2(1); 
      accum  varchar2(64); 
   begin 
      if in_val < 0 or trunc(in_val) != in_val then
        raise_application_error(-20000, 'Invalid input value');
      end if;
      accum  := ''; 
      work_1 := in_val; 
      work_2 := in_val; 
      loop 
         work_2 := work_1/2; 
         work_3 := trunc(work_2); 
         if work_2 = work_3 then digit := '0'; 
         else digit := '1'; end if; 
         accum := digit || accum; 
         if work_3 = 0 then exit; end if; 
         work_1 := work_3; 
      end loop; 
      return accum; 
   end; 
   function bits_to_int (in_val in varchar2) return integer
   is 
      i     number; 
      digit number; 
      accum number; 
   begin 
      accum := 0; 
      for i in 1..length(in_val) loop 
         digit := to_number(substr(in_val,i,1)); 
         accum := 2*accum + digit; 
      end loop; 
      return accum; 
   end; 
   function bit_and (in_1 in integer, in_2 in integer) return integer
   is 
      work_1 varchar2(64); 
      work_2 varchar2(64); 
      digit  number; 
      i      number; 
      accum  number; 
   begin 
      if in_1 < 0 or trunc(in_1) != in_1 or in_2 < 0 or trunc(in_2) != in_2 then
        raise_application_error(-20000, 'Invalid input value');
      end if;
      work_1 := int_to_bits (in_1); 
      work_2 := int_to_bits (in_2); 
      accum  := 0; 
      if length(work_1) > length(work_2) then 
         work_2 := lpad(work_2,length(work_1),'0'); 
      end if; 
      if length(work_2) > length(work_1) then 
         work_1 := lpad(work_1,length(work_2),'0'); 
      end if; 
      for i in 1..length(work_1) loop 
         if substr(work_1,i,1) = '1' and substr(work_2,i,1) = '1' then 
            digit := 1; 
         else 
            digit := 0; 
         end if; 
         accum := 2*accum + digit; 
      end loop; 
      return accum; 
   end; 
   function bit_or (in_1 in integer, in_2 in integer) return integer 
   is 
      work_1 varchar2(64); 
      work_2 varchar2(64); 
      digit  number; 
      i      number; 
      accum  number; 
   begin 
      if in_1 < 0 or trunc(in_1) != in_1 or in_2 < 0 or trunc(in_2) != in_2 then
        raise_application_error(-20000, 'Invalid input value');
      end if;
      work_1 := int_to_bits (in_1); 
      work_2 := int_to_bits (in_2); 
      accum  := 0; 
      if length(work_1) > length(work_2) then 
         work_2 := lpad(work_2,length(work_1),'0'); 
      end if; 
      if length(work_2) > length(work_1) then 
         work_1 := lpad(work_1,length(work_2),'0'); 
      end if; 
      for i in 1..length(work_1) loop 
         if substr(work_1,i,1) = '1' or substr(work_2,i,1) = '1' then 
            digit := 1; 
         else 
            digit := 0; 
         end if; 
         accum := 2*accum + digit; 
      end loop; 
      return accum; 
   end; 
   function bit_xor (in_1 in integer, in_2 in integer) return integer 
   is 
      work_1 varchar2(64); 
      work_2 varchar2(64); 
      digit  number; 
      i      number; 
      accum  number; 
   begin 
      if in_1 < 0 or trunc(in_1) != in_1 or in_2 < 0 or trunc(in_2) != in_2 then
        raise_application_error(-20000, 'Invalid input value');
      end if;
      work_1 := int_to_bits(in_1); 
      work_2 := int_to_bits(in_2); 
      accum  := 0; 
      if length(work_1) > length(work_2) then 
         work_2 := lpad(work_2,length(work_1),'0'); 
      end if; 
      if length(work_2) > length(work_1) then 
         work_1 := lpad(work_1,length(work_2),'0'); 
      end if; 
      for i in 1..length(work_1) loop 
         if substr(work_1,i,1) = substr(work_2,i,1) then 
            digit := 0; 
         else 
            digit := 1; 
         end if; 
         accum := 2*accum + digit; 
      end loop; 
      return accum; 
   end; 
   function bit_lsh (in_num in integer, in_shft in integer default 1)
   return integer 
   is 
   begin 
      if in_num < 0 or trunc(in_num) != in_num or in_shft < 0 or trunc(in_shft) != in_shft then
        raise_application_error(-20000, 'Invalid input value');
      end if;
      return power(2,in_shft)*in_num; 
   end; 
   function bit_rsh (in_num in integer, in_shft in integer default 1)
   return integer 
   is 
   begin 
      if in_num < 0 or trunc(in_num) != in_num or in_shft < 0 or trunc(in_shft) != in_shft then
        raise_application_error(-20000, 'Invalid input value');
      end if;
      return trunc(in_num/power(2,in_shft)); 
   end; 
end; 
/
show errors 

[Updated on: Thu, 07 March 2019 00:59]

Report message to a moderator

Re: Binary Integer Division [message #675062 is a reply to message #675060] Thu, 07 March 2019 03:58 Go to previous messageGo to next message
jagman
Messages: 8
Registered: March 2019
Junior Member
Quote:
What would you then say if a language has 2 (or 3) integer types (for history reason) with different results?

Hmmmm.... I'm not sure what's worse to be honest. Having 3 integer types giving different results, or having them all give the wrong result.
Razz


Thanks for the code by the way.
I'll take a copy and refer it back to this page.

Re: Binary Integer Division [message #675066 is a reply to message #675062] Thu, 07 March 2019 08:13 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2977
Registered: January 2010
Location: Connecticut, USA
Senior Member
It is funny to watch how people react when they end up in a situation where something doesn't work they are used to. Many label it "wrong" right away. Others label it "different". Do you say "it is wrong" when you come to UK and cars drive on the left side of the road? Same way does person from UK say "it is wrong" about cars driving on the right side of the road? Cars have to drive on some side, right? Same way is assigning fraction to an integer. No matter what we have to do something with fractional part. Some tools say lose it completely some say round it up so we lose less. Personally, I am for losing up to .499...9 versus losing .999...9. Now I support both Oracle & SQL Server. Oracle rounds SQL Server truncates. I don't label either of them right or wrong. I simply accept the rules.

SY.
Re: Binary Integer Division [message #675071 is a reply to message #675058] Thu, 07 March 2019 15:30 Go to previous message
Bill B
Messages: 1968
Registered: December 2004
Senior Member
i disagree with the other systems and agree with oracle.

111/2 = 55.5

Following the industry standard .5 rounding rule it goes to 56.

however if you want to duplicate your results then simply throw away the remainder.

trunc(111/2)
floor(111/2)

In effect the other systems are simply truncating the remainders anyway. In Oracle you have a choice wither or not you want to use the remainder.




Previous Topic: checkbox charachter
Next Topic: nvl and is null
Goto Forum:
  


Current Time: Sat Aug 08 10:35:59 CDT 2020