What is typeof(dancename)? I bet it's not TEXT.
sqlite> CREATE TABLE test(id INTEGER PRIMARY KEY, dancename TEXT);
sqlite> INSERT INTO test(dancename) VALUES ('Waltz'), (cast('Waltz' AS
BLOB));
sqlite> SELECT *, typeof(dancename) FROM test WHERE dancename = 'Waltz';
id dancename typeof(dancename)
---------- ---------- -----------------
1 Waltz text
sqlite> SELECT *, typeof(dancename) FROM test WHERE dancename LIKE 'Waltz';
id dancename typeof(dancename)
---------- ---------- -----------------
1 Waltz text
2 Waltz blob
On Tue, Jun 4, 2019 at 11:26 AM Doug <[email protected]> wrote:
> Sqlite version is 3.27.1.
>
> I have no indexes defined.
>
> Here is a query I just ran from bash:
>
> select songfile_id,dancename,dancegroupname from songfiletable where
> songfile_id=377;
> 377|Waltz|American Smooth
>
> ... and another:
>
> select songfile_id,dancename,dancegroupname from songfiletable where
> dancename like "Waltz";
> 377|Waltz|American Smooth
> 388|Waltz|American Smooth
> 459|Waltz|American Smooth
> 647|Waltz|American Smooth
> 827|Waltz|American Smooth
> 873|Waltz|American Smooth
> 896|Waltz|American Smooth
> 1156|Waltz|American Smooth
> 1157|Waltz|American Smooth
> 1158|Waltz|American Smooth
> 1159|Waltz|American Smooth
> 1160|Waltz|American Smooth
> 1161|Waltz|American Smooth
> 1162|Waltz|American Smooth
> 1164|Waltz|American Smooth
> 1167|Waltz|American Smooth
>
> ... and yet another:
>
> select songfile_id,dancename,dancegroupname from songfiletable where
> dancename = "Waltz";
> sqlite>
>
> Nothing selected using "=".
>
> Also a hex dump:
>
> select songfile_id,dancename,hex(dancename),dancegroupname from
> songfiletable where songfile_id=377;
> 377|Waltz|57616C747A|American Smooth
>
> Clearly what I think is in the database is there. Is there supposed to be
> a trailing null in the database for text fields that is/is not in play here?
>
> Doug
>
> > -----Original Message-----
> > From: sqlite-users <[email protected]>
> > On Behalf Of [email protected]
> > Sent: Tuesday, June 04, 2019 5:00 AM
> > To: [email protected]
> > Subject: sqlite-users Digest, Vol 138, Issue 4
> >
> > Send sqlite-users mailing list submissions to
> > [email protected]
> >
> > To subscribe or unsubscribe via the World Wide Web, visit
> > http://mailinglists.sqlite.org/cgi-
> > bin/mailman/listinfo/sqlite-users
> > or, via email, send a message with subject or body 'help' to
> > [email protected]
> >
> > You can reach the person managing the list at
> > [email protected]
> >
> > When replying, please edit your Subject line so it is more
> > specific
> > than "Re: Contents of sqlite-users digest..."
> >
> >
> > Today's Topics:
> >
> > 1. Re: SQLite build on Risc-V (Carlos Eduardo de Paula)
> > 2. select * where abc like "xxx" works, ...where abc='xxx'
> > fails
> > (Doug)
> > 3. Re: select * where abc like "xxx" works, ...where abc='xxx'
> > fails (Simon Slavin)
> > 4. Re: select * where abc like "xxx" works, ...where abc='xxx'
> > fails (Keith Medcalf)
> > 5. Re: Bug in sqlite3.c (bhandari_nikhil)
> >
> >
> > ------------------------------------------------------------------
> > ----
> >
> > Message: 1
> > Date: Sun, 2 Jun 2019 11:15:35 -0300
> > From: Carlos Eduardo de Paula <[email protected]>
> > To: [email protected], "[email protected]"
> > <[email protected]>
> > Subject: Re: [sqlite] SQLite build on Risc-V
> > Message-ID:
> > <[email protected]
> > il.com>
> > Content-Type: text/plain; charset="UTF-8"
> >
> > I'm on a SiFive Unleashed board running Debian Linux on Kernel
> > 4.19.
> > --
> > Sent from IPhone
> >
> >
> > ------------------------------
> >
> > Message: 2
> > Date: Mon, 3 Jun 2019 16:46:47 -0700
> > From: "Doug" <[email protected]>
> > To: <[email protected]>
> > Subject: [sqlite] select * where abc like "xxx" works,
> > ...where
> > abc='xxx' fails
> > Message-ID: <[email protected]>
> > Content-Type: text/plain; charset="us-ascii"
> >
> > I am using Sqlite under QT 5.12.0.
> >
> >
> >
> > Why does the "=" query fail and the "like" query work? There are
> > no
> > wildcards involved.
> >
> >
> >
> > I create the table this way:
> >
> >
> >
> > QString sqlcreate = QLatin1String(
> >
> > "CREATE TABLE songfiletable ("
> >
> > "songfile_id INTEGER PRIMARY KEY,"
> >
> > "dancename TEXT"
> >
> > ");" );
> >
> >
> >
> > I populated the table this way with
> > dancename=QLatin1String("Waltz"):
> >
> >
> >
> > QString sqlinsert = QLatin1String(
> >
> > "INSERT INTO songfiletable ("
> >
> > "dancename"
> >
> > ") VALUES(?);");
> >
> > queryinsert.prepare(sqlinsert);
> >
> > queryinsert.addBindValue(dancename.toUtf8());
> >
> >
> >
> > The query that works for dancename="Waltz" is this (with 'like'):
> >
> > QByteArray dn = d->dance_name.toUtf8();
> >
> > QByteArray filter = "(dancename like '" + dn + "')";
> >
> >
> >
> > The query that fails for dancename="Waltz" is this:
> >
> > QByteArray dn = d->dance_name.toUtf8();
> >
> > QByteArray filter = "(dancename='" + dn + "')";
> >
> >
> >
> > The behavior is the same in Sqlite command line. There are no
> > wildcards involved.
> >
> >
> >
> > I have simplified the table and the queries to a single column for
> > this message. The actual table has 22 columns.
> >
> >
> >
> > Why does the "=" query fail and the "like" query work?
> >
> >
> >
> > Regards, Doug
> >
> >
> >
> >
> >
> >
> >
> > ------------------------------
> >
> > Message: 3
> > Date: Tue, 4 Jun 2019 00:55:45 +0100
> > From: Simon Slavin <[email protected]>
> > To: SQLite mailing list <[email protected]>
> > Subject: Re: [sqlite] select * where abc like "xxx" works,
> > ...where
> > abc='xxx' fails
> > Message-ID: <[email protected]>
> > Content-Type: text/plain; charset=us-ascii
> >
> > On 4 Jun 2019, at 12:46am, Doug <[email protected]> wrote:
> >
> > > Why does the "=" query fail and the "like" query work?
> >
> > To help us investigate ...
> >
> > Which version of SQLite is this ? You can use
> >
> > SELECT sqlite_version();
> >
> > to find out.
> >
> > What do you mean by 'work' and 'fail' ? Are you referring to an
> > error code ?
> >
> > Can you add a debug line to your code so that just before applying
> > the filter it outputs the same text to a log ? Just to check
> > whether '.toUtf8()' does what you think it's doing.
> >
> > ------------------------------
> >
> > Message: 4
> > Date: Mon, 03 Jun 2019 18:33:17 -0600
> > From: "Keith Medcalf" <[email protected]>
> > To: "SQLite mailing list" <[email protected]>
> > Subject: Re: [sqlite] select * where abc like "xxx" works,
> > ...where
> > abc='xxx' fails
> > Message-ID: <[email protected]>
> > Content-Type: text/plain; charset="utf-8"
> >
> >
> > >Why does the "=" query fail and the "like" query work? There are
> > no
> > >wildcards involved.
> >
> > >The behavior is the same in Sqlite command line. There are no
> > >wildcards involved.
> >
> > sqlite> create table songfiletable (
> > ...> songfile_id integer primary key,
> > ...> dancename text
> > ...> );
> > sqlite> insert into songfiletable (dancename) values ('Waltz');
> > sqlite> select * from songfiletable where dancename like 'Waltz';
> > 1|Waltz
> > sqlite> select * from songfiletable where dancename = 'Waltz';
> > 1|Waltz
> >
> > Works for me. Perhaps your database does not contain what you
> > believe it does ... or you have some indexes on dancename that you
> > are not disclosing
> >
> > ---
> > The fact that there's a Highway to Hell but only a Stairway to
> > Heaven says a lot about anticipated traffic volume.
> >
> >
> > >-----Original Message-----
> > >From: sqlite-users [mailto:sqlite-users-
> > >[email protected]] On Behalf Of Doug
> > >Sent: Monday, 3 June, 2019 17:47
> > >To: [email protected]
> > >Subject: [sqlite] select * where abc like "xxx" works, ...where
> > >abc='xxx' fails
> > >
> > >I am using Sqlite under QT 5.12.0.
> > >
> > >
> > >
> > >
> > >
> > >
> > >I create the table this way:
> > >
> > >
> > >
> > >QString sqlcreate = QLatin1String(
> > >
> > > "CREATE TABLE songfiletable ("
> > >
> > > "songfile_id INTEGER PRIMARY KEY,"
> > >
> > > "dancename TEXT"
> > >
> > > ");" );
> > >
> > >
> > >
> > >I populated the table this way with
> > dancename=QLatin1String("Waltz"):
> > >
> > >
> > >
> > >QString sqlinsert = QLatin1String(
> > >
> > > "INSERT INTO songfiletable ("
> > >
> > > "dancename"
> > >
> > > ") VALUES(?);");
> > >
> > > queryinsert.prepare(sqlinsert);
> > >
> > > queryinsert.addBindValue(dancename.toUtf8());
> > >
> > >
> > >
> > >The query that works for dancename="Waltz" is this (with 'like'):
> > >
> > > QByteArray dn = d->dance_name.toUtf8();
> > >
> > > QByteArray filter = "(dancename like '" + dn + "')";
> > >
> > >
> > >
> > >The query that fails for dancename="Waltz" is this:
> > >
> > > QByteArray dn = d->dance_name.toUtf8();
> > >
> > > QByteArray filter = "(dancename='" + dn + "')";
> > >
> > >
> > >
> > >
> > >
> > >
> > >I have simplified the table and the queries to a single column
> > for
> > >this message. The actual table has 22 columns.
> > >
> > >
> > >
> > >Why does the "=" query fail and the "like" query work?
> > >
> > >
> > >
> > >Regards, Doug
> > >
> > >
> > >
> > >
> > >
> > >_______________________________________________
> > >sqlite-users mailing list
> > >[email protected]
> > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> > users
> >
> >
> >
> >
> >
> > ------------------------------
> >
> > Message: 5
> > Date: Mon, 3 Jun 2019 22:11:48 -0700 (MST)
> > From: bhandari_nikhil <[email protected]>
> > To: [email protected]
> > Subject: Re: [sqlite] Bug in sqlite3.c
> > Message-ID: <[email protected]>
> > Content-Type: text/plain; charset=us-ascii
> >
> > Thanks Dan. I had checked the database integrity using the
> > following command:
> >
> > sqlite3 myfile.db "PRAGMA integrity_check;"
> >
> > And it had reported ok. I will see if I can share the database
> > file here.
> > Can you let me know how to check the db file (in case I am not
> > able to share
> > the db file here) ? And how the fts5 can get corrupted ?
> >
> > To run the rebuild command, the ft refers to the db name ?
> >
> > BTW, I just looked at the code, not used the latest version.
> >
> >
> >
> > --
> > Sent from: http://sqlite.1065341.n5.nabble.com/
> >
> >
> > ------------------------------
> >
> > Subject: Digest Footer
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [email protected]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> > users
> >
> >
> > ------------------------------
> >
> > End of sqlite-users Digest, Vol 138, Issue 4
> > ********************************************
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
