[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Paolo Bolzoni]

> I can't confirm that 100% off the top of my head but I'm uncoordinated
> enough to repeatedly confuse the bind and column value API calls and use
> 0-based indices for both and haven't noticed any really untoward behaviour
> (beyond my code not working and requiring fixing).

I know the feeling, I made it wrong so many times that I almost wanted
to "fix" the index in my thin C++ wrapper...

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Scott Hess]

On Tue, Mar 3, 2015 at 3:33 AM, Paolo Bolzoni
 wrote:
>> I can't confirm that 100% off the top of my head but I'm uncoordinated
>> enough to repeatedly confuse the bind and column value API calls and use
>> 0-based indices for both and haven't noticed any really untoward behaviour
>> (beyond my code not working and requiring fixing).
>
> I know the feeling, I made it wrong so many times that I almost wanted
> to "fix" the index in my thin C++ wrapper...

Instead, you could consider having your wrapper throw an assertion if
someone doesn't bind all parameters.  Asserting if someone doesn't
consume all result columns is a little more dubious, because you'd
probably need an API call to explicitly ignore a column, but I'd not
consider such an assertion insane, either.  For either case using
indices leads to kind of brittle code in any case, regardless of
whether it's 0-based or 1-based.

-scott

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Donald Shepherd]

On Tue, 3 Mar 2015 at 03:01 Simon Slavin  wrote:

>
> On 2 Mar 2015, at 12:23am, Jay Kreibich  wrote:
>
> > Every database I?ve every used starts SQL parameter indexes from 1.  I?m
> not sure it is part of the SQL standard, but it is more or less the defacto
> standard of SQL APIs, and might be considered part of the SQL language.
>
> I hope the SQLite library does something appropriate if you try to bind to
> parameter 0.  It would seem to be an excellent avenue for a security bug if
> nothing tests for it.
>

SQLITE_RANGE  is returned if the
parameter index is out of range.

https://www.sqlite.org/c3ref/bind_blob.html

I can't confirm that 100% off the top of my head but I'm uncoordinated
enough to repeatedly confuse the bind and column value API calls and use
0-based indices for both and haven't noticed any really untoward behaviour
(beyond my code not working and requiring fixing).

Regards,
Donald Shepherd.

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[R.Smith]

On 2015-03-02 04:51 PM, Paolo Bolzoni wrote:
> I kinda get your point, but still we are speaking of the C interface
> all the time. Sure in other contexts, like the TCL/SQL, the indexes
> start from 1. However the bind and the column function are both in the
> C interface.

It's when the C interface (or API) refers to items indexed by the SQL 
that's the odd child - it will cause much greater confusion if you bind 
in SQL ?1,?2,... and then the API binds or refers to those as 
col[0],col[1],... in stead of col[1],col[2],...  This is what I meant 
with the API following the TCL convention in certain cases. In the ones 
where there are no TCL/SQL obfuscation it indexes like any C API to 
0-based ranges.

Yeah - it's still not nice, but I'm not sure any other way would be better.

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Nesvarbu Ne]

Valodia valodia

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Simon Slavin]

On 2 Mar 2015, at 12:23am, Jay Kreibich  wrote:

> Every database I?ve every used starts SQL parameter indexes from 1.  I?m not 
> sure it is part of the SQL standard, but it is more or less the defacto 
> standard of SQL APIs, and might be considered part of the SQL language.

I hope the SQLite library does something appropriate if you try to bind to 
parameter 0.  It would seem to be an excellent avenue for a security bug if 
nothing tests for it.

Simon.

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Paolo Bolzoni]

I kinda get your point, but still we are speaking of the C interface
all the time. Sure in other contexts, like the TCL/SQL, the indexes
start from 1. However the bind and the column function are both in the
C interface.

Oh, well... I just have to wrap my mind around it. Definitely not a
real problem, I was merely curious.

On Mon, Mar 2, 2015 at 1:23 AM, Jay Kreibich  wrote:
>
> On Mar 1, 2015, at 5:33 PM, Richard Hipp  wrote:
>
>> On 3/1/15, Paolo Bolzoni  wrote:
>>> Dear everyone,
>>>
>>> I find strange and confusing that bind indexes start from 1 (docs in
>>> [1]) and instead column indexes start from 0 (doc in [2]). Is there
>>> any technical reason or it is just an unlucky legacy?
>>
>> Seems like there was a reason for this, 11 years ago when it went in,
>> but I cannot now call that reason to mind right this moment.  So lets
>> just call it unlucky legacy.
>
> Every database I?ve every used starts SQL parameter indexes from 1.  I?m not 
> sure it is part of the SQL standard, but it is more or less the defacto 
> standard of SQL APIs, and might be considered part of the SQL language.
>
> I assume column indexes start at 0 because of C.  Column indexes are used 
> within the context of the C language API, so it makes some sense to use C 
> conventions.
>
>
> I know they?re very easy to confuse (I still do it myself), but I can kind of 
> see why we ended up there, even if it isn?t a very strong reason.
>
>  -j
>
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it, but 
> showing it to the wrong people has the tendency to make them feel 
> uncomfortable." -- Angela Johnson
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Jay Kreibich]

On Mar 2, 2015, at 8:51 AM, Paolo Bolzoni  
wrote:

> I kinda get your point, but still we are speaking of the C interface
> all the time.

Not exactly.  SQL parameters are defined in SQL, and they?re part of the SQL 
language.  The parameter placement (and from that, their indexing and binding) 
can be considered an integral part of the SQL expression.  After all, in a 
given SQL statement, which parameter is ?parameter #3? shouldn?t really change, 
regardless of if you are using C, Python, Pascal, or some other language 
(including raw SQL at a prompt[1]).  This is more explicit when you use 
numbered or named parameters, but the same logic applies: the definition of the 
parameter enumeration and placement is more squarely in the domain of the SQL 
language, not the host access language.

[1] consider, for example, if the CLI tool sqlite3 allowed one to store 
statements and then execute them with a parameter list, perhaps taken from a 
CSV file or something.


Getting data back out, i.e. the column functions, on the other hand, is more of 
a language and database driver specific thing.  SQL doesn?t tend to enumerate 
columns, it just provides them in the order requested.  How a DB API chooses to 
return those values into a language specific construct is really up to the 
database driver and API designer.  Consider a language that has a native array 
or vector type, for example? it might make sense for an API to just return a 
whole row, rather than picking out column values one at a time, eliminating the 
whole need to index columns outside of the native language access syntax.  The 
sqlite3_exec() API kind of works that way, for example.  From that standpoint, 
it makes some amount of sense to use the C semantics, and index off zero.

Actually, I?m guessing the sqlite3_exec() function is the whole reason for 
column indexes starting at zero.  SQLite2 didn?t have prepared statements and 
ONLY had the sqlite_exec() API.  My guess is to keep the APIs as similar as 
possible, output columns are also indexed from zero.

Although? there are times when SQL does reference output columns.  For example, 
you can use a numerical reference to an output column in a GROUP BY or ORDER BY 
clause, and in those cases SQL is consistent and considers the first column to 
be ?1?, just as it does with parameters.  From that standpoint, it might make 
sense to say columns should be referenced from 1, regardless of the host 
language.

All of this is a little fuzzy and gray.  The fact they?re different is very 
unfortunate, and I think most of us would agree that if anything is ?broken?, 
it is the column access functions, not the parameters.  But regardless, it is 
what it is.

 -j



> Sure in other contexts, like the TCL/SQL, the indexes
> start from 1. However the bind and the column function are both in the
> C interface.
> 
> Oh, well... I just have to wrap my mind around it. Definitely not a
> real problem, I was merely curious.
> 
> On Mon, Mar 2, 2015 at 1:23 AM, Jay Kreibich  wrote:
>> 
>> On Mar 1, 2015, at 5:33 PM, Richard Hipp  wrote:
>> 
>>> On 3/1/15, Paolo Bolzoni  wrote:
 Dear everyone,
 
 I find strange and confusing that bind indexes start from 1 (docs in
 [1]) and instead column indexes start from 0 (doc in [2]). Is there
 any technical reason or it is just an unlucky legacy?
>>> 
>>> Seems like there was a reason for this, 11 years ago when it went in,
>>> but I cannot now call that reason to mind right this moment.  So lets
>>> just call it unlucky legacy.
>> 
>> Every database I?ve every used starts SQL parameter indexes from 1.  I?m not 
>> sure it is part of the SQL standard, but it is more or less the defacto 
>> standard of SQL APIs, and might be considered part of the SQL language.
>> 
>> I assume column indexes start at 0 because of C.  Column indexes are used 
>> within the context of the C language API, so it makes some sense to use C 
>> conventions.
>> 
>> 
>> I know they?re very easy to confuse (I still do it myself), but I can kind 
>> of see why we ended up there, even if it isn?t a very strong reason.
>> 
>> -j
>> 
>> 
>> --
>> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>> 
>> "Intelligence is like underwear: it is important that you have it, but 
>> showing it to the wrong people has the tendency to make them feel 
>> uncomfortable." -- Angela Johnson
>> 
>> 
>> 
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela 

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Igor Tandetnik]

On 3/2/2015 9:51 AM, Paolo Bolzoni wrote:
> I kinda get your point, but still we are speaking of the C interface
> all the time. Sure in other contexts, like the TCL/SQL, the indexes
> start from 1. However the bind and the column function are both in the
> C interface.

Not quite. There's ?N syntax you can use in your SQL statement to refer 
to a parameter number N. It may be confusing if ?1 had to be bound with 
index 1 in some contexts, and index 0 in others.
-- 
Igor Tandetnik

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Richard Hipp]

On 3/1/15, Paolo Bolzoni  wrote:
> Dear everyone,
>
> I find strange and confusing that bind indexes start from 1 (docs in
> [1]) and instead column indexes start from 0 (doc in [2]). Is there
> any technical reason or it is just an unlucky legacy?

Seems like there was a reason for this, 11 years ago when it went in,
but I cannot now call that reason to mind right this moment.  So lets
just call it unlucky legacy.

>
> Yours faithfully,
> Paolo
>
>
> [1] https://www.sqlite.org/c3ref/bind_blob.html
> [2] https://www.sqlite.org/c3ref/column_blob.html
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[R.Smith]

On 2015-03-01 04:41 PM, Paolo Bolzoni wrote:
> Dear everyone,
>
> I find strange and confusing that bind indexes start from 1 (docs in
> [1]) and instead column indexes start from 0 (doc in [2]). Is there
> any technical reason or it is just an unlucky legacy?

I'm going to venture a guess here and go with "Unlucky Legacy", but it 
is not really confusing I think. Please note that column indices start 
from 1 too when you refer a column or field in the TCL/SQL - a design 
that might stem from the SQL standard. I observed the same in MySQL or 
MSSQL etc. (refer ELEMENT() or similar functions) - Only the SQLite API 
refers to the first column/Index as 0, but the API is a programming API 
and not a TCL and follows the base way all programming constructs (at 
least those I am familiar with) such as array indexing, bytes, pointers 
etc. do it.  Where the API needs to refer to SQL/TCL constructs, such as 
in your example [1], the lines get blurry and the API adapts (which is 
far better than having the SQL adapt).

I'm sure if we had the opportunity to redesign the SQL standard - it 
might look somewhat different.

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Jay Kreibich]

On Mar 1, 2015, at 5:33 PM, Richard Hipp  wrote:

> On 3/1/15, Paolo Bolzoni  wrote:
>> Dear everyone,
>> 
>> I find strange and confusing that bind indexes start from 1 (docs in
>> [1]) and instead column indexes start from 0 (doc in [2]). Is there
>> any technical reason or it is just an unlucky legacy?
> 
> Seems like there was a reason for this, 11 years ago when it went in,
> but I cannot now call that reason to mind right this moment.  So lets
> just call it unlucky legacy.

Every database I?ve every used starts SQL parameter indexes from 1.  I?m not 
sure it is part of the SQL standard, but it is more or less the defacto 
standard of SQL APIs, and might be considered part of the SQL language.

I assume column indexes start at 0 because of C.  Column indexes are used 
within the context of the C language API, so it makes some sense to use C 
conventions.


I know they?re very easy to confuse (I still do it myself), but I can kind of 
see why we ended up there, even if it isn?t a very strong reason.

 -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Paolo Bolzoni]

Dear everyone,

I find strange and confusing that bind indexes start from 1 (docs in
[1]) and instead column indexes start from 0 (doc in [2]). Is there
any technical reason or it is just an unlucky legacy?

Yours faithfully,
Paolo


[1] https://www.sqlite.org/c3ref/bind_blob.html
[2] https://www.sqlite.org/c3ref/column_blob.html