Re: [sqlite] Select statements returned column names
At 04:31 PM 2/20/04 +0100, eno wrote: I don't see the compatibility point in returning column names as either fully qualified or not. The effort to support both automatically by that piece of software which is parsing a DB engine's return values is quite small: it only has to accept additional '.' chars inside a column name. That code is still generic enough to work with all DBs. I think the point is that many would prefer compatibility as much as possible. The specific question was about the select statement, but in principal, many of us would like to see some consistency across the various SQL servers. It makes life a lot easier. Regarding compatibility: there are more and harder to circumvent topics here. Think AUTO_INCREMENT, for example: Absolutely true, but that's changing the subject. Has nothing to do with the original question. Scott Utilities for POPFile, the OpenSource Mail Classifier http://www.geocities.com/Helphand1/popfile.htm - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Select statements returned column names
Richard, can't you make it user configurable? I was serious about the PRAGMA. Would be nice if it could be persistent as well as single instance. With user configuration many could configure around the issue on older code and take the default (is it not an SQL standard? I've never had occasion until now to look.) with new stuff. I've been mentally reviewing my twenty plus year history with SQL and I can never recall using any database that appended the table designator on the fields returned. SQLite is one of the sweetest little databases I have ever used, it is a pity that there are a few innocent seeming issues that can prove to be serious roadblocks. I think based on the product of your work so far, you will certainly find a workable answer for this one, and the storage issue related to database "cleansing." Fred -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Thursday, February 19, 2004 7:12 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Select statements returned column names Gerard Samuel wrote: > > If I execute an sql select like -> > SELECT f.id, f.foo FROM table f; > The returned data is -> > f.id f.foo > 1 hello > 2 world > > Instead of the normal (as in other DBs I've used) > idfoo > 1 hello > 2 world > > Is this the correct/expected behaviour of sqlite? You can always specify your own column names using an AS clause, of source: SELECT f.id AS one, f.foo AS two FROM table f; one two 1 hello 2 world SQLite does attach "different" names to the columns than other database engines. This has been a persistent source of complaint. The problem comes up on joins more than anyplace else. Question to all: If I modified SQLite to use the same column naming rules as (say) PostgreSQL, how much existing code would it break? Is this something that should be done, even though it is a (slightly) incompatible change? -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Select statements returned column names
> Question to all: If I modified SQLite to use the > same column naming rules as (say) PostgreSQL, how > much existing code would it break? Is this something > that should be done, even though it is a (slightly) > incompatible change? > > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 I also vote for compatibility with other databases. Users, especially new users, are more likely to be happy with SQLite if it runs their existing queries unchanged. SQLite is being adopted by ever larger numbers of new users, and this will probably continue into the foreseeable future. Aggravating all these new users to avoid annoying the much smaller number of existing users who rely on the current non-standard behavior doesn't seem like a good tradeoff. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Select statements returned column names
On Thursday 19 February 2004 08:12 am, D. Richard Hipp wrote: > Gerard Samuel wrote: > > If I execute an sql select like -> > > SELECT f.id, f.foo FROM table f; > > The returned data is -> > > f.idf.foo > > 1 hello > > 2 world > > > > Instead of the normal (as in other DBs I've used) > > id foo > > 1 hello > > 2 world > > > > Is this the correct/expected behaviour of sqlite? > > You can always specify your own column names using > an AS clause, of source: > True. I even stated in an earlier email that your suggestion would be a workaround. But its one thing if a person is just working with SQLite. And its another when a person is working with other databases (like myself). For what Im doing, if sql isn't cross database compatible, I have a problem. Granted the above suggestion would work on other databases, I dont know, to me why should a "normal" database use aliases when it doesn't have to. Fortunately, I was able to modify my code where it strips out the table prefixes from the returned results. It's a slight performance hit, but hey, it does the job. > SQLite does attach "different" names to the columns > than other database engines. This has been a > persistent source of complaint. The problem comes > up on joins more than anyplace else. > > Question to all: If I modified SQLite to use the > same column naming rules as (say) PostgreSQL, how > much existing code would it break? Is this something > that should be done, even though it is a (slightly) > incompatible change? Yes join statements totally break. I for one vote for sql compatibilty. Thanks for replying.. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Select statements returned column names
> -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: 19 February 2004 13:12 > To: [EMAIL PROTECTED] > Subject: Re: [sqlite] Select statements returned column names > SQLite does attach "different" names to the columns than > other database engines. This has been a persistent source of > complaint. The problem comes up on joins more than anyplace else. > > Question to all: If I modified SQLite to use the same column > naming rules as (say) PostgreSQL, how much existing code > would it break? Is this something that should be done, even > though it is a (slightly) incompatible change? I actually prefer it with the table name prefixes. So one vote for leaving it as-is. Tim - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Select statements returned column names
On Wednesday 18 February 2004 12:19 pm, Fred Williams wrote: > Just did some testing on the "other" databases I have available. This > "feature" of SQLite does seem to be unique. I am early in the learning > curve on SQLite, and had not found this particular "enhancement" as yet. > Nice to know, and avoid! > > Looks like to me it is time for a new PRAGMA! > > I am not well versed on the SQL '9x specifications, so don't know if SQLite > is breaking spec's or just breaking "convention." Either way I think it > deserves consideration, as it directly impacts cross platform (i.e. > database engines) environments. Something I am painfully familiar with. > Just never got burned by this particular situation in the past, so was > blissfully unaware of the pending potential "all nighters," BUMMER! > > Fred > In my journey with databases, (mysql, postgresql, sql 2000, ibm db2, and sqlite), sqlite is the only one that behaves like this. IMHO, its not a "feature" but more of a "catch all" regardless of whether the results contain same column names. Like using an axe to split match sticks!! Lets hope it gets reverted to how it was, or some kind of switch is introduced... - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Select statements returned column names
Just did some testing on the "other" databases I have available. This "feature" of SQLite does seem to be unique. I am early in the learning curve on SQLite, and had not found this particular "enhancement" as yet. Nice to know, and avoid! Looks like to me it is time for a new PRAGMA! I am not well versed on the SQL '9x specifications, so don't know if SQLite is breaking spec's or just breaking "convention." Either way I think it deserves consideration, as it directly impacts cross platform (i.e. database engines) environments. Something I am painfully familiar with. Just never got burned by this particular situation in the past, so was blissfully unaware of the pending potential "all nighters," BUMMER! Fred -Original Message- From: Gerard Samuel [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 18, 2004 10:47 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [sqlite] Select statements returned column names On Wednesday 18 February 2004 11:23 am, Gerard Samuel wrote: > On Wednesday 18 February 2004 10:58 am, Ilia Alshanetsky wrote: > > Yes, this is the correct behavior, without the table 'prefix' if you have > > common column names in the result set the data would lost. > > True about common column names. But isn't that where aliases come in??? > E.g. select a.foo as foo1, b.foo as foo2 > Another question. Can this behaviour be turned off, reverting sqlite, making it more compatible to other databases such as mysql, pgsql, mssql, etc ... - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Select statements returned column names
On Wednesday 18 February 2004 11:23 am, Gerard Samuel wrote: > On Wednesday 18 February 2004 10:58 am, Ilia Alshanetsky wrote: > > Yes, this is the correct behavior, without the table 'prefix' if you have > > common column names in the result set the data would lost. > > True about common column names. But isn't that where aliases come in??? > E.g. select a.foo as foo1, b.foo as foo2 > Another question. Can this behaviour be turned off, reverting sqlite, making it more compatible to other databases such as mysql, pgsql, mssql, etc Thanks - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Select statements returned column names
On Wednesday 18 February 2004 10:58 am, Ilia Alshanetsky wrote: > Yes, this is the correct behavior, without the table 'prefix' if you have > common column names in the result set the data would lost. > True about common column names. But isn't that where aliases come in??? E.g. select a.foo as foo1, b.foo as foo2 The last time I used sqlite (2.8.3), my query below used to work. I don't understand why the decision was made to change it, to something that is incompatible to sql statements from mysql, postgresql, mssql, ibm db2 (the ones Im using so far). While its true that I can do -> SELECT f.id as id, f.foo as foo FROM table f; OR remove the table 'prefix' from the returned column names via php, to overcome this, but to me that sucks because sqlite takes away the choice from the sql author, on whether aliases should be used on a select statement. Is there a better reason for this sqlite behaviour, as your explanation, doesn't really justify (at least in my mind) why sqlite is like this now Thanks for your input > On February 18, 2004 10:43 am, Gerard Samuel wrote: > > Im currently interfacing with sqlite via php 4.3.5-RC2. > > The latest version of sqlite, distributed by php snaps > > (http://snaps.php.net/win32/PECL_STABLE/) is 2.8.11. > > Im currently experiencing behaviour with sqlite that I didn't experience > > when I was using version 2.8.3 several months ago. > > If I have a table like > > id foo > > 1 hello > > 2 world > > > > If I execute an sql select like -> > > SELECT f.id, f.foo FROM table f; > > The returned data is -> > > f.idf.foo > > 1 hello > > 2 world > > > > Instead of the normal (as in other DBs I've used) > > id foo > > 1 hello > > 2 world > > > > Is this the correct/expected behaviour of sqlite? > > Thanks for any input you may provide... - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
