Re: [sqlite] sqlite bug? Subquery containing like-expression in where clause with starting '%' returns no rows, but without '%' does
Am Sonntag, den 03.02.2013, 14:00 -0700 schrieb Keith Medcalf: > > SELECT moz_places.id, moz_places.url, moz_places.title, moz_bookmarks.parent > > FROM moz_places, moz_bookmarks > > WHERE moz_places.id = moz_bookmarks.fk > >and moz_bookmarks.parent = (select id > > from moz_bookmarks > > where title like '%arbeit%') > > Firstly, are you sure you shouldn't be saying IN rather that = ? Yep. Correct. Thanks, Keith. No bug! > > SELECT moz_places.id, moz_places.url, moz_places.title, moz_bookmarks.parent >FROM moz_places, moz_bookmarks > WHERE moz_places.id = moz_bookmarks.fk > and moz_bookmarks.parent = (select id > from moz_bookmarks > where title like '%arbeit%' limit 1) > > Is what is actually being processed. > > If the id of the one row (selected by happenchance) satisfying the constraint > in the subquery does not happen to have any rows where that id is used in the > parent column, you will get no result rows at all. > > You probably mean something like: > > SELECT moz_places.id, moz_places.url, moz_places.title, moz_bookmarks.parent >FROM moz_places, moz_bookmarks > WHERE moz_places.id = moz_bookmarks.fk > and moz_bookmarks.parent IN (select id >from moz_bookmarks > where title like '%arbeit%') > > which will return results if ANY of the selected id are used in the parent > column, rather than requiring that only the happenchance selected id be used > in the parent column. > > --- > () ascii ribbon campaign against html e-mail > /\ www.asciiribbon.org > > > > -- thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite bug? Subquery containing like-expression in where clause with starting '%' returns no rows, but without '%' does
Could this be a bug? SELECT moz_places.id, moz_places.url, moz_places.title, moz_bookmarks.parent FROM moz_places , moz_bookmarks WHERE moz_places.id = moz_bookmarks.fkand moz_bookmarks.parent = (select id from moz_bookmarks where title like '%arbeit%') returns no rows, but using the subquery without the starting '%' returns data: "297607","http://www.minijob-zentrale.de/DE/0_Home/node.html","Minijob-Zentrale - Startseite","83332" Also just doing a select id from moz_bookmarks where title like '%arbeit%' also returns several rows. I am using sqlite3 version 3.7.14.1 under firefox 18 (Gecko 18.0.1) using a firefox plugin called Sqlite Manager. CREATE TABLE moz_bookmarks (id INTEGER PRIMARY KEY,type INTEGER, fk INTEGER DEFAULT NULL, parent INTEGER, position INTEGER, title LONGVARCHAR, keyword_id INTEGER, folder_type TEXT, dateAdded INTEGER, lastModified INTEGER, guid TEXT) CREATE TABLE moz_places (id INTEGER PRIMARY KEY, url LONGVARCHAR, title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL, favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, last_visit_date INTEGER, guid TEXT) -- thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problems on importing quoted csv files
Am Dienstag, den 17.05.2011, 13:53 +0800 schrieb jiajianying:
> Hello,
>
> I'm using sqlite to process some csv files. It is very disappointing
> that sqlite's csv mode doesn't support quoted csv format. I tried
> spatialite which can only strip quote marks but can't parse it correctly.
>
> Is there any suitable modules or tools for importing quoted csv files?
>
> Thanks
>
> Justin
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Have you tried to use a tab ('\t') as the field delimiter? I tried that
when exporting a calendar from gnome evolution to a csv file and
importing it into an OpenOffice spreadsheet.
--
thomas
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue 608 in sqlite-manager: select date('2011-04-29', quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17
Am Samstag, den 07.05.2011, 03:00 + schrieb
sqlite-mana...@googlecode.com:
> Comment #6 on issue 608 by mrinal.k...@gmail.com: select date('2011-04-29',
> quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17
> http://code.google.com/p/sqlite-manager/issues/detail?id=608
>
> This is the equivalent output on my computer; and Firefox 3.6.17 has sqlite
> 3.7.4 (as you have reported)
>
> user@user-office-laptop:~$ sqlite3 --version
> 3.7.4
> user@user-office-laptop:~$ sqlite3
> SQLite version 3.7.4
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select date('2011-04-29', quote(-3) || 'day');
Wait a minute. The previous sql produces 'NULL' output, yes? If so, it's
an sqlite3 problem with that 3.7.4 version, because with my 3.6.16, it's
correct as you can see in my previous mail:
---- Weitergeleitete Nachricht
> Von: Thomas Mittelstaedt <tmsta...@t-mittelstaedt.de>
> Reply-to: tmsta...@t-mittelstaedt.de
> An: sqlite-mana...@googlecode.com
> Betreff: Re: Issue 608 in sqlite-manager: select date('2011-04-29',
> quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17
> Datum: Sat, 07 May 2011 04:40:17 +0200
>
> Am Samstag, den 07.05.2011, 01:41 + schrieb
> sqlite-mana...@googlecode.com:
> > Comment #4 on issue 608 by mrinal.k...@gmail.com: select date('2011-04-29',
> >
> > quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17
> > http://code.google.com/p/sqlite-manager/issues/detail?id=608
> >
> > sqlite manager in firefox uses the sqlite library which is included with
> > firefox and not the one separately installed on the computer. It is
> > possible that the firefox version 3.6.17 includes a higher version of
> > sqlite library as compared to the earlier firefox versions.
> > According to your report, firefox 3.6.17 includes sqlite 3.7.4
> > My firefox and Ubuntu have exactly this version of sqlite.
> >
>
> tom@linux1:~$ sqlite3 --version
> 3.6.16
> tom@linux1:~$ sqlite3
> SQLite version 3.6.16
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select date('2011-04-29', quote(-3) || 'day');
> 2011-04-26
> sqlite>
>
--
thomas
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pthreadMutex
Luiz, You may try to compile with -pthread in the compile flags. Am Mittwoch, den 09.12.2009, 00:12 -0200 schrieb Luiz Gustavo P Tonello: > undefined reference to `pthread_mutexattr_init' -- thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be
Thanks for that pointer to the icu project. Did not know about that!! thomas Am Freitag, den 14.11.2008, 15:27 +0200 schrieb Elefterios Stamatogiannakis: > Has anybody successfully compiled sqlite with icu for win32? > > I haven't managed to find an libicu for mingw. Any tips welcome. > > lefteris > > D. Richard Hipp wrote: > > On Nov 14, 2008, at 8:08 AM, Martin Engelschalk wrote: > > > >> Hi all, > >> > >> the ICU project is a very powerful tool to handle codepages, and also > >> supports regular expressions (using a class named "RegexMatcher", see > >> http://icu-project.org/apiref/icu4c/classRegexMatcher.html). > >> So, it should be relatively easy to replace the like() - function in > >> sqlite (see http://www.sqlite.org/lang_corefunc.html#like and > >> http://www.sqlite.org/c3ref/create_function.html) > >> > > > > http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt=1.2 > > > > D. Richard Hipp > > [EMAIL PROTECTED] > > > > > > > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] bug? like-search with german umlaut is case-sensitive, should not be
Hallo, Just did a search on my database using SELECT * FROM ku2008 where "Empfaenger 1" like '%köck%'; and nothing was found. Doing a SELECT * FROM ku2008 where "Empfaenger 1" like '%kÖck%'; with the capital umlaut did find the record. The data is utf-8! my sqlite version is 3.5.9 on ubuntu hardy. thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
