Help with row values

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Help with row values

curmudgeon
sqlite> create table t(a integer primary key);
sqlite> insert into t values (1), (3), (5);
sqlite> select count(*) from t where a>0;
3
sqlite> select count(*) from t where (a,1)>(0,0);
0

Surely it should be 3 in both cases?????

If t is created as ‘create table t(a)’ both return 3.

Tried it in 3.21 and 3.22.
_______________________________________________
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: Help with row values

Simon Slavin-3
On 13 Feb 2018, at 5:32pm, x <[hidden email]> wrote:

> Surely it should be 3 in both cases?????

I agree.  Here's verification with a version number:

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(a integer primary key);
sqlite> insert into t values (1), (3), (5);
sqlite> select count(*) from t where a>0;
3
sqlite> select count(*) from t where (a,1)>(0,0);
0

        but also

sqlite> select * from t WHERE (a,0)>(0,0);
sqlite> SELECT (3,0) > (0,0);
1
sqlite> select a,typeof(a) FROM t;
1|integer
3|integer
5|integer
sqlite> select 3,typeof(3);
3|integer
sqlite>

weird.

Simon.
_______________________________________________
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: Help with row values

Richard Hipp-3
On 2/13/18, Simon Slavin <[hidden email]> wrote:
> On 13 Feb 2018, at 5:32pm, x <[hidden email]> wrote:
>
>> Surely it should be 3 in both cases?????
>
> I agree.  Here's verification with a version number:

https://www.sqlite.org/src/tktview/f484b65f3d623059
--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Help with row values

Richard Hipp-3
Now fixed on trunk.  Thanks for the bug report.

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Help with row values

curmudgeon
Thanks Richard.

From: Richard Hipp<mailto:[hidden email]>
Sent: 13 February 2018 19:17
To: SQLite mailing list<mailto:[hidden email]>
Subject: Re: [sqlite] Help with row values

Now fixed on trunk.  Thanks for the bug report.

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Help with row values

Dominique Devienne
In reply to this post by Richard Hipp-3
On Tue, Feb 13, 2018 at 7:09 PM, Richard Hipp <[hidden email]> wrote:

> On 2/13/18, Simon Slavin <[hidden email]> wrote:
> > On 13 Feb 2018, at 5:32pm, x <[hidden email]> wrote:
> >
> >> Surely it should be 3 in both cases?????
> >
> > I agree.  Here's verification with a version number:
>
> https://www.sqlite.org/src/tktview/f484b65f3d623059


in https://www.sqlite.org/src/info/f3112e67cdb27c1a
to fix above ticket, I see queries with order by +a,
but in https://www.sqlite.org/lang_select.html#orderby
I don't see any obvious mention about that +.

Could someone please explain what this + is? Thanks, --DD
_______________________________________________
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: Help with row values

Clemens Ladisch
Dominique Devienne wrote:
> in https://www.sqlite.org/src/info/f3112e67cdb27c1a
> to fix above ticket, I see queries with order by +a,
> but in https://www.sqlite.org/lang_select.html#orderby
> I don't see any obvious mention about that +.

"Unary plus" does not change the value:

  sqlite> select 1;
  1
  sqlite> select -1;
  -1
  sqlite> select +1;
  1

It's used to change the expression from a column reference to something
that is computed, which means that SQLite cannot use an index for it
(unless you created an expression index on +a):
<http://www.sqlite.org/optoverview.html#uplus>


Regards,
Clemens
_______________________________________________
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: Help with row values

Dominique Devienne
On Wed, Feb 14, 2018 at 8:44 AM, Clemens Ladisch <[hidden email]> wrote:

> Dominique Devienne wrote:
> > in https://www.sqlite.org/src/info/f3112e67cdb27c1a
> > to fix above ticket, I see queries with order by +a,
> > but in https://www.sqlite.org/lang_select.html#orderby
> > I don't see any obvious mention about that +.
>
> "Unary plus" does not change the value:
>
>   sqlite> select 1;
>   1
>   sqlite> select -1;
>   -1
>   sqlite> select +1;
>   1
>
> It's used to change the expression from a column reference to something
> that is computed, which means that SQLite cannot use an index for it
> (unless you created an expression index on +a):
> <http://www.sqlite.org/optoverview.html#uplus>


Thanks. That's interesting. But then, why use it in this context?
Why DRH wants to purposely bypass the index in this case?
How is that relevant to testing tuple / row-values comparisons? --DD
_______________________________________________
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: Help with row values

Simon Slavin-3


On 14 Feb 2018, at 7:50am, Dominique Devienne <[hidden email]> wrote:

> Thanks. That's interesting. But then, why use it in this context?
> Why DRH wants to purposely bypass the index in this case?
> How is that relevant to testing tuple / row-values comparisons? --DD

In the original example, column a is the PRIMARY KEY.  SQLite automatically makes an index for the PRIMARY KEY.  So SQLIte might use that index if there's a WHERE clause on a.  In the case of the ticket DRH wants to avoid this, since the bug shows up only when an index is not used.

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