CODEWITHSUNDEEP
oracle-databaseselectplsqltriggerscount
Ranit Dholey
Ranit Dholey

Reputation: 37

count(*) yielding different results for normal select statement and for triggers

I'm having a weird problem in SQLDeveloper.

I have a normal select statement as in: SELECT COUNT(*) FROM demo WHERE NAME='ABC'; The above query gives a result 1. Running the same select query as in : SELECT COUNT(*) FROM demo WHERE NAME='ABC '; gives a result 1 as well. Note that the extra appended spaces in the second query doesn't change the value of the count.

Now, having the count(*) statement inside a trigger yields different count results and it takes the spaces into consideration while evaluating the count. Why exactly is this happenening? I'm using SQLDeveloper Version 4.1.5.21.

Upvotes: 0

Views: 168

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You can see this if your column is char rather than varchar2:

create table demo (name char(9));

insert into demo values ('ABC');

1 row inserted.

SELECT COUNT(*) FROM demo WHERE NAME='ABC';

  COUNT(*)
----------
         1

SELECT COUNT(*) FROM demo WHERE NAME='ABC      ';

  COUNT(*)
----------
         1

If you do exactly the same queries in a trigger - or any PL/SQL block - using the same text literals you get the same result:

set serveroutput on
declare
  n number;
begin
  SELECT COUNT(*) into n FROM demo WHERE NAME='ABC'; 
  dbms_output.put_line(n);
  SELECT COUNT(*) into n FROM demo WHERE NAME='ABC      ';
  dbms_output.put_line(n);
end;
/

1
1

PL/SQL procedure successfully completed.

But if you use a varchar2 variable to supply the value instead of a text literal you get different counts:

declare
  n number;
  v varchar2(9);
begin
  v := 'ABC';
  SELECT COUNT(*) into n FROM demo WHERE NAME=v; 
  dbms_output.put_line(n);
  v := 'ABC      ';
  SELECT COUNT(*) into n FROM demo WHERE NAME=v;
  dbms_output.put_line(n);
end;
/

0
1

PL/SQL procedure successfully completed.

When you use a text literal it is treated as a char:

  • Within expressions and conditions, Oracle treats text literals as though they have the data type CHAR by comparing them using blank-padded comparison semantics.

The apparent behaviour difference then comes down to blank-padded and nonpadded comparison semantics:

With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal.
...
Oracle uses nonpadded comparison semantics whenever one or both values in the comparison have the data type VARCHAR2 or NVARCHAR2.

When you compare your char column with the (char) text literal, both are padded to the same length, so both conditions evaluate to true. With blank-padded semantics, 'ABC', 'ABC ', 'ABC ' etc. are all equivalent.

When you compare your char columns with the varchar2 variable no padding occurs, so only the second condition - which is manually padded to exactly the same length as the actual column value - evaluates to true. The padded column value 'ABC ' only matches the variable when it is explicitly set to exactly the same. It is not equal to just 'ABC' or anything else.

It's generally much simpler to use varchar2 for your text columns to avoid these oddities. It's unusual (in my experience anyway) to need to use fixed-length strings, and if the length of the data will vary then varchar2 is more flexible and will use less storage.

Upvotes: 1

Related Questions