DBManager cannot execute a SQL query on a virtual table whose name begins with numbers

[FERRATON]

To reproduce: Create a virtual table and give it a name starting with numbers Make a SQL request on this virtual table in DBManager. Error message: Query execution error on DROP TABLE IF EXISTS "2019_mytable"; CREATE VIRTUAL TABLE "2019_mytable" USING QgsVLayer(2019_mytable_55424896_8ff2_48d9_8424_0ab2361b6d04);: 1 - unrecognized token: "2019_mytable_55424896_8ff2_48d9_8424_0ab2361b6d04"

[elpaso]

Quoting table identifier is probably required in this case.

[FERRATON]

I forgot to mention that it doesn't work with quotation marks either.

[elpaso]

I meant that the plugin python code should take care of the correct quoting, not the user.

[gioman]

I forgot to mention that it doesn't work with quotation marks either.

@FERRATON works here with quotes (QGIS master on Ubuntu 20.04) which is totally expected to me.

[FERRATON]

I still see the issue (QGIS 3.16.0 windows 10)

[gioman]

QGIS 3.16.0

@FERRATON can you kindly add the exact steps to replicate? If possible also try on a updated version of QGIS, 3.16.0 is outdated. Thanks!

[github-actions[bot]]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale". If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue. In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue. If there is no further activity on this issue, it will be closed in a week.

[FERRATON]

create a virtual layer, whose name starts with numbers, from any table (see capture1) make a simple query on this virtual layer in DBManager (see capture2) QGIS 3.16.3 (windows 10)

[Jakob-Lanstorp]

This problem, a table name starting with a number, used in a virtual layer query, also exists in QGIS 3.18.1

[Jakob-Lanstorp]

Still a problem in Windows 10 - OSGEO QGIS 3.18.3-Zürich. Wrapping table name with quotation marks won't work, throwing an "1 - unreconized token ...", when making a virtual layer from a table starting with a number.

It is the same using Python:

from qgis.core import QgsVectorLayer
vlayer = QgsVectorLayer( '?query=SELECT * from "8100_Paleogen_Kalk_Bund"', 'vlayer', 'virtual' )
QgsProject.instance().addMapLayer(vlayer)

throwing:

CRITICAL Layer vlayer : Error while executing feature count request : Query preparation error on SELECT Count(*),Min(MbrMinX("")),Min(MbrMinY("")),Max(MbrMaxX("")),Max(MbrMaxY("")) FROM : incomplete input

[decheu]

Still an issue on Windows 10 QGIS 3.16.11 OSGEO install

The query

SELECT "007_ABWASSERKNOTEN"."geometry" 
FROM "007_ABWASSERKNOTEN", "008__VORFLUTEREINLAUF"
WHERE "007_ABWASSERKNOTEN""007_ABWASSERKNOTEN"."GlobalId" = "008__VORFLUTEREINLAUF"."KNOTEN_REF"

results in

Query execution error on DROP TABLE IF EXISTS "007_ABWASSERKNOTEN"; CREATE VIRTUAL TABLE "007_ABWASSERKNOTEN" USING QgsVLayer(004a_421dcb47_6093_40a3_b43f_d195c1bfdfaa);: 1 - unrecognized token: "004a_421dcb47_6093_40a3_b43f_d195c1bfdfaa"

As mentioned above, quotation marks (also suggested here: https://trac.osgeo.org/gdal/ticket/5738) don't help.

[lbartoletti]

@FERRATON I cannot reproduce on QGIS 3.22

2012test is a virtual layer based on tic_cable via "SELECT * FROM tic_cable"

[Jakob-Lanstorp]

Indeed this issue seems to be solved now. Cannot reproduce anymore on Windows 10 with OSGEO QGIS 3.22.3

[FERRATON]

The issue is not resolved in QGIS 3.22.3 (package MSI - windows 10).

[lbartoletti]

The issue is not resolved in QGIS 3.22.3 (package MSI - windows 10).

Mmm it's weird, since @Jakob-Lanstorp said it's solved on Win10. The only difference is MSI vs OSGEO...

[gioman]

Works fine on master/win10 (osgeo4w and standalone build from last commit).

[Jakob-Lanstorp]

Had a colleague test it on a Win 10 MSI standalone QGIS 3.22.3 installation and it still fails there. But appear fixed in the OSGEO QGIS 3.22.3 version.

[gioman]

Had a colleague test it on a Win 10 MSI standalone QGIS 3.22.3 installation and it still fails there. But appear fixed in the OSGEO QGIS 3.22.3 version.

@Jakob-Lanstorp works fine here on standalone 3.22.4 msi

[FERRATON]

Weird! I just loaded a QGIS 3.22.4 version with Osgeo4w and the bug is still there. Would it depend on the initial data set and not only the name of the virtual layer?

[FERRATON]

no... if I give a name without numbers and with the same dataset it works...

[FERRATON]

Sorry! I correct... if you put the name between "" it works with QGIS 3.22 MSI or QGIS 3.22.4 osgeo4w

[lbartoletti]

Sorry! I correct... if you put the name between "" it works with QGIS 3.22 MSI or QGIS 3.22.4 osgeo4w

So, it's OK?

[FERRATON]

Yes for me.

[kleenkanteen]

Just happened to me, joining a .shp file and a .dbf file:

QGIS version: 3.32.3-Lima

Latest version of w10

[kleenkanteen]

edit: I just upgraded to 3.34.0 and tried again. Works now, great