Is this a bug?

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

Is this a bug?

Roman Fleysher
Dear SQLiters,


I can not figure out what I am doing wrong. In testing, I simplified to the following:

CREATE TABLE cosSquared(refVolume INT, vecVolume INT, cosSquared REAL);

SELECT refVolume, CAST(10*max(cosSquared) AS INT) FROM cosSquared GROUP BY refVolume;

refVolume   CAST(10*max(cosSquared) AS INT)
----------  -------------------------------
2           9
3           9
4           9
5           9
.............
31          9
32          9
33          9

That is, we see that for refVolumes between 2 and 33, the value of the CAST() is always 9. Thus, I expect the following statement to output the same list of refVolumes. But it does not:

SELECT DISTINCT refVolume FROM cosSquared
 WHERE (refVolume, CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM cosSquared);

refVolume
----------
2

What am I doing wrong? I am using version 3.16.

Thank you for your help,

Roman

_______________________________________________
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: Is this a bug?

Barry Smith
Your nested select statement wants to return many rows. Technically I think
this is illegal SQL (?), but it looks like SQLite is being lax about this
as usual and doing what it normally does in this sort of situation: picks a
row at random from the inner select.

Your statement is more or less equivalent to:
SELECT DISTINCT refVolume FROM cosSquered WHERE (refVolumn
CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM cosSquared LIMIT 1)

The statement that will output the same same list of refVolumes is:
SELECT DISTINCT refVolume FROM cosSquared WHERE CAST(10 * cosSquared AS
INT) = 9

On Tue, 18 Jun 2019 at 16:44, Roman Fleysher <[hidden email]>
wrote:

> Dear SQLiters,
>
>
> I can not figure out what I am doing wrong. In testing, I simplified to
> the following:
>
> CREATE TABLE cosSquared(refVolume INT, vecVolume INT, cosSquared REAL);
>
> SELECT refVolume, CAST(10*max(cosSquared) AS INT) FROM cosSquared GROUP BY
> refVolume;
>
> refVolume   CAST(10*max(cosSquared) AS INT)
> ----------  -------------------------------
> 2           9
> 3           9
> 4           9
> 5           9
> .............
> 31          9
> 32          9
> 33          9
>
> That is, we see that for refVolumes between 2 and 33, the value of the
> CAST() is always 9. Thus, I expect the following statement to output the
> same list of refVolumes. But it does not:
>
> SELECT DISTINCT refVolume FROM cosSquared
>  WHERE (refVolume, CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM
> cosSquared);
>
> refVolume
> ----------
> 2
>
> What am I doing wrong? I am using version 3.16.
>
> Thank you for your help,
>
> Roman
>
> _______________________________________________
> 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: Is this a bug?

Roman Fleysher
Oh, Thank you Barry.

I am glad it is not a bug. Bug is in my head.

Thank you,

Roman


________________________________________
From: sqlite-users [[hidden email]] on behalf of Barry [[hidden email]]
Sent: Tuesday, June 18, 2019 7:59 PM
To: SQLite mailing list
Subject: Re: [sqlite] Is this a bug?

Your nested select statement wants to return many rows. Technically I think
this is illegal SQL (?), but it looks like SQLite is being lax about this
as usual and doing what it normally does in this sort of situation: picks a
row at random from the inner select.

Your statement is more or less equivalent to:
SELECT DISTINCT refVolume FROM cosSquered WHERE (refVolumn
CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM cosSquared LIMIT 1)

The statement that will output the same same list of refVolumes is:
SELECT DISTINCT refVolume FROM cosSquared WHERE CAST(10 * cosSquared AS
INT) = 9

On Tue, 18 Jun 2019 at 16:44, Roman Fleysher <[hidden email]>
wrote:

> Dear SQLiters,
>
>
> I can not figure out what I am doing wrong. In testing, I simplified to
> the following:
>
> CREATE TABLE cosSquared(refVolume INT, vecVolume INT, cosSquared REAL);
>
> SELECT refVolume, CAST(10*max(cosSquared) AS INT) FROM cosSquared GROUP BY
> refVolume;
>
> refVolume   CAST(10*max(cosSquared) AS INT)
> ----------  -------------------------------
> 2           9
> 3           9
> 4           9
> 5           9
> .............
> 31          9
> 32          9
> 33          9
>
> That is, we see that for refVolumes between 2 and 33, the value of the
> CAST() is always 9. Thus, I expect the following statement to output the
> same list of refVolumes. But it does not:
>
> SELECT DISTINCT refVolume FROM cosSquared
>  WHERE (refVolume, CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM
> cosSquared);
>
> refVolume
> ----------
> 2
>
> What am I doing wrong? I am using version 3.16.
>
> Thank you for your help,
>
> Roman
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C0f35bb6726c3413690d208d6f4490695%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636964991807478228&amp;sdata=AJjpaa48jiKBcZI3eSsNRapqcBp%2FywsQ%2BWMoYCo1jOw%3D&amp;reserved=0
>
_______________________________________________
sqlite-users mailing list
[hidden email]
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C0f35bb6726c3413690d208d6f4490695%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636964991807478228&amp;sdata=AJjpaa48jiKBcZI3eSsNRapqcBp%2FywsQ%2BWMoYCo1jOw%3D&amp;reserved=0
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

SQLITE_LOCKED means "database table is locked"?

Carsten Müncheberg
I'm puzzled by the error strings for SQLITE_BUSY and SQLITE_LOCKED. The
documentation for both is very clear and precise, but these too strings
which are returned by sqlite3_errmsg() are misleading in my opinion,
especially "database table is locked". Is there really something like a
table lock? And do you think this is something worth improving?

  /* SQLITE_BUSY        */ "database is locked",
  /* SQLITE_LOCKED      */ "database table is locked",

Carsten
_______________________________________________
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_LOCKED means "database table is locked"?

Igor Tandetnik-2
On 6/19/2019 10:39 AM, Carsten Müncheberg wrote:
> Is there really something like a table lock?

Yes there is: https://sqlite.org/sharedcache.html
--
Igor Tandetnik


_______________________________________________
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_LOCKED means "database table is locked"?

Simon Slavin-3
In reply to this post by Carsten Müncheberg
On 19 Jun 2019, at 3:39pm, Carsten Müncheberg <[hidden email]> wrote:

>  /* SQLITE_BUSY        */ "database is locked",
>  /* SQLITE_LOCKED      */ "database table is locked",

With two different connections, either by the same program or different programs/computers, you will see SQLITE_BUSY .  SQLITE_BUSY is subject to handling with timeouts set within SQLite.

If your code simultaneously tries to use one database connection for two accesses (multi-threading), or two connections with shared cache, SQLite immediately returns a result of SQLITE_LOCKED without any attempt at pause and retry.

This is explained in the SQLite documentation, but the explanation is spread out over several different pages.
_______________________________________________
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_LOCKED means "database table is locked"?

Carsten Müncheberg
In reply to this post by Igor Tandetnik-2
Am 19.06.2019 um 16:47 schrieb Igor Tandetnik:
> On 6/19/2019 10:39 AM, Carsten Müncheberg wrote:
>> Is there really something like a table lock?
>
> Yes there is: https://sqlite.org/sharedcache.html

Thanks, I wasn't aware of that.

--
Carsten Müncheberg
Software Developer
Native Instruments GmbH

www.native-instruments.com

_______________________________________________
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_LOCKED means "database table is locked"?

Kevin Benson
On Wed, Jun 19, 2019 at 12:12 PM Carsten Müncheberg <
[hidden email]> wrote:

> Am 19.06.2019 um 16:47 schrieb Igor Tandetnik:
> > On 6/19/2019 10:39 AM, Carsten Müncheberg wrote:
> >> Is there really something like a table lock?
> >
> > Yes there is: https://sqlite.org/sharedcache.html
>
> Thanks, I wasn't aware of that.
>
>
 There's an inadvertent word omission in the second stanza under Table
Level Locking:

                 IS To read data a table, a connection must first obtain a
read-lock.
SHOULD BE To read data from a table, a connection must first obtain a
read-lock.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users