Hi Jay,
I understand that it is not possible to define what the correct output is.
But what I am asking for is a simple convention which doesn't behave more badly
in general (i. e. it's still database behavior) but much better in my case and
most likely for many cases of other users too.
The convention is to use the dequoted column identifier as column name in case
the column expression translates to a single identifier.
You have to dequote the identifier anyway to find the column in the subselect
to which this expression refers to.
So all I'm asking for is to change the default database behavior to yield more
obvious or consistent default column names. In case the database behavior
doesn't fit, one has to use the AS clause anyway.
For the below mentioned join, sqlite3 currently behaves like that:
select [x].[a], [y].[a] from x join x y on x.a = y.a;
a|a
1|1
Hence, it simply uses the column names. And the next statement does that too:
select [x].[a] from x;
a
1
So in my opinion the default behavior of the database should be to yield the
same column name even for this statement:
select [x].[a] from (select a from x) x;
But it currently returns:
[x].[a]
1
I'd like to create a patch which changes the behavior in that way, but I'm not
that used to the sqlite3 internals. From a quick glance at the source, I think
it has something to do with TK_COLUMN and TK_VARIABLE. It would be nice if you
could give me a pointer where to place the change in the source code.
Bye.
--
Reinhard Nißl
-----Ursprüngliche Nachricht-----
Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
Im Auftrag von Jay A. Kreibich
Gesendet: Donnerstag, 10. Februar 2011 17:05
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of
column names for certain statements
On Thu, Feb 10, 2011 at 09:35:04AM -0600, Puneet Kishor scratched on the wall:
> that code would be very complex to cover all the possible cases. The
> simplest solution is to depend upon AS aliasing
To be clear, that's not an excuse the development team is using
to avoid writing a hard bit of code. The SQL standard leaves column
names undefined in the absence of a column alias (e.g. "AS" phrase).
In other words, the database is free to do its best, but it is really
up to the developer to strictly define names, via AS, if the names are
relevant (i.e. used in code). (The wisdom of using names as column
identifiers is a whole different argument.)
Consider your own example. Is "a" really the correct output? What
about "x.a"? Or "main.x.a"? If you feel the need to quote a column
name, such as "[a]", why shouldn't the database feel it is proper to
quote it back at you?
What if there is both an "x.a" and a "y.a" column from an "x JOIN y"
operation? Should the columns be "a" and "a", or should they
promoted to be more specific? What about a sub-select that has an
"a AS a" output specification, where it is an alias that just
happens to be the same as a column, but it is no longer a
source-column reference? What about "a+1 AS a" where any
source-column association (and therefore table and database
association) is specifically broken?
For almost any naming scheme one can come up with, it is fairly
easy to find odd edge cases that add dozens of extra "but",
"unless", "except" rules to your naming convention. Your rule set
quickly becomes so huge and fragile, you might as well treat the
naming convention as undefined. And, of course, the naming rules
would be product-specific (Some DBs have schema name-spaces, some
don't. Some have table-spaces, some don't. Some can access multiple
databases, some can't.), meaning every database is going to do it
differently anyways-- which is exactly why it isn't in the standard.
-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@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
