Subquery different behavior across databases

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.