- edited description
LIKE and whitespace
It seems that nunicode LIKE
differs from SQLite's LIKE
.
For example, if there is white-space in a table value, it does not find this record:
If we have a table 'person' with a single column 'name' and single value 'john doe', using SELECT * FROM person WHERE name LIKE '%john doe%
will return 0. Why is this so? Removing the % signs does not help either..
P.S.: It doesn't matter if the collation of the column is set to NU1000 or is the default one.
Comments (22)
-
reporter -
reporter P.S.: Calling directly
SELECT 'Document Name' LIKE '%Document Name%';
works as expected. -
repo owner - changed status to open
I can't reproduce this:
$ sqlite3 :memory: SQLite version 3.8.2 2013-12-06 14:53:30 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .load ./libnusqlite3.so sqlite> create table person (name); sqlite> insert into person values ("john doe"); sqlite> select * from person where name like '%john doe%'; john doe sqlite> select 'ф' like 'Ф'; 1 sqlite>
What
select 'ф' like 'Ф'
returns in your case? What whitespace symbol is used between words "john" and "doe"? -
repo owner Also SQLite version?
-
reporter I use SQLite 3.23.1
If I use the command line utility, it works as expected.
If I use c code, with
nunicode_sqlite3_static_init
called, it fails. If I do not callnunicode_sqlite3_static_init
, it works as expected.select 'ф' like 'Ф'
works as expected in the command line, but fails in code.I am really puzzled what could be the problem.
P.S.: It turns out that just the % sign is enough, so it is not a white-space problem. I.e.
LIKE 'john'
works,LIKE '%john%'
does not..P.P.S.: NU1000 collation works as expected in both places.
-
repo owner What do you mean by "
LIKE 'john'
works"? Does it return result? I don't think it normally should becauseLIKE 'john'
is exact match pattern and it shouldn't match'john doe'
.Please
- Link your test database or email it to me (you can find email at the bottom of the overview page)
- Specify all steps to reproduce the issue including all SQL queries exactly as they are
- Actual result(s) that you're getting including SQLite return code
- Expected result(s)
-
reporter Ok, I will do that.
P.S.: I do not see an email on your overview page, but I can share a link here.
-
repo owner -
reporter It looks more like a link problem, because all builtin functions that nunicode replaces are not working in my main project. However, if I create a new project from the scratch, everything seems to work OK.
I will continue to investigate what could be the problem here..
P.S.: NU1000 collation however works everywhere..
-
reporter It turns out that it has something to do with the Poco libraries I use. If I link to them, the problems start to appear. The Poco libraries contain copy of SQLite 3.23.1 (so I do not link to any other SQLite3 library). I guess it has something to do with this, but I am wondering what..
-
repo owner Sorry, not familiar with Poco so can't say anything. I suspect maybe it provides it's own LIKE implementation or something (judging by "NU1000 collation however works everywhere"). I'll keep this issue open anyway, i'll close it later.
-
reporter I can't find such things in their code so far, but I've asked the developers just in case I am missing something.
-
reporter I've found out what is the problem here. POCO libraries are compiled by default with the
SQLITE_OMIT_UTF16
define. It seems that the missing UTF-16 functionality causes nusqlite to corrupt something when loaded and this is why you get this weird behavior there.Do you think that it will be possible to somehow make the extension compatible with such cases or I have to compile the POCO libraries without this define?
-
repo owner Take a look at the SQLite extension source code: https://bitbucket.org/alekseyt/nunicode/src/66dfe6e3ad9e8c0ca3331cfd2415d87cff8a3e09/sqlite3/nusqlite3.c?at=master&fileviewer=file-view-default#nusqlite3.c-508 . You could try to comment out UTF-16 bits if you want, then recompile extension.
-
reporter Yes, I already do that. Probably I will just create a pull request for a NU_NO_UTF16, which will omit this functions, if you don't mind?
-
repo owner If that works, then perhaps those bits has to be under
#ifdef NU_WITH_UTF16
. You could just tell me if that works or not, i can make necessary changes.Taking this into consideration, do you still need static target for SQLite extension (
#13) or not anymore? -
reporter Yes, adding
#ifdef NU_WITH_UTF16
around the appropriate bits works, but they are more than just theREGISTER_XXX
functions. A number of other functions have to be worked, because they reference SQLite functions that are not available whenSQLITE_OMIT_UTF16
is defined (likenunicode_sqlite3_casemapping_utf16le
, etc..).I also use the static target, it works excellent.
-
repo owner Yeah, other functions would need to be enclosed into
#ifdef
too. But keep in mind that it "works" only becauselibnu/config.h
isn't included into SQLite plugin, thusNU_WITH_EVERYTHING
(which i assume you compile with) doesn't defineNU_WITH_UTF16
. When this is fixed (too), you would need to change your compilation options according to how you want to build all this stuff.NU_WITH_EVERYTHING
is defined in rootCMakeLists.txt
: https://bitbucket.org/alekseyt/nunicode/src/66dfe6e3ad9e8c0ca3331cfd2415d87cff8a3e09/CMakeLists.txt?at=master&fileviewer=file-view-default#CMakeLists.txt-10set(NU_BUILD_OPTIONS "-DNU_WITH_EVERYTHING")
I'll look into this bit later and see what can be done to introduce compile options into SQLite extension.
-
repo owner You could try a9b33eb. After looking into this, i think your idea of having UTF16 disablable in SQLite extension specifically is superior. I've added optional compilation flag
NU_SQLITE_OMIT_UTF16
that can be set explicitly, or set implicitly if either ofNU_WITH_UTF16
orNU_WITH_UTF16HE
aren't set in libnu build options. It can also be enabled with CMake option likeccmake .. -DNU_SQLITE_OMIT_UTF16=ON
. -
repo owner -
reporter Thanks, Aleksey! With the new commits everything works very smoothly!
-
repo owner - changed status to resolved
You're welcome. I'm going to close this as resolved, feel free to reopen or open another issue if anything comes up.
- Log in to comment