QGIS DB Manager - Load new layer not loading SQL query

As an aside to anyone else who come across this, I found that when I changed my query to select specific columns, not select all columns, I was then able to load the layer.

I can't say why exactly but I believe that by selecting all the columns I was returning two identically names columns (boreid) in the query result. When I amend the query to not return the boreid column from the second table the subsequent data will load as a layer.

I also experienced this problem, and it was because the field I indicated that was supposed to have unique values did not in fact have unique values. After fixing that, everything went as expected. Not very friendly to not get an error message-

I had similar problem in QGIS Madeira and I solved it by changing in DB Manager plug-in the getExecutableSQLQuery() to getSqlExecutableQuery() (there are 4 instances in the file).

So just go to C:\Program Files\QGIS 3.4\apps\qgis-ltr\python\plugins\db_manager, open the file dlg_sql_window.py and with find and replace change the above and save (with QGIS program not active).

On both QGIS Wien 2.8 and QGIS 3.4 Madeira, my problem was a bit different. Though I tried logging the error to get more details, I only got repeating lines of:

PostGIS(1): invalid PostgreSQL layer

Connecting as a database-user other than postgres fixed the problem.

I'm not sure what goes on under the hood for that to make a difference, but it appears that's the case.

I had this problem because I had a semicolon (;) at the end of my query. This messes up how QGIS was loading the query. I found this out when I learned that you can view logs in QGIS while trying to figure this out. Nice!

This was what I saw in the logs:

2022-06-09T18:15:28     WARNING    Unable to execute the query.
             The error message from the database was:
             ERROR: syntax error at or near ";"
             LINE 1: ... on ST_DWithin(cs.wkb_geometry, s.wkb_geometry, 2) limit 10;
              ^
             .
             SQL: SELECT * FROM (SELECT row_number() over () AS _uid0_, * FROM (select cs.* from class_segments as cs join shorelines as s on ST_DWithin(cs.wkb_geometry, s.wkb_geometry, 2) limit 10;
             ) AS _subq_0_
             ) AS "subQuery_0" LIMIT 1

which clued me into the fact that the semicolon was interrupting the query that was being injected.

You can view logs panel with the little message icon at the bottom right of the QGIS window.

So, when I changed

select cs.* from class_segments as cs join shorelines as s on ST_DWithin(cs.wkb_geometry, s.wkb_geometry, 2);

to

select cs.* from class_segments as cs join shorelines as s on ST_DWithin(cs.wkb_geometry, s.wkb_geometry, 2)

It worked!