Re: [sqlite] Bug report

[Richard Hipp]

On 2/27/18, Alexander Ananin  wrote:
>
> I've found the strange behavior in the rtree.c file.

Are you trying to compile rtree.c separately, rather than using the
version that is bundled into the sqlite3.c amalgmation?  May I ask why
you want to do that?
-- 
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] Bug report

[Alexander Ananin]

Hi, Support,

I've found the strange behavior in the rtree.c file.
I think that in the  static int writeInt64(u8 *p, i64 i) function.

static int writeInt64(u8 *p, i64 i){
#if SQLITE_BYTEORDER==1234 && GCC_VERSION>=4003000
  i = (i64)__builtin_bswap64((u64)i);
  memcpy(p, &i, 8);
#elif SQLITE_BYTEORDER==1234 && MSVC_VERSION>=1300
  i = (i64)_byteswap_uint64((u64)i);
  memcpy(p, &i, 8);
#elif SQLITE_BYTEORDER==4321
  memcpy(p, &i, 8);
#else
  p[0] = (i>>56)&0xFF;
  p[1] = (i>>48)&0xFF;
  p[2] = (i>>40)&0xFF;
  p[3] = (i>>32)&0xFF;
  p[4] = (i>>24)&0xFF;
  p[5] = (i>>16)&0xFF;
  p[6] = (i>> 8)&0xFF;
  p[7] = (i>> 0)&0xFF;
#endif
  return 8;
}

If I build the SQLite with option 'Compile as C Code (/TC)' I've got the 
warning warning C4013: '_byteswap_uint64' undefined; assuming extern returning 
int

The highlighted line of function works incorrectly if #include  is 
missed. For example, it returns 0 value if i=1000 was passed into the function 
_byteswap_uint64.

Steps to reproduce:

  1.  Create console application in Visual Studio. The source code is below.
#include "stdafx.h"
//#include 

static __int64 writeInt64(char *p, __int64 i) {
   i = (__int64)_byteswap_uint64((unsigned __int64)i);
   memcpy(p, &i, 8);

   return 8;
}

int main()
{
   unsigned __int64 i = 1000;
   char buff[8] = { 0, };
   i = writeInt64(buff, i);
   return 0;
}


  1.  Build it with the option 'Compile as C Code (/TC)'
  2.  Set breakpoint on the i = (__int64)_byteswap_uint64((unsigned __int64)i);
  3.  Run it and call 'Go to Disassembled'
w/o #include 
i = (__int64)_byteswap_uint64((unsigned __int64)i);
008F17CE  mov eax,dword ptr [ebp+10h]
008F17D1  pusheax
008F17D2  mov ecx,dword ptr [i]
008F17D5  pushecx
008F17D6  call__byteswap_uint64 (08F1348h)
008F17DB  add esp,8
008F17DE  cdq
008F17DF  mov dword ptr [i],eax
008F17E2  mov dword ptr [ebp+10h],edx
with #include 
i = (__int64)_byteswap_uint64((unsigned __int64)i);

001217CE  mov eax,dword ptr [ebp+10h]
001217D1  pusheax
001217D2  mov ecx,dword ptr [i]
001217D5  pushecx
001217D6  call__byteswap_uint64 (0121348h)
001217DB  add esp,8
001217DE  mov dword ptr [i],eax
001217E1  mov dword ptr [ebp+10h],edx


Best Regards,
Alexander Ananin
Confidentiality Notice: This message (including attachments) is a private 
communication solely for use of the intended recipient(s). If you are not the 
intended recipient(s) or believe you received this message in error, notify the 
sender immediately and then delete this message. Any other use, retention, 
dissemination or copying is prohibited and may be a violation of law, including 
the Electronic Communication Privacy Act of 1986.   
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Missing several important tricks in partial indexes

[Cezary H. Noweta]

Hello,

On 2018-02-27 08:27, Shevek wrote:

If I create a partial index:



create table a (a0, a1)
create index idx on a (a0) where a1 is null;



3) The third issue is almost more major than the preceding two, and has 
nothing to do with covering, but with partial index selection AT ALL:


If we do a select "WHERE a1 IS ?" and pass a null for the bind-value of 
? it will never select the partial index, even though it's eligible.


This is due to a fact that a code for VDBE is generated before any 
parameter substitution takes place. Such code contains a parameter 
substitution command (OP_Variable). Said code does not contain two 
branches: (1) for param == NULL; (2) for param != NULL.


IMHO, a problem, you are describing, comes from a never-ending problem 
of SQL optimization: PREPARE vs. EXECUTION efficiency. It has been 
mentioned by DRH a few weeks ago, so it is considered by the team.


There are plenty of things, which can be done by an optimizer. What 
about INDEX... WHERE a1 > 0 AND a1 < 100 AND a1 <> 78, and SELECT... 
WHERE a1 > 10 AND a1 < 20 OR 10*a1 = 306? Certainly, I do not think that 
your example falls into ``making an algebra system'' category, and I am 
calm that the previously-mentioned by DRH fact will be considered by the 
team while implementing of such optimization.


Being or not NULL is one of the most frequent things which are occurring 
in db systems -- thus your wonderment seems to be quite justified. 
However it is desirable to take into account that every optimization 
speeds up an execution at the price of a preparation (as every index 
speeds up a query at the price of an update/insert) -- it is easy to say 
``why such and that index is not considered in such and that case?'' -- 
I have not seen ``my system could speed up due to an elimination of rare 
optimizations''. Speeding up is done (too frequently) by adding 
consecutive optimizations (on an engine's side) and indices (on an 
user's side) without a balance. (Still, abstracting from your particular 
justified example of being NULL.) Not every system operates on trillion 
records dbs taken from a heaven and executes fancy queries -- there are 
also systems which update/insert something and run many relatively 
simple queries on a relatively small data.


-- best regards

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

Re: [sqlite] [EXTERNAL] Re: Strange concatenation result

[x]

>What should substr('abcd',0,-2) return? 'cd' or just 'd'? Or maybe just an 
>empty string?

You’re starting at position 0 (in the imaginary infinite array) and taking the 
two characters to the left of that which are ‘\0’, so an empty string.

substr(‘abcd’,0,2) on the other hand should return array[0]+array[1] = ‘a’

Seems fairly consistent to me.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Strange concatenation result

[Bart Smissaert]

If anything I would prefer it to return an empty string as returning Null
could mess up the detection of the column data type.

RBS



On Tue, Feb 27, 2018 at 7:46 AM, Simon Slavin  wrote:

>
>
> On 27 Feb 2018, at 7:01am, Hick Gunter  wrote:
>
> > What should substr('abcd',0,-2) return? 'cd' or just 'd'? Or maybe just
> an empty string?
>
> NULL
>
> Simon.
> ___
> 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] Strange concatenation result

[x]

That’s the way I see it Jean-Luc.

From: Jean-Luc Hainaut
Sent: 27 February 2018 09:56
To: SQLite mailing list
Subject: Re: [sqlite] Strange concatenation result


Let me suggest an interpretation that seems to comply with the current
implementation of "substr".

1. String X is stored in a (ficticious) infinite array, the cells of
which are indexed -*, ..., -2, -1, 0, 1, 2,.., +*.
2. String X is stored from cell 1 upward.
3. String 'abcd' is stored in cells [1,4]. Cells [-*,0] and [5,+*] are
empty.
4. Parameters X and Y specify a slice of the array.
5. Parameter Y, as described in the documentation, denotes any cell of
the array, even if it doesn't contain a character of X.
6. Parameter Z, as described in the documentation, denotes any slice of
the array, that may (but need not) include characters of X.
7. Function "substr" returns the contents of the non empty cells of this
slice.

Some examples:

select substr('abcd',1,2);   --> slice [1,2]
select substr('abcd',0,2);   --> slice [0,2]
select substr('abcd',0,-2);  --> slice [-2,-1]
select substr('abcd',5,-3);  --> slice [2,4]
select substr('abcd',5,2);   --> slice [5,6]
select substr('abcd',-3,3);  --> slice [2,4]
select substr('abcd',-4,3);  --> slice [1,3]
select substr('abcd',-5,3);  --> slice [0,2]
select substr('abcd',-6,3);  --> slice [-1,1]
select substr('abcd',-7,3);  --> slice [-2,0]
select substr('abcd',2,0);   --> empty slice
select substr('abcd',-5,0);  --> empty slice

+-+
| substr('abcd',1,2)  |
+-+
| ab  |
+-+
+-+
| substr('abcd',0,2)  |
+-+
| a   |
+-+
+-+
| substr('abcd',0,-2) |
+-+
| |
+-+
+-+
| substr('abcd',5,-3) |
+-+
| bcd |
+-+
+-+
| substr('abcd',5,2)  |
+-+
| |
+-+
+-+
| substr('abcd',-3,3) |
+-+
| bcd |
+-+
+-+
| substr('abcd',-4,3) |
+-+
| abc |
+-+
+-+
| substr('abcd',-5,3) |
+-+
| ab  |
+-+
+-+
| substr('abcd',-6,3) |
+-+
| a   |
+-+
+-+
| substr('abcd',-7,3) |
+-+
| |
+-+
+-+
| substr('abcd',2,0)  |
+-+
| |
+-+
+-+
| substr('abcd',-5,0) |
+-+
| |
+-+

___
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] Strange concatenation result

[Jean-Luc Hainaut]

Let me suggest an interpretation that seems to comply with the current 
implementation of "substr".


1. String X is stored in a (ficticious) infinite array, the cells of 
which are indexed -*, ..., -2, -1, 0, 1, 2,.., +*.

2. String X is stored from cell 1 upward.
3. String 'abcd' is stored in cells [1,4]. Cells [-*,0] and [5,+*] are 
empty.

4. Parameters X and Y specify a slice of the array.
5. Parameter Y, as described in the documentation, denotes any cell of 
the array, even if it doesn't contain a character of X.
6. Parameter Z, as described in the documentation, denotes any slice of 
the array, that may (but need not) include characters of X.
7. Function "substr" returns the contents of the non empty cells of this 
slice.


Some examples:

select substr('abcd',1,2);   --> slice [1,2]
select substr('abcd',0,2);   --> slice [0,2]
select substr('abcd',0,-2);  --> slice [-2,-1]
select substr('abcd',5,-3);  --> slice [2,4]
select substr('abcd',5,2);   --> slice [5,6]
select substr('abcd',-3,3);  --> slice [2,4]
select substr('abcd',-4,3);  --> slice [1,3]
select substr('abcd',-5,3);  --> slice [0,2]
select substr('abcd',-6,3);  --> slice [-1,1]
select substr('abcd',-7,3);  --> slice [-2,0]
select substr('abcd',2,0);   --> empty slice
select substr('abcd',-5,0);  --> empty slice

+-+
| substr('abcd',1,2)  |
+-+
| ab  |
+-+
+-+
| substr('abcd',0,2)  |
+-+
| a   |
+-+
+-+
| substr('abcd',0,-2) |
+-+
| |
+-+
+-+
| substr('abcd',5,-3) |
+-+
| bcd |
+-+
+-+
| substr('abcd',5,2)  |
+-+
| |
+-+
+-+
| substr('abcd',-3,3) |
+-+
| bcd |
+-+
+-+
| substr('abcd',-4,3) |
+-+
| abc |
+-+
+-+
| substr('abcd',-5,3) |
+-+
| ab  |
+-+
+-+
| substr('abcd',-6,3) |
+-+
| a   |
+-+
+-+
| substr('abcd',-7,3) |
+-+
| |
+-+
+-+
| substr('abcd',2,0)  |
+-+
| |
+-+
+-+
| substr('abcd',-5,0) |
+-+
| |
+-+

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

Re: [sqlite] Strange concatenation result

[Cezary H. Noweta]

Hello,

On 2018-02-27 08:46, Simon Slavin wrote:

What should substr('abcd',0,-2) return? 'cd' or just 'd'? Or maybe just an 
empty string?



NULL


Why?

-- best regards

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