Timeline for PostgreSQL: simple equality query returning no results for indexed columns
Current License: CC BY-SA 3.0
23 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 18, 2021 at 3:03 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Nov 8, 2020 at 4:01 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Jul 10, 2020 at 7:07 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Mar 10, 2020 at 6:02 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Nov 10, 2019 at 22:01 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Jul 13, 2019 at 2:02 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Mar 12, 2019 at 11:00 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Nov 7, 2017 at 11:31 | answer | added | Daniel Vérité | timeline score: 2 | |
| Nov 6, 2017 at 19:26 | history | edited | Peter M | CC BY-SA 3.0 |
clarification about NULLs
|
| Nov 6, 2017 at 17:26 | comment | added | Peter M |
@ypercubeᵀᴹ SELECT COUNT(*), COUNT(citing_id) FROM citations; gives me 148130999 | 148130999, and your second query (NOT DISTINCT FROM) gives me the 29 rows I would expect. Is it true that if there are no NULLs in a column, the query from my question should return at least 1 row? It seems like it is only queries that are using the hash index that are returning nothing.
|
|
| Nov 6, 2017 at 17:08 | comment | added | ypercubeᵀᴹ |
This query however will always return at least 1 row from the table - unless the table has no rows: SELECT * FROM citations WHERE citing_id IS NOT DISTINCT FROM (SELECT citing_id FROM citations LIMIT 1) ;
|
|
| Nov 6, 2017 at 17:01 | comment | added | ypercubeᵀᴹ |
Peter, what does this return?: SELECT COUNT(*), COUNT(citing_id) FROM citations; Even one row with NULL in citing_id can make your first query return 0 rows.
|
|
| Nov 6, 2017 at 16:57 | history | edited | McNets | CC BY-SA 3.0 |
added 50 characters in body
|
| Nov 6, 2017 at 16:25 | comment | added | Peter M |
@a_horse_with_no_name : I dropped the hash index on cited_id, and now a query on that column works. It looks like it was the index specifically that was preventing the query from matching.
|
|
| Nov 6, 2017 at 16:17 | history | edited | Peter M | CC BY-SA 3.0 |
added 1248 characters in body
|
| Nov 6, 2017 at 16:10 | comment | added | Peter M |
There is trailing whitespace in the indexed values. Will that break the index or WHERE clause?
|
|
| Nov 6, 2017 at 16:08 | comment | added | Peter M |
Sorry I should have been more clear. SELECT citing_id FROM citations LIMIT 1 returns exactly one row with a non-NULL value. There are no NULL values in the table.
|
|
| Nov 6, 2017 at 16:08 | comment | added | user1822 | What happens if you remove those indexes? What are the execution plans for both statements? Did you verify if there are any trailing blanks in the values? | |
| Nov 6, 2017 at 16:05 | comment | added | Daniel Vérité |
What says that SELECT citing_id FROM citations LIMIT 1 cannot return NULL ?
|
|
| Nov 6, 2017 at 15:40 | history | edited | Peter M | CC BY-SA 3.0 |
more descriptive title
|
| Nov 6, 2017 at 15:29 | history | edited | user1822 | CC BY-SA 3.0 |
added 153 characters in body
|
| Nov 6, 2017 at 15:28 | review | First posts | |||
| Nov 6, 2017 at 15:45 | |||||
| Nov 6, 2017 at 15:27 | history | asked | Peter M | CC BY-SA 3.0 |