escaping underscore in query builder works for postgis layer, but not for shape layer #58097
Closed
2 tasks done
Labels
Bug
Either a bug report, or a bug fix. Let's hope for the latter!
Data Provider
Related to specific vector, raster or mesh data providers
Feedback
Waiting on the submitter for answers
Comments
janhec
added
the
Bug
agiudiceandrea
added
the
Data Provider
|
@janhec, the correct syntax to use depends on the underlying driver. In the case of the ESRI Shapefile format, the driver responsible to parse and execute the filter query (WHERE clause) is the GDAL/OGR driver which in turns normally uses the OGR SQL dialect for such layer format. As stated in https://gdal.org/user/ogr_sql_dialect.html#where, you need to use an ESCAPE escape_char clause in order to define the escape character. For example, in your case you can use |
|
Thank you.
I've tried to add the ESCAPE '_' clause in query builder, but this gets
rejected at ESCAPE' as and expression parsing error.
Query (in query builder, which appears to be the problem, since elsewhere
it might work):
ESCAPE '\' and naam like '%\_%'
Error text (The dataprovider said:)
OGR[3] error 1: SQL Expression Parsing Error: syntax error, unexpected
ESCAPE.
Occurred around:
ESCAPE '\' and name like '%\_%'
^
So I think the advice, although sound in general for OGR SQL, cannot be
used here.
Please advise,
Jan
…On Sun, Jul 14, 2024 at 5:17 PM Andrea Giudiceandrea < ***@***.***> wrote:
@janhec <https://github.com/janhec>, the correct syntax to use depends on
the underlying driver. In the case of the ESRI Shapefile format, the driver
responsible to parse and execute the filter query (WHERE clause) is the
GDAL/OGR driver which in turns normally uses the OGR SQL dialect for such
layer format.
See for reference https://gdal.org/user/ogr_sql_dialect.html,
https://gdal.org/user/ogr_sql_dialect.html.
As stated in https://gdal.org/user/ogr_sql_dialect.html#where, you need
to use an ESCAPE escape_char clause in order to define the escape character.
For example, in your case you can use name like '%/_' ESCAPE '/' to
filter in all the features with the field name containing a string where
the last char is an underscore.
—
Reply to this email directly, view it on GitHub
<#58097 (comment)>, or
unsubscribe
<https://github.com/notifications/unsubscribe-auth/ADTBHLZ7U5UV6CL7P2TKTB3ZMKJBDAVCNFSM6AAAAABK27XLBKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEMRXGM4DGOBWGM>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
|
|
@janhec, have you tried the very expression I've indicated in my previous comment Just to be clear, are you referring to the "Query Builder" dialog available either through Layer -> Filter or Layer Properties -> Source -> Provider Feature Filter -> Query Builder?
|
|
I use right click Layer in TOC -> filter.
That appears to work out the same as (menu) Layer -> Filter.
Layer Properties -> Source -> Provider Feature Filter gets automatically
the same query and clicking Query Builder seems to do the same thing again.
I hope this helps,
Jan
…On Sun, Jul 14, 2024 at 6:36 PM Andrea Giudiceandrea < ***@***.***> wrote:
@janhec <https://github.com/janhec>, have you tried the very expression
I've indicated in my previous comment name LIKE '%/_' ESCAPE '/'?
Just to be clear, are you referring to the "Query Builder" dialog
available either through Layer -> Filter or Layer Properties -> Source ->
Provider Feature Filter -> Query Builder?
—
Reply to this email directly, view it on GitHub
<#58097 (comment)>, or
unsubscribe
<https://github.com/notifications/unsubscribe-auth/ADTBHL5YIFQU5NWSNPIDCDDZMKSH7AVCNFSM6AAAAABK27XLBKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEMRXGQYDOMRRGI>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
|
|
Sorry, no, I had not.
This works.
Clearly I had the wrong idea about the ESCAPE clause.
Excuses,
Jan
…On Sun, Jul 14, 2024 at 6:30 PM Andrea Giudiceandrea < ***@***.***> wrote:
@janhec <https://github.com/janhec>, have you tried the very expression
I've indicated in my previous comment name LIKE '%/_' ESCAPE '/'?
—
Reply to this email directly, view it on GitHub
<#58097 (comment)>, or
unsubscribe
<https://github.com/notifications/unsubscribe-auth/ADTBHL6N3TLADBRQDNFKSZ3ZMKRUFAVCNFSM6AAAAABK27XLBKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEMRXGQYDKOJXHE>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
|
|
It appears necessary to not use a backward slash, at least I cannot get it
to work with \ or \\.
Forward slash works fine and e.g. | too.
Is it correct that a backwards slash, which I would normally use as in
psql, is not good for this occasion?
Thanks,
Jan
…On Sun, Jul 14, 2024 at 6:53 PM Jan Heckman ***@***.***> wrote:
Sorry, no, I had not.
This works.
Clearly I had the wrong idea about the ESCAPE clause.
Excuses,
Jan
On Sun, Jul 14, 2024 at 6:30 PM Andrea Giudiceandrea <
***@***.***> wrote:
> @janhec <https://github.com/janhec>, have you tried the very expression
> I've indicated in my previous comment name LIKE '%/_' ESCAPE '/'?
>
> —
> Reply to this email directly, view it on GitHub
> <#58097 (comment)>, or
> unsubscribe
> <https://github.com/notifications/unsubscribe-auth/ADTBHL6N3TLADBRQDNFKSZ3ZMKRUFAVCNFSM6AAAAABK27XLBKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEMRXGQYDKOJXHE>
> .
> You are receiving this because you were mentioned.Message ID:
> ***@***.***>
>
|
|
@janhec, see OSGeo/gdal#10416. I think this issue report can be closed, since there is no issue in QGIS itself. |
|
Yes, it can. |
|
@janhec see OSGeo/gdal#10425. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
What is the bug or the crash?
A WHERE clause in query builder with e.g.
name like '%\_'does not function when the source layer is a shapefile.Probably this escape is not implemented in the shapefile driver, whereas it is in the postgis/postgresql driver.
This is confusing and I do not think there is a workaround, as functions (such as replace) are not supported in query builder.
Steps to reproduce the issue
Create and/or load a shapefile in qgis (3.36), having a text column (say name) where some rows contain an underscore.
Try filtering rows with an underscore through
name like '%\_'This will not return any row.
For comparison, load a similar postgis layer and apply the same query builder expression.
This will filter the rows properly.
Versions
<style type="text/css"> p, li { white-space: pre-wrap; } </style>Active Python plugins
BezierEditing
version 1.3.8
cesium_ion
v1.0.2
CityJSON-loader
0.8.1
converteer_populatie
0.1
GeoCoding
2.19
inspireNL
2.8
mmqgis
2021.9.10
moving_feature
2.0
nominatim_locator_filter
0.3.2
pdokservicesplugin
5.1.0
pdok_locatieserver_locator_filter
0.2.1
pluginbuilder3
3.2.1
plugin_reloader
0.9.4
postgis_geoprocessing
0.9
quick_map_services
0.19.34
spline
0.2
SpreadsheetLayers
2.1.2
StreetView
3.2
db_manager
0.1.20
MetaSearch
0.3.6
processing
2.12.99
Supported QGIS version
New profile
Additional context
No response
The text was updated successfully, but these errors were encountered: