How do you test your sql-s? I write sql
from step to step from very simple to complex.
So I have select and finally I need table for
that. It was simple until now. Just put
create table as before select and you get
same result (same names) as in select.
Your example is not the same. If using expression
name can be different and can change. This is
true for every datatase I know. In Firebird If
I write select 1+2 from rdb$database I get
column name ADD. If I specify with AS clause
I get name I want.
This works in sqlite and is not a problem. If
I do not specify name it can change from version
to version and everbody knows it must be specified.
But in my example name is explicitly defined in
inner select and for select I get correct name
but for create table as I do not.
Please dont tell me again what is documented because
at first very little was and not as one would like but
I understand that. It is not the point in this example.
The point is different behavior or result or names
when you have select or create table as from that select.
It should give same result or is better just not using
create table as at all because you will never know
what you can expect. Maybe as you eplained some day
create table as will create name like 1, 2, 3, 4....
Long names sometimes are used for excel export or some
simple reports with just sql behind.
R Smith je 22.12.2017 ob 12:34 napisal:
On 2017/12/22 11:06 AM, Radovan Antloga wrote:
I'm using sqlite from version 2. I have 1000+
sql-s written already. I understand that
outermost statement must have AS clause as I
have explained below (select d as d ...).
Sqlite authors always say that new version can
not break sql-s for milions of users using
sqlite. But this change does.
It didn't break for millions of users, it only broke for the few who
relied on behaviour that is documented to not be reliable and that
might change in future.
Usually the Dev team is not in the habit of going around messing with
features just because it is documented as unspecified, but in this
case, it had to change to fix another real bug that had real-world
problematic implications.
Sorry but I did not ask how can I fix my sql-s.
I know I can create table and insert into.
Change select to select d as d is just ridiculous.
Rename column name into same name. What is that?
The first "d" tells SQLite where to find or how to evaluate the value,
the second "d" tells SQLite how to name the output of that SELECT. It
isn't any more ridiculous than SELECT (P+3) AS 'P+3' would be ridiculous.
This is not English101, this is Programming. Rules are rules, even
silly ones.
.../and later/...
>If I have long name then I would have
>select some_long_name_to_understand_column as
some_long_name_to_understand_column
>and if I have 20 columns like that. Just ridiculous or what.
>So once again I use AS clause but why I must use AS clause in
>outer statement if name is already defined in inner select.
Why would you use such a long silly name in the inner query KNOWING
that it is ignored (wrt. to output naming anyway)? That doesn't make
a sound argument.
The CREATE TABLE Evaluates the select with some special considerations
(as documented), nothing prescribes the resulting column name except
the final outer statement's AS clause (as documented). Internally
sqlite doesn't even see Aliases, it has numbers for columns. The outer
select is however the boss, when it says "this-column" AS 'this_name'
then sqlite (and any other DB engine) /has/ to abide by that (as
documented). The aliasing in the inner queries matter none other than
to allow reliable reference pointing between query levels (as
documented).
If it were undocumented behaviour you'd still not have a case but some
anger would be understandable, however, you've explicitly relied on
/documented-to-be-non-reliable/ behaviour, I still feel your pain, but
it still isn't a bug and calling it "ridiculous" still won't change that.
(Btw. - the devs might change the behaviour again, maybe even to your
liking if they feel merit and it doesn't break another behaviour, the
point remains that it must not be trusted until it becomes the
documented behaviour.)
I sincerely hope not too many of those 1000+ sql-s need fixing...
Good luck!
Ryan
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
