QGIS "Execute SQL" does not accept more than 1 input table

Question

I have some problems using the "Execute SQL" algorithm (in General Vector Tool) in the Processing Toolbox. No problem when I've got only 1 input table, but when I try to select 2 tables as input data I get an error: virtual: Referenced table input2 in query not found!

For background information my SQL expression (works fine with add virtual layers):

select makepoint((st_line_locate_point(p.geometry, a.geometry)*st_length(a.geometry)) /*:real*/,"Z" /*:real*/,2154) as geometry
from 'input1' as a, 'input2' as p

• Inactivated Account: I can't find the tool 'execute SQL' anywhere in the Vector menu...
• Phil G: @DPSSpatial It's in Toolbox -> GDAL/OGR -> [OGR] Miscellaneous -> Execute SQL. At least in 2.14 and 2.16 it is.
• PieterB: when making use of toolbox -> qgis algorithms -> vector general tools -> execute sql then I can select multiple layers and use all of them in the sql-expression
• sylvain badey: @PieterB did you use input1 and input2 for calling your first and second entries ? or 'input1' and 'input2' ?
• PieterB: You have to refer to the name of the layer. Not input1, input2, ... eg: select makepoint((st_line_locate_point(p.geometry, a.geometry)*st_length(a.geometry)) /*:real*/,"Z" /*:real*/,2154) as geometry from layerA as a, layerB as p

Answer 1

This problem is due to a bug in the "ExecuteSQL.py" script, where an index wasn't being incremented (see the bug report here). That bug report says the issue has been resolved, so I'm not sure why it's still happening on my machine but the fix itself was easy enough to do by hand, following the fix shown here.

Basically it involves adding the following line to "ExecuteSQL.py" after line 106:

 layerIdx += 1

I just made the change, tried it and it worked.

• Leehan: I added the line in ExecuteSQL.py in QGIS 2.18.28, but an python error appears when loading Qgis layerIdx += 1 IndentationError: unexpected indent
• Don: Looks like you don't have the correct number of spaces at the front of your line. This matters because indentation is how Python keeps track of nesting and loops etc.
• Leehan: so obvious...Thanks. But the issue is before that : in the graphic modeler ExecuteSQL can't use a layer previously generated with points layer from table. It is a part of a bug that is not fixed so far according to your link (?).
• Don: @Leehan for the Graphical Modeler you might want to look at this question. The bug report I referenced was reopened about three months ago, because of problems with the Graphical Modeler. I just tried using ExecuteSQL: it works from the Processing Toolbox, but not within the Graphical Modeler, so maybe these problems require different fixes.