[GRASS-dev] [GRASS GIS] #3548: sql where statement encoding issue on windows

GDAL · April 20, 2018, 10:18am

#3548: sql where statement encoding issue on windows
-----------------------------------------------+-------------------------
Reporter: martinl | Owner: grass-dev@…
Type: defect | Status: new
Priority: major | Milestone: 7.4.1
Component: Database | Version: 7.4.0
Keywords: select, where, wingrass, encoding | CPU: Unspecified
Platform: Unspecified |
-----------------------------------------------+-------------------------
SQL WHERE statements with diacritics on Windows return no records. It
seems to be a bug. Same command performed in GRASS GUI (no record, wrong)
and SQLiteBrowser (one record, correct), see
attachment:grass_bug_windows_db_select.png. It seems to be encoding issue,
tested with Czech Windows 8.1 localization (cp1250). Strangely, records
with diacritics appear correct in GRASS GUI, see
attachment:grass_bug_windows_encoding.png

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3548>
GRASS GIS <https://grass.osgeo.org>

GDAL · April 20, 2018, 10:19am

#3548: sql where statement encoding issue on windows
--------------------------+-----------------------------------------------
  Reporter: martinl | Owner: grass-dev@…
      Type: defect | Status: new
  Priority: major | Milestone: 7.4.1
Component: Database | Version: 7.4.0
Resolution: | Keywords: select, where, wingrass, encoding
       CPU: Unspecified | Platform: Unspecified
--------------------------+-----------------------------------------------
Changes (by martinl):

* Attachment "grass_bug_windows_encoding.png" added.

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3548>
GRASS GIS <https://grass.osgeo.org>

GDAL · April 20, 2018, 10:19am

#3548: sql where statement encoding issue on windows
--------------------------+-----------------------------------------------
  Reporter: martinl | Owner: grass-dev@…
      Type: defect | Status: new
  Priority: major | Milestone: 7.4.1
Component: Database | Version: 7.4.0
Resolution: | Keywords: select, where, wingrass, encoding
       CPU: Unspecified | Platform: Unspecified
--------------------------+-----------------------------------------------
Changes (by martinl):

* Attachment "grass_bug_windows_db_select.png" added.

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3548>
GRASS GIS <https://grass.osgeo.org>

GDAL · April 20, 2018, 10:23am

#3548: sql where statement encoding issue on windows
--------------------------+-----------------------------------------------
  Reporter: martinl | Owner: grass-dev@…
      Type: defect | Status: new
  Priority: major | Milestone: 7.4.1
Component: Database | Version: 7.4.0
Resolution: | Keywords: select, where, wingrass, encoding
       CPU: Unspecified | Platform: Unspecified
--------------------------+-----------------------------------------------
Description changed by martinl:

Old description:

SQL WHERE statements with diacritics on Windows return no records. It
seems to be a bug. Same command performed in GRASS GUI (no record, wrong)
and SQLiteBrowser (one record, correct), see
attachment:grass_bug_windows_db_select.png. It seems to be encoding
issue, tested with Czech Windows 8.1 localization (cp1250). Strangely,
records with diacritics appear correct in GRASS GUI, see
attachment:grass_bug_windows_encoding.png

New description:

SQL WHERE statements with diacritics on Windows return no records. It
seems to be a bug. Same command performed in GRASS GUI (no record, wrong)
and SQLiteBrowser (one record, correct). See

[[Image(grass_bug_windows_db_select.png, 500px)]].

It seems to be encoding issue, tested with Czech Windows 8.1 localization
(cp1250). Strangely, records with diacritics appear correct in GRASS GUI.
See

[[Image(grass_bug_windows_encoding.png, 500px)]]

--

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3548#comment:1>
GRASS GIS <https://grass.osgeo.org>

GDAL · April 20, 2018, 10:26am

#3548: sql where statement encoding issue on windows
--------------------------+-----------------------------------------------
  Reporter: martinl | Owner: grass-dev@…
      Type: defect | Status: new
  Priority: major | Milestone: 7.4.1
Component: Database | Version: 7.4.0
Resolution: | Keywords: select, where, wingrass, encoding
       CPU: Unspecified | Platform: Unspecified
--------------------------+-----------------------------------------------

Comment (by martinl):

Tested also from command line:

{{{
# no diacritics, works
C:\>db.select sql="select count(*) from obce where nazev='Kladno';
count(*)
2

# diacritics involved, fails
C:\>db.select sql="select count(*) from obce where nazev='Aš';
count(*)
0
}}}

Debug:

{{{
D2/5: path to db is
C:\Users\landa\Documents\grassdata\ArcCR500\PERMANENT\sqlite

D3/5: Escaped SQL: select count(*) from obce where nazev='Aš';
D3/5: describe_table()
D4/5: this is not a table column
D3/5: sqltype = 3
D3/5: litetype = 1
D3/5: nkcols = 1
D4/5: this is not a table column
D3/5: sqltype = 3
D3/5: litetype = 1
D3/5: col: count(*), nkcols 0, litetype : 1, sqltype 3
count(*)
D3/5: fetch row = -1
D3/5: col 0, litetype 1, sqltype 3: val = '0'
D3/5: Row fetched
0
D3/5: fetch row = 0
D3/5: db_close_database()
}}}

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3548#comment:2>
GRASS GIS <https://grass.osgeo.org>

GDAL · April 20, 2018, 10:41am

#3548: sql where statement encoding issue on windows
--------------------------+-----------------------------------------------
  Reporter: martinl | Owner: grass-dev@…
      Type: defect | Status: new
  Priority: major | Milestone: 7.4.1
Component: Database | Version: 7.4.0
Resolution: | Keywords: select, where, wingrass, encoding
       CPU: Unspecified | Platform: Unspecified
--------------------------+-----------------------------------------------

Comment (by martinl):

Tested also on Linux (same data, same import command). Works, seems to be
really encoding issue on Windows.

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3548#comment:3>
GRASS GIS <https://grass.osgeo.org>

GDAL · April 26, 2019, 8:12pm

#3548: sql where statement encoding issue on windows
--------------------------+-----------------------------------------------
  Reporter: martinl | Owner: grass-dev@…
      Type: defect | Status: new
  Priority: major | Milestone: 7.6.2
Component: Database | Version: 7.4.0
Resolution: | Keywords: select, where, wingrass, encoding
       CPU: Unspecified | Platform: Unspecified
--------------------------+-----------------------------------------------
Changes (by martinl):

* milestone: 7.4.5 => 7.6.2

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3548#comment:8>
GRASS GIS <https://grass.osgeo.org>