Home » SQL & PL/SQL » SQL & PL/SQL » count(1) a bug (oracle 10g)
count(1) a bug [message #348603] Wed, 17 September 2008 07:06 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Can anyone please explain how count(1) is considered to be an oracle bug and count(*) is prefered to use??
Re: count(1) a bug [message #348605 is a reply to message #348603] Wed, 17 September 2008 07:10 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
both are same . no diffrence at all,neither in functioning nor in performance.
Re: count(1) a bug [message #348607 is a reply to message #348603] Wed, 17 September 2008 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a logical bug as you want to count rows and not "1".
And why 1 and not 2 or 3 or 42?

Regards
Michel
Re: count(1) a bug [message #348610 is a reply to message #348603] Wed, 17 September 2008 07:24 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
understood the logical point but from the database point of view can there be any chance of error while fetching the number of rows?

In all the instances count(1) and count(*) never differ

[Updated on: Wed, 17 September 2008 07:25]

Report message to a moderator

Re: count(1) a bug [message #348617 is a reply to message #348610] Wed, 17 September 2008 07:35 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156151916789

Check this you will find all your answers.

Regards,
Rajat
Re: count(1) a bug [message #348619 is a reply to message #348603] Wed, 17 September 2008 07:36 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

http://www.orafaq.com/forum/m/322251/102855/#msg_322251

This link may help you find the answer of your question

Regards,
Dipali..
Re: count(1) a bug [message #348622 is a reply to message #348610] Wed, 17 September 2008 07:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To the best of my knowledge, they will never differ, as COUNT(!) is rendered internally into COUNT(*)
Re: count(1) a bug [message #348726 is a reply to message #348622] Wed, 17 September 2008 15:52 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ummmm ... no. This is internally rendered into the "missing expression" error ./fa/1987/0/

Yes, I know, it was a typo, I'm just kidding.
Re: count(1) a bug [message #348737 is a reply to message #348603] Wed, 17 September 2008 16:35 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Its not a logical bug at all. It just depends on who's logic you are following...

From the ask tom thread:-
sure, that counts the number of NON-NULL 1's returned by that query.  That is the same as 
"count(*)".

It is the same as "select count(<any non-null column>) ...."

count(1) is count(*) in disguise.


However, "select count(<any non-null column>)" is not a true statement for count(*)

If you have a table with 1 row, 4 columns, all of them set to NULL, count(*) will still return a count of 1, when according to the above statement from Tom, it should return 0.

Therefore count(1) is more logical than count(*) if the above description is to be beleived.

So to recap, this is totally pointless as the 2 are equivalent in every way.


Re: count(1) a bug [message #348826 is a reply to message #348737] Thu, 18 September 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
However, "select count(<any non-null column>)" is not a true statement for count(*)

True. Logical, one counts the number or not null valued in the column and the other one the number of rows.
I know the result is the same for not null column but logically speaking is not the same, count what you actually want to count and not an other thing even if you know the result are the same.
What happens if some day, model changes and the column can now be null? Your code is wrong, you have to change it.

Quote:
If you have a table with 1 row, 4 columns, all of them set to NULL, count(*) will still return a count of 1, when according to the above statement from Tom, it should return 0.

Therefore count(1) is more logical than count(*) if the above description is to be beleived.

Wrong. You misunderstand what is "*" there, it does NOT mean a list of columns, it means row.
If I quote the doc (emphasis is mine):
COUNT in SQL Reference
If you specify the asterisk (*), then this function returns all rows, including duplicates and nulls


Regards
Michel
Re: count(1) a bug [message #348846 is a reply to message #348603] Thu, 18 September 2008 01:56 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
Therefore count(1) is more logical than count(*) if the above description is to be beleived.


Im not saying I beleive the description, I was just playing devils advocat. as you say count(*) behaves like row count, not count(not null column).
Previous Topic: Bulk Update
Next Topic: data encryption in oracle 10g following hipaa guidelines
Goto Forum:
  


Current Time: Sun Dec 04 08:55:44 CST 2016

Total time taken to generate the page: 0.15262 seconds