Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0
But column name is specified in inner select with AS clause. It can be used in outer select and work as expected. select d from (select c AS d from (select a AS c from test)); I hope you did read my example. You can see explicitly defined name in select a AS c from test then again explicitly defined new name select c AS d from and finally outer select can select only name d or what you think? select statement gives correct answer or column name but when you put CREATE TABLE AS in front of this statement you get a as column name not d in created table. Do you understand that? So outer select must have select d AS d so column must be renamed into it self to get proper name?! 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. Hick Gunter je 22.12.2017 ob 10:57 napisal: The problem lies in your reliance on unspecified behaviour. Unspecified behaviour is allowed to change. I am sure you have read (and ignored) the following guarantee taken from http://sqlite.org/c3ref/column_name.html: "Column Names In A Result Set ... The name of a result column is the value of the "AS" clause for that column, if there is an AS clause. If there is no AS clause then the name of the column is unspecified and may change from one release of SQLite to the next." Here is another common trap to fall into, taken from http://sqlite.org/lang_select.html : " The ORDER BY clause If a SELECT statement that returns more than one row does not have an ORDER BY clause, the order in which the rows are returned is undefined." The order in which rows are returned is determined by the selected query plan. This may change after running ANALYZE, adding or deleting indices, or when changes are made to the Query Planner itself. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Radovan Antloga Gesendet: Freitag, 22. Dezember 2017 10:06 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0 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. 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? Hick Gunter je 22.12.2017 ob 7:55 napisal: The behaviour does not need to match what you think of as consistent. The only way to force a certain column name is with the AS clause *on the outermost statement*. Otherwise, the column name is implementation defined and may change between releases. You should not be relying on column names other than those you explicitly set using the AS clause. This is a common mistake. If you really need the column names, then just CREATE TABLE first (this gives you defined column names AND declared data types) and the INSERT INTO ... SELECT later (which ignores the generated column names from the select statement). -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Radovan Antloga Gesendet: Donnerstag, 21. Dezember 2017 16:35 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0 Behaviour is not consistent when using create table as or just select statement. Try this simple test. create table test(a int, b int); insert into test values (1, 1); select d from (select c as d from (select a as c from test)); you get column name d as expected but when you have create table as statement create table test2 as select d from (select c as d from (select a as c from test)); you get table test2 with column name a. If you change to this create table test2 as select d as d from (select c as d from (select a as c from test)); you will get name correct. I think it should be the same as when just using select statement. Best Regards Radovan select a from (select b from (select c from test))) Richard Hipp je 21.12.2017 ob 14:52 napisal: The behavior change is a bug fix. See http://sqlite.org/src/info/de3403bf5ae for details. On 12/21/17, Radovan Antloga <radovan.antl...@siol.net> wrote: I have table (create statement): CREATE TABLE SOPP1 ( STAT varchar(1) collate systemnocase, RID varchar(2) collate systemnocase, VP integer, BLANK varchar(6) collate systemnocase, NAZIV varchar(24) collate systemn
Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0
The problem lies in your reliance on unspecified behaviour. Unspecified behaviour is allowed to change. I am sure you have read (and ignored) the following guarantee taken from http://sqlite.org/c3ref/column_name.html: "Column Names In A Result Set ... The name of a result column is the value of the "AS" clause for that column, if there is an AS clause. If there is no AS clause then the name of the column is unspecified and may change from one release of SQLite to the next." Here is another common trap to fall into, taken from http://sqlite.org/lang_select.html : " The ORDER BY clause If a SELECT statement that returns more than one row does not have an ORDER BY clause, the order in which the rows are returned is undefined." The order in which rows are returned is determined by the selected query plan. This may change after running ANALYZE, adding or deleting indices, or when changes are made to the Query Planner itself. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Radovan Antloga Gesendet: Freitag, 22. Dezember 2017 10:06 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0 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. 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? Hick Gunter je 22.12.2017 ob 7:55 napisal: > The behaviour does not need to match what you think of as consistent. > > The only way to force a certain column name is with the AS clause *on the > outermost statement*. Otherwise, the column name is implementation defined > and may change between releases. You should not be relying on column names > other than those you explicitly set using the AS clause. This is a common > mistake. > > If you really need the column names, then just CREATE TABLE first (this gives > you defined column names AND declared data types) and the INSERT INTO ... > SELECT later (which ignores the generated column names from the select > statement). > > -Ursprüngliche Nachricht- > Von: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von > Radovan Antloga > Gesendet: Donnerstag, 21. Dezember 2017 16:35 > An: sqlite-users@mailinglists.sqlite.org > Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field > alias in 3.21.0 > > Behaviour is not consistent when using create table as or just select > statement. > Try this simple test. > > create table test(a int, b int); > insert into test values (1, 1); > > select d from (select c as d from (select a as c from test)); > > you get column name d as expected > but when you have create table as statement > > create table test2 as > select d from (select c as d from (select a as c from test)); > > you get table test2 with column name a. > > If you change to this > > create table test2 as > select d as d from (select c as d from (select a as c from test)); > > you will get name correct. I think it should be the same as when just using > select statement. > > Best Regards > Radovan > > > select a from (select b from (select c from test))) > > Richard Hipp je 21.12.2017 ob 14:52 napisal: >> The behavior change is a bug fix. See >> http://sqlite.org/src/info/de3403bf5ae for details. >> >> On 12/21/17, Radovan Antloga <radovan.antl...@siol.net> wrote: >>> I have table (create statement): >>> >>> CREATE TABLE SOPP1 ( >>> STAT varchar(1) collate systemnocase, >>> RID varchar(2) collate systemnocase, >>> VP integer, >>> BLANK varchar(6) collate systemnocase, >>> NAZIV varchar(24) collate systemnocase, >>> KN varchar(12) collate systemnocase, >>> A varchar(1) collate systemnocase, >>> B varchar(1) collate systemnocase, >>> RACUN varchar(1) collate systemnocase, >>> URE varchar(1) collate systemnocase, >>> ZN varchar(1) collate systemnocase, >>> TOCKE varchar(1) collate systemnocase, >>> PRC varchar(1) collate systemnocase, >>> UP varchar(1) collate systemnocase, >>> IZPIS varchar(1) collate systemnocase, >>> D varchar(1) collate systemnocase, >>> F2U varchar(1) collate s
Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0
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.
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?
Hick Gunter je 22.12.2017 ob 7:55 napisal:
The behaviour does not need to match what you think of as consistent.
The only way to force a certain column name is with the AS clause *on the
outermost statement*. Otherwise, the column name is implementation defined and
may change between releases. You should not be relying on column names other
than those you explicitly set using the AS clause. This is a common mistake.
If you really need the column names, then just CREATE TABLE first (this gives
you defined column names AND declared data types) and the INSERT INTO ...
SELECT later (which ignores the generated column names from the select
statement).
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Radovan Antloga
Gesendet: Donnerstag, 21. Dezember 2017 16:35
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field alias in
3.21.0
Behaviour is not consistent when using
create table as
or just select statement.
Try this simple test.
create table test(a int, b int);
insert into test values (1, 1);
select d from (select c as d from (select a as c from test));
you get column name d as expected
but when you have create table as statement
create table test2 as
select d from (select c as d from (select a as c from test));
you get table test2 with column name a.
If you change to this
create table test2 as
select d as d from (select c as d from (select a as c from test));
you will get name correct. I think it should be the same as when just using
select statement.
Best Regards
Radovan
select a from (select b from (select c from test)))
Richard Hipp je 21.12.2017 ob 14:52 napisal:
The behavior change is a bug fix. See
http://sqlite.org/src/info/de3403bf5ae for details.
On 12/21/17, Radovan Antloga <radovan.antl...@siol.net> wrote:
I have table (create statement):
CREATE TABLE SOPP1 (
STAT varchar(1) collate systemnocase,
RID varchar(2) collate systemnocase,
VP integer,
BLANK varchar(6) collate systemnocase,
NAZIV varchar(24) collate systemnocase,
KN varchar(12) collate systemnocase,
A varchar(1) collate systemnocase,
B varchar(1) collate systemnocase,
RACUN varchar(1) collate systemnocase,
URE varchar(1) collate systemnocase,
ZN varchar(1) collate systemnocase,
TOCKE varchar(1) collate systemnocase,
PRC varchar(1) collate systemnocase,
UP varchar(1) collate systemnocase,
IZPIS varchar(1) collate systemnocase,
D varchar(1) collate systemnocase,
F2U varchar(1) collate systemnocase,
F2O varchar(1) collate systemnocase,
F2T varchar(1) collate systemnocase,
F2Z varchar(1) collate systemnocase,
F2P_1 integer,
F2P_2 integer,
F2P_3 integer,
F5 varchar(1) collate systemnocase,
AJPES varchar(1) collate systemnocase,
ZZ integer,
VD integer,
NS integer,
MES integer,
NORURE varchar(1) collate systemnocase,
G varchar(1) collate systemnocase,
E varchar(1) collate systemnocase,
H varchar(1) collate systemnocase,
I varchar(1) collate systemnocase,
J varchar(1) collate systemnocase,
SM varchar(1) collate systemnocase,
NO varchar(1) collate systemnocase,
PRIO varchar(1) collate systemnocase,
V_1 varchar(1) collate systemnocase,
V_2 varchar(1) collate systemnocase,
V_3 varchar(1) collate systemnocase,
V_4 varchar(1) collate systemnocase,
V_5 varchar(1) collate systemnocase,
V_6 varchar(1) collate systemnocase,
V_7 varchar(1) collate systemnocase,
V_8 varchar(1) collate systemnocase,
V_9 varchar(1) collate systemnocase,
V_10 varchar(1) collate systemnocase,
V_11 varchar(1) collate systemnocase,
V_12 varchar(1) collate systemnocase,
FOR integer,
P_1 integer,
P_2 integer,
P_3 integer,
P_4 integer,
P_5 integer,
P_6 integer,
primary key (RID, VP, BLANK));
When I create new table using this sql:
drop table if exists WM4P;
create table WM4P as
select
P, A, B, AB, U, H, ZZ,
case
when AB in ('7') then 99
when AB in ('57', '58', '59', '5M') then null
when AB = '56' and ZZ = 12 then 01
when AB = '56' then 02
when A = '3' then 03
when AB in ('1M') then 08
when AB in ('10') then 07
when AB in ('12') the
Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0
The behaviour does not need to match what you think of as consistent.
The only way to force a certain column name is with the AS clause *on the
outermost statement*. Otherwise, the column name is implementation defined and
may change between releases. You should not be relying on column names other
than those you explicitly set using the AS clause. This is a common mistake.
If you really need the column names, then just CREATE TABLE first (this gives
you defined column names AND declared data types) and the INSERT INTO ...
SELECT later (which ignores the generated column names from the select
statement).
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Radovan Antloga
Gesendet: Donnerstag, 21. Dezember 2017 16:35
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field alias in
3.21.0
Behaviour is not consistent when using
create table as
or just select statement.
Try this simple test.
create table test(a int, b int);
insert into test values (1, 1);
select d from (select c as d from (select a as c from test));
you get column name d as expected
but when you have create table as statement
create table test2 as
select d from (select c as d from (select a as c from test));
you get table test2 with column name a.
If you change to this
create table test2 as
select d as d from (select c as d from (select a as c from test));
you will get name correct. I think it should be the same as when just using
select statement.
Best Regards
Radovan
select a from (select b from (select c from test)))
Richard Hipp je 21.12.2017 ob 14:52 napisal:
> The behavior change is a bug fix. See
> http://sqlite.org/src/info/de3403bf5ae for details.
>
> On 12/21/17, Radovan Antloga <radovan.antl...@siol.net> wrote:
>> I have table (create statement):
>>
>> CREATE TABLE SOPP1 (
>> STAT varchar(1) collate systemnocase,
>> RID varchar(2) collate systemnocase,
>> VP integer,
>> BLANK varchar(6) collate systemnocase,
>> NAZIV varchar(24) collate systemnocase,
>> KN varchar(12) collate systemnocase,
>> A varchar(1) collate systemnocase,
>> B varchar(1) collate systemnocase,
>> RACUN varchar(1) collate systemnocase,
>> URE varchar(1) collate systemnocase,
>> ZN varchar(1) collate systemnocase,
>> TOCKE varchar(1) collate systemnocase,
>> PRC varchar(1) collate systemnocase,
>> UP varchar(1) collate systemnocase,
>> IZPIS varchar(1) collate systemnocase,
>> D varchar(1) collate systemnocase,
>> F2U varchar(1) collate systemnocase,
>> F2O varchar(1) collate systemnocase,
>> F2T varchar(1) collate systemnocase,
>> F2Z varchar(1) collate systemnocase,
>> F2P_1 integer,
>> F2P_2 integer,
>> F2P_3 integer,
>> F5 varchar(1) collate systemnocase,
>> AJPES varchar(1) collate systemnocase,
>> ZZ integer,
>> VD integer,
>> NS integer,
>> MES integer,
>> NORURE varchar(1) collate systemnocase,
>> G varchar(1) collate systemnocase,
>> E varchar(1) collate systemnocase,
>> H varchar(1) collate systemnocase,
>> I varchar(1) collate systemnocase,
>> J varchar(1) collate systemnocase,
>> SM varchar(1) collate systemnocase,
>> NO varchar(1) collate systemnocase,
>> PRIO varchar(1) collate systemnocase,
>> V_1 varchar(1) collate systemnocase,
>> V_2 varchar(1) collate systemnocase,
>> V_3 varchar(1) collate systemnocase,
>> V_4 varchar(1) collate systemnocase,
>> V_5 varchar(1) collate systemnocase,
>> V_6 varchar(1) collate systemnocase,
>> V_7 varchar(1) collate systemnocase,
>> V_8 varchar(1) collate systemnocase,
>> V_9 varchar(1) collate systemnocase,
>> V_10 varchar(1) collate systemnocase,
>> V_11 varchar(1) collate systemnocase,
>> V_12 varchar(1) collate systemnocase,
>> FOR integer,
>> P_1 integer,
>> P_2 integer,
>> P_3 integer,
>> P_4 integer,
>> P_5 integer,
>> P_6 integer,
>> primary key (RID, VP, BLANK));
>>
>> When I create new table using this sql:
>>
>> drop table if exists WM4P;
>> create table WM4P as
>> select
>> P, A, B, AB, U, H, ZZ,
>> case
>> when AB in ('7') then 99
>> when AB in ('57', '58', '59', '5M') then null
>> when AB = '56' and ZZ = 12 then 01
>> when AB = '56' then 02
>> when A = '3' then 03
>>
