Home » SQL & PL/SQL » SQL & PL/SQL » Binary Integer Division (Oracle 11g R2)
Binary Integer Division Wed, 06 March 2019 07:49
 jagman Messages: 8Registered: 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
 cookiemonster Messages: 13894Registered: 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
 jagman Messages: 8Registered: 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
 cookiemonster Messages: 13894Registered: 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
 jagman Messages: 8Registered: March 2019 Junior Member
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
 cookiemonster Messages: 13894Registered: 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
 jagman Messages: 8Registered: 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...

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
 Michel Cadot Messages: 67290Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Bill B Messages: 1968Registered: 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
 jagman Messages: 8Registered: 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
 jagman Messages: 8Registered: 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
 jagman Messages: 8Registered: 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
 Michel Cadot Messages: 67290Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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?

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
end if;
if length(work_2) > length(work_1) then
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
end if;
if length(work_2) > length(work_1) then
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
end if;
if length(work_2) > length(work_1) then
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
 jagman Messages: 8Registered: 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.

Thanks for the code by the way.

Re: Binary Integer Division [message #675066 is a reply to message #675062] Thu, 07 March 2019 08:13
 Solomon Yakobson Messages: 2977Registered: 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
 Bill B Messages: 1968Registered: 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 09:35:30 CDT 2020