As previously outlined in this ticket, subqueries performed using SQLite backend do not fail if they return multiple results. Other database backends do error if multiple results are returned. I encountered this issue when moving from SQLite to PostgreSQL
Richard Eames provided the helpful table below outlining the behavior across different database engines:
| Engine | SubQuery Rows | Result |
|---|---|---|
| MySQL | 0 | No error, 0 records |
| MySQL | 1 | No error, 1 record |
| MySQL | 2 | Error |
| PostgreSQL | 0 | No error, 0 records |
| PostgreSQL | 1 | No error, 1 record |
| PostgreSQL | 2 | Error |
| SQLite | 0 | No error, 0 records |
| SQLite | 1 | No error, 1 record |
| SQLite | 2 | No error, 2 records |
While this did not cause any major issues for us, it was difficult to track down the root cause, and the silent nature could have in theory concealed a bug in a incorrectly written query.
The previous discussion mentioned possibly validating queries and that could in theory be done in the SQLite backend’s check_expression_support method. However it may prove difficult to capture every case in which this would occur.
I submitted a pull request (17655) to add documentation around this, and felixxm recommended to start a discussion to get more opinions on if there is a way to handle this in code, if it is worth documenting, or if it should just be ignored altogether.