sql: ErrNoRows is not got when no is matched a query

[Archos]

I'm running this SQL code (in PostgreSQL) to get the first email, if any.

    row := db.QueryRow(`SELECT COUNT(*) as N FROM email WHERE address = $1`, email)
    var n int
    err := row.Scan(&n)

And I was expecting that "err == sql.ErrNoRows" when there is not any match in the DB, because the function says: "If no row matches the query, Scan returns ErrNoRows."[1]

Am I missing anything?


[1]: http://golang.org/pkg/database/sql/#Row.Scan

[Daniel Theophanes]

select count(*) is an aggregate function, it will always return a result.

-Daniel

[Patrick Mylund Nielsen]

A single row with 0 is the result for a SELECT COUNT(*) on 0 elements, and n for n elements. Do SELECT * FROM email WHERE address = $1 and you won't get any rows.

--
 
 

[Peter S]

As others have explained, the reason is that COUNT(*) returns a table with a single row.

I believe the confusion is caused by the word "match" in the godoc; even though the WHERE clause doesn't "match" any rows, the result of the WHERE clause is not the result of the query.

I think using something like "the result is empty" would be less confusing, and more consistent with both common SQL terminology and the rest of the godoc. ("Match" is only used in this paragraph in the godoc.)

For example: "Scan copies the columns from the result row into the values pointed at by dest. If the result of the query contains more than one row, Scan uses the first row and discards the rest. If the result is empty, Scan returns ErrNoRows."

For reference, SQL-92 (draft: [1]) describes it as "the result of the <query specification> is an empty table".

[1] http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Peter

--
 
 

[Archos]

http://code.google.com/p/go/issues/detail?id=4321