Re: sqlite-users Digest, Vol 138, Issue 4

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: sqlite-users Digest, Vol 138, Issue 4

Doug
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 <[hidden email]>
> On Behalf Of [hidden email]
> Sent: Tuesday, June 04, 2019 5:00 AM
> To: [hidden email]
> Subject: sqlite-users Digest, Vol 138, Issue 4
>
> Send sqlite-users mailing list submissions to
> [hidden email]
>
> 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
> [hidden email]
>
> You can reach the person managing the list at
> [hidden email]
>
> 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 <[hidden email]>
> To: [hidden email],  "[hidden email]"
> <[hidden email]>
> Subject: Re: [sqlite] SQLite build on Risc-V
> Message-ID:
> <[hidden email]
> 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" <[hidden email]>
> To: <[hidden email]>
> Subject: [sqlite] select * where abc like "xxx" works,
> ...where
> abc='xxx' fails
> Message-ID: <04fc01d51a66$9c94c700$d5be5500$@comcast.net>
> 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 <[hidden email]>
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] select * where abc like "xxx" works,
> ...where
> abc='xxx' fails
> Message-ID: <[hidden email]>
> Content-Type: text/plain; charset=us-ascii
>
> On 4 Jun 2019, at 12:46am, Doug <[hidden email]> 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" <[hidden email]>
> To: "SQLite mailing list" <[hidden email]>
> Subject: Re: [sqlite] select * where abc like "xxx" works,
> ...where
> abc='xxx' fails
> Message-ID: <[hidden email]>
> 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-
> >[hidden email]] On Behalf Of Doug
> >Sent: Monday, 3 June, 2019 17:47
> >To: [hidden email]
> >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
> >[hidden email]
> >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 <[hidden email]>
> To: [hidden email]
> Subject: Re: [sqlite] Bug in sqlite3.c
> Message-ID: <[hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users
>
>
> ------------------------------
>
> End of sqlite-users Digest, Vol 138, Issue 4
> ********************************************

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite-users Digest, Vol 138, Issue 4

Doug
I tried a few more functions:

select songfile_id, dancename,hex(dancename), hex("Waltz"), length(dancename),length("Waltz"),
nullif(dancename,"Waltz"),instr(dancename,"Waltz"),dancegroupname from songfiletable where songfile_id=377;
377|Waltz|57616C747A|57616C747A|5|5|Waltz|1|American Smooth

Notice that nullif() failed to find the field and the literal equal. That is consistent with the failure.

Doug

> -----Original Message-----
> From: sqlite-users <[hidden email]>
> On Behalf Of [hidden email]
> Sent: Tuesday, June 04, 2019 5:00 AM
> To: [hidden email]
> Subject: sqlite-users Digest, Vol 138, Issue 4
>
> Send sqlite-users mailing list submissions to
> [hidden email]
>
> 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
> [hidden email]
>
> You can reach the person managing the list at
> [hidden email]
>
> 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 <[hidden email]>
> To: [hidden email],  "[hidden email]"
> <[hidden email]>
> Subject: Re: [sqlite] SQLite build on Risc-V
> Message-ID:
> <[hidden email]
> 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" <[hidden email]>
> To: <[hidden email]>
> Subject: [sqlite] select * where abc like "xxx" works,
> ...where
> abc='xxx' fails
> Message-ID: <04fc01d51a66$9c94c700$d5be5500$@comcast.net>
> 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 <[hidden email]>
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] select * where abc like "xxx" works,
> ...where
> abc='xxx' fails
> Message-ID: <[hidden email]>
> Content-Type: text/plain; charset=us-ascii
>
> On 4 Jun 2019, at 12:46am, Doug <[hidden email]> 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" <[hidden email]>
> To: "SQLite mailing list" <[hidden email]>
> Subject: Re: [sqlite] select * where abc like "xxx" works,
> ...where
> abc='xxx' fails
> Message-ID: <[hidden email]>
> 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-
> >[hidden email]] On Behalf Of Doug
> >Sent: Monday, 3 June, 2019 17:47
> >To: [hidden email]
> >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
> >[hidden email]
> >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 <[hidden email]>
> To: [hidden email]
> Subject: Re: [sqlite] Bug in sqlite3.c
> Message-ID: <[hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users
>
>
> ------------------------------
>
> End of sqlite-users Digest, Vol 138, Issue 4
> ********************************************

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite-users Digest, Vol 138, Issue 4

Shawn Wagner
In reply to this post by Doug
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 <[hidden email]> 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 <[hidden email]>
> > On Behalf Of [hidden email]
> > Sent: Tuesday, June 04, 2019 5:00 AM
> > To: [hidden email]
> > Subject: sqlite-users Digest, Vol 138, Issue 4
> >
> > Send sqlite-users mailing list submissions to
> >       [hidden email]
> >
> > 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
> >       [hidden email]
> >
> > You can reach the person managing the list at
> >       [hidden email]
> >
> > 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 <[hidden email]>
> > To: [hidden email],  "[hidden email]"
> >       <[hidden email]>
> > Subject: Re: [sqlite] SQLite build on Risc-V
> > Message-ID:
> >       <[hidden email]
> > 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" <[hidden email]>
> > To: <[hidden email]>
> > Subject: [sqlite] select * where abc like "xxx" works,
> >       ...where
> >       abc='xxx' fails
> > Message-ID: <04fc01d51a66$9c94c700$d5be5500$@comcast.net>
> > 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 <[hidden email]>
> > To: SQLite mailing list <[hidden email]>
> > Subject: Re: [sqlite] select * where abc like "xxx" works,
> > ...where
> >       abc='xxx' fails
> > Message-ID: <[hidden email]>
> > Content-Type: text/plain;     charset=us-ascii
> >
> > On 4 Jun 2019, at 12:46am, Doug <[hidden email]> 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" <[hidden email]>
> > To: "SQLite mailing list" <[hidden email]>
> > Subject: Re: [sqlite] select * where abc like "xxx" works,
> >       ...where
> >       abc='xxx' fails
> > Message-ID: <[hidden email]>
> > 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-
> > >[hidden email]] On Behalf Of Doug
> > >Sent: Monday, 3 June, 2019 17:47
> > >To: [hidden email]
> > >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
> > >[hidden email]
> > >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 <[hidden email]>
> > To: [hidden email]
> > Subject: Re: [sqlite] Bug in sqlite3.c
> > Message-ID: <[hidden email]>
> > 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> > users
> >
> >
> > ------------------------------
> >
> > End of sqlite-users Digest, Vol 138, Issue 4
> > ********************************************
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite-users Digest, Vol 138, Issue 4

Graham Holden
In reply to this post by Doug
Throwing a wild idea out: Can you try using single-quotes
(e.g. "...where dancename = 'Waltz'..."). Single-quotes are meant to
be used for text-literals; double-quotes are meant to "protect"
field/table names where they clash with keywords (or contain "odd"
characters). Additionally, do you by any chance have a field called
"Waltz" in your table?

Tuesday, June 04, 2019, 7:26:12 PM, Doug <[hidden email]> 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
[snip]
> ... and yet another:

> select songfile_id,dancename,dancegroupname from songfiletable where dancename = "Waltz";
sqlite>>

> Nothing selected using "=".



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite-users Digest, Vol 138, Issue 4

James K. Lowden
In reply to this post by Doug
On Tue, 4 Jun 2019 11:26:12 -0700
"Doug" <[hidden email]> wrote:

> select songfile_id,dancename,dancegroupname from songfiletable where
> dancename like "Waltz";

What Shawn Wagner's answer shows you is that

        'Waltz'

is a string and

        "Waltz"

is a column name, because in SQL double-quotes denote identifiers.
They don't denote strings, unlike as in, say, C.  

The double-quote escape syntax let's you have odd columns names with
spaces and such:

create table "The Blue Danube" (
        "Waltzing Matilda" text not NULL primary key
);

If there's no column name "Waltz" in songfiletable, that's a bug IMO.

As a matter of style, what is songfiletable?  A set of songs, or a
file, or a table?  Why not just "songs"?  

create table songs {
        id integer not null primary key, -- probably not needed
        dance ... ,
        dance_group ... , -- or just "group", but see next
);

If songs have names and dances, and dances have groups, then
dancegroupname belongs in another table, "dances".  

HTH.  

--jkl


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users