LIKE and whitespace

[petko]

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.

[petko]

P.S.: Calling directly SELECT 'Document Name' LIKE '%Document Name%'; works as expected.

[Aleksey Tulinov]

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"?

[Aleksey Tulinov]

Also SQLite version?

[petko]

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 call nunicode_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.

[Aleksey Tulinov]

What do you mean by "LIKE 'john' works"? Does it return result? I don't think it normally should because LIKE 'john' is exact match pattern and it shouldn't match 'john doe'.

Please

1. Link your test database or email it to me (you can find email at the bottom of the overview page)
2. Specify all steps to reproduce the issue including all SQL queries exactly as they are
3. Actual result(s) that you're getting including SQLite return code
4. Expected result(s)

[petko]

Ok, I will do that.

P.S.: I do not see an email on your overview page, but I can share a link here.

[Aleksey Tulinov]

Email: https://bitbucket.org/alekseyt/nunicode/overview#markdown-header-questions

[petko]

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..

[petko]

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..

[Aleksey Tulinov]

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.

[petko]

I can't find such things in their code so far, but I've asked the developers just in case I am missing something.

[petko]

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?

[Aleksey Tulinov]

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.

[petko]

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?

[Aleksey Tulinov]

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?

[petko]

Yes, adding #ifdef NU_WITH_UTF16 around the appropriate bits works, but they are more than just the REGISTER_XXX functions. A number of other functions have to be worked, because they reference SQLite functions that are not available when SQLITE_OMIT_UTF16 is defined (like nunicode_sqlite3_casemapping_utf16le, etc..).

I also use the static target, it works excellent.

[Aleksey Tulinov]

Yeah, other functions would need to be enclosed into #ifdef too. But keep in mind that it "works" only because libnu/config.h isn't included into SQLite plugin, thus NU_WITH_EVERYTHING (which i assume you compile with) doesn't define NU_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 root CMakeLists.txt: https://bitbucket.org/alekseyt/nunicode/src/66dfe6e3ad9e8c0ca3331cfd2415d87cff8a3e09/CMakeLists.txt?at=master&fileviewer=file-view-default#CMakeLists.txt-10

set(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.

[Aleksey Tulinov]

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 of NU_WITH_UTF16 or NU_WITH_UTF16HE aren't set in libnu build options. It can also be enabled with CMake option like ccmake .. -DNU_SQLITE_OMIT_UTF16=ON.

[Aleksey Tulinov]

Please note that a9b33eb also includes commit that resolves #13.

[petko]

Thanks, Aleksey! With the new commits everything works very smoothly!

[Aleksey Tulinov]

You're welcome. I'm going to close this as resolved, feel free to reopen or open another issue if anything comes up.