Re: [sqlite] Help with row values

[Simon Slavin]

On 14 Feb 2018, at 7:50am, Dominique Devienne  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with row values

[Dominique Devienne]

On Wed, Feb 14, 2018 at 8:44 AM, Clemens Ladisch  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):
> 


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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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):



Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with row values

[Dominique Devienne]

On Tue, Feb 13, 2018 at 7:09 PM, Richard Hipp  wrote:

> On 2/13/18, Simon Slavin  wrote:
> > On 13 Feb 2018, at 5:32pm, x  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with row values

[x]

Thanks Richard.

From: Richard Hipp<mailto:d...@sqlite.org>
Sent: 13 February 2018 19:17
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Help with row values

Now fixed on trunk.  Thanks for the bug report.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with row values

[Richard Hipp]

Now fixed on trunk.  Thanks for the bug report.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with row values

[Richard Hipp]

On 2/13/18, Simon Slavin  wrote:
> On 13 Feb 2018, at 5:32pm, x  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with row values

[Simon Slavin]

On 13 Feb 2018, at 5:32pm, x  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help with row values

[x]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users