On Mar 2, 2015, at 8:51 AM, Paolo Bolzoni <paolo.bolzoni.brown at gmail.com> 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 <jay at kreibi.ch> wrote: >> >> On Mar 1, 2015, at 5:33 PM, Richard Hipp <drh at sqlite.org> wrote: >> >>> On 3/1/15, Paolo Bolzoni <paolo.bolzoni.brown at gmail.com> 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 Johnson
