Changed behaviour or bug using field alias in 3.21.0

classic Classic list List threaded Threaded
31 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Radovan Antloga
My point is you do not have to change anything regards
to how select works. SELECT statement is working just
great or OK or fine.

CREATE TABLE AS
SELECT ....

does not give same name as SELECT does.

SELECT give name d

CREATE TABLE AS SELECT
create table with name a instead of d.

Richard Hipp je 22.12.2017 ob 19:45 napisal:
> On 12/22/17, Radovan Antloga <[hidden email]> wrote:
>> In my example I have AS clause so rule 1.
> You did not understand my question.
>
> I think what you are asking for is that we should enhance rule 1 so
> that it applies even if the AS clause is contained within a subquery.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

David Raymond
In reply to this post by Richard Hipp-3
Correct.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Friday, December 22, 2017 1:45 PM
To: SQLite mailing list
Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

On 12/22/17, Radovan Antloga <[hidden email]> wrote:
> In my example I have AS clause so rule 1.

You did not understand my question.

I think what you are asking for is that we should enhance rule 1 so
that it applies even if the AS clause is contained within a subquery.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Richard Hipp-3
In reply to this post by Radovan Antloga
On 12/22/17, Radovan Antloga <[hidden email]> wrote:

> My point is you do not have to change anything regards
> to how select works. SELECT statement is working just
> great or OK or fine.
>
> CREATE TABLE AS
> SELECT ....
>
> does not give same name as SELECT does.
>
> SELECT give name d
>
> CREATE TABLE AS SELECT
> create table with name a instead of d.

Can you provide a simple test case for this behavior?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Radovan Antloga
In reply to this post by David Raymond
What is correct?

Do you understand what I write?
What is my point? Tell me please.

David Raymond je 22.12.2017 ob 20:04 napisal:

> Correct.
>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
> Sent: Friday, December 22, 2017 1:45 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
>
> On 12/22/17, Radovan Antloga <[hidden email]> wrote:
>> In my example I have AS clause so rule 1.
> You did not understand my question.
>
> I think what you are asking for is that we should enhance rule 1 so
> that it applies even if the AS clause is contained within a subquery.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Radovan Antloga
In reply to this post by Richard Hipp-3
Just try this sql-s:

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));

will return name d.

create table test2 as
select d from (select c as d from (select a as c from test));

will create table test2 with column name a.

with PostgreSQL I get table test2 with name d.

Thank you very much for your time!


Richard Hipp je 22.12.2017 ob 20:06 napisal:

> On 12/22/17, Radovan Antloga <[hidden email]> wrote:
>> My point is you do not have to change anything regards
>> to how select works. SELECT statement is working just
>> great or OK or fine.
>>
>> CREATE TABLE AS
>> SELECT ....
>>
>> does not give same name as SELECT does.
>>
>> SELECT give name d
>>
>> CREATE TABLE AS SELECT
>> create table with name a instead of d.
> Can you provide a simple test case for this behavior?
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Richard Hipp-3
Your trouble ticket is here: https://sqlite.org/src/tktview/3b4450072511

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Klaus Maas
In reply to this post by Radovan Antloga
Radovan is correct.
Executing the same command sequence in version 3.11.0 and 3.21.0 results
in different column names for table test2.
(Xubuntu 16.04 with sqlite3.11.0 and sqlite3.21.0)
I marked the results with '<====='
Klaus


SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .header on
sqlite>
sqlite> create table test(a int, b int);
sqlite> insert into test values (1,1);
sqlite>
sqlite> select d from (select c as d from (select a as c from test));
d
1
sqlite>
sqlite> create table test2 as
    ...> select d from (select c as d from (select a as c from test));
sqlite> SELECT * FROM test2;
d  <=====
1
sqlite>



SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .header on
sqlite> create table test(a int, b int);
sqlite> insert into test values (1,1);
sqlite>
sqlite> select d from (select c as d from (select a as c from test));
d
1
sqlite> create table test2 as
    ...> select d from (select c as d from (select a as c from test));
sqlite> SELECT * FROM test2;
a   <=====
1
sqlite>

email signature Klaus Maas
------------------------------------------------------------------------
On 2017-12-22 20:11, Radovan Antloga wrote:

> Just try this sql-s:
>
> 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));
>
> will return name d.
>
> create table test2 as
> select d from (select c as d from (select a as c from test));
>
> will create table test2 with column name a.
>
> with PostgreSQL I get table test2 with name d.
>
> Thank you very much for your time!
>
>
> Richard Hipp je 22.12.2017 ob 20:06 napisal:
>> On 12/22/17, Radovan Antloga <[hidden email]> wrote:
>>> My point is you do not have to change anything regards
>>> to how select works. SELECT statement is working just
>>> great or OK or fine.
>>>
>>> CREATE TABLE AS
>>> SELECT ....
>>>
>>> does not give same name as SELECT does.
>>>
>>> SELECT give name d
>>>
>>> CREATE TABLE AS SELECT
>>> create table with name a instead of d.
>> Can you provide a simple test case for this behavior?
>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Radovan Antloga
In reply to this post by Richard Hipp-3
THANK YOU!

Richard Hipp je 22.12.2017 ob 20:29 napisal:
> Your trouble ticket is here: https://sqlite.org/src/tktview/3b4450072511
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Radovan Antloga
In reply to this post by Klaus Maas
Thank you Klaus!

Klaus Maas je 22.12.2017 ob 20:30 napisal:

> Radovan is correct.
> Executing the same command sequence in version 3.11.0 and 3.21.0
> results in different column names for table test2.
> (Xubuntu 16.04 with sqlite3.11.0 and sqlite3.21.0)
> I marked the results with '<====='
> Klaus
>
>
> SQLite version 3.11.0 2016-02-15 17:29:24
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .header on
> sqlite>
> sqlite> create table test(a int, b int);
> sqlite> insert into test values (1,1);
> sqlite>
> sqlite> select d from (select c as d from (select a as c from test));
> d
> 1
> sqlite>
> sqlite> create table test2 as
>    ...> select d from (select c as d from (select a as c from test));
> sqlite> SELECT * FROM test2;
> d  <=====
> 1
> sqlite>
>
>
>
> SQLite version 3.21.0 2017-10-24 18:55:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .header on
> sqlite> create table test(a int, b int);
> sqlite> insert into test values (1,1);
> sqlite>
> sqlite> select d from (select c as d from (select a as c from test));
> d
> 1
> sqlite> create table test2 as
>    ...> select d from (select c as d from (select a as c from test));
> sqlite> SELECT * FROM test2;
> a   <=====
> 1
> sqlite>
>
> email signature Klaus Maas
> ------------------------------------------------------------------------
> On 2017-12-22 20:11, Radovan Antloga wrote:
>> Just try this sql-s:
>>
>> 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));
>>
>> will return name d.
>>
>> create table test2 as
>> select d from (select c as d from (select a as c from test));
>>
>> will create table test2 with column name a.
>>
>> with PostgreSQL I get table test2 with name d.
>>
>> Thank you very much for your time!
>>
>>
>> Richard Hipp je 22.12.2017 ob 20:06 napisal:
>>> On 12/22/17, Radovan Antloga <[hidden email]> wrote:
>>>> My point is you do not have to change anything regards
>>>> to how select works. SELECT statement is working just
>>>> great or OK or fine.
>>>>
>>>> CREATE TABLE AS
>>>> SELECT ....
>>>>
>>>> does not give same name as SELECT does.
>>>>
>>>> SELECT give name d
>>>>
>>>> CREATE TABLE AS SELECT
>>>> create table with name a instead of d.
>>> Can you provide a simple test case for this behavior?
>>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

petern
Radovan.  Thank you for sticking to your guns.  Your appeal to expected
behavior under other DB engines was also a very good post.  I see the usual
suspects of the echo chamber uselessly piled on against you to clog the
forum nevertheless.  That happens too frequently.

BTW, here is a simpler test suite which pinpoints the problem:

SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c));
d
1

CREATE TABLE test AS SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c));
SELECT * FROM test;
c
1

CREATE TABLE test AS SELECT c AS d FROM (SELECT 1 AS c);
SELECT * FROM test;
d
1

And still one more that illustrates the contradictory and unstable
behavior. Use '*' instead of 'd' and it works as expected.

CREATE TABLE test AS SELECT * FROM (SELECT c AS d FROM (SELECT 1 AS c));
SELECT * FROM test;
d
1

Obviously the most immediate well formed alias 'd' should be carried
through irrespective of nesting.

FYI, there was a checkin just now with some changes to track intermediate
aliases:

http://www.sqlite.org/src/info/5efd854fe2147033

Peter


On Fri, Dec 22, 2017 at 11:34 AM, Radovan Antloga <[hidden email]>
wrote:

> Thank you Klaus!
>
> Klaus Maas je 22.12.2017 ob 20:30 napisal:
>
>> Radovan is correct.
>> Executing the same command sequence in version 3.11.0 and 3.21.0 results
>> in different column names for table test2.
>> (Xubuntu 16.04 with sqlite3.11.0 and sqlite3.21.0)
>> I marked the results with '<====='
>> Klaus
>>
>>
>> SQLite version 3.11.0 2016-02-15 17:29:24
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .header on
>> sqlite>
>> sqlite> create table test(a int, b int);
>> sqlite> insert into test values (1,1);
>> sqlite>
>> sqlite> select d from (select c as d from (select a as c from test));
>> d
>> 1
>> sqlite>
>> sqlite> create table test2 as
>>    ...> select d from (select c as d from (select a as c from test));
>> sqlite> SELECT * FROM test2;
>> d  <=====
>> 1
>> sqlite>
>>
>>
>>
>> SQLite version 3.21.0 2017-10-24 18:55:49
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .header on
>> sqlite> create table test(a int, b int);
>> sqlite> insert into test values (1,1);
>> sqlite>
>> sqlite> select d from (select c as d from (select a as c from test));
>> d
>> 1
>> sqlite> create table test2 as
>>    ...> select d from (select c as d from (select a as c from test));
>> sqlite> SELECT * FROM test2;
>> a   <=====
>> 1
>> sqlite>
>>
>> email signature Klaus Maas
>> ------------------------------------------------------------------------
>> On 2017-12-22 20:11, Radovan Antloga wrote:
>>
>>> Just try this sql-s:
>>>
>>> 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));
>>>
>>> will return name d.
>>>
>>> create table test2 as
>>> select d from (select c as d from (select a as c from test));
>>>
>>> will create table test2 with column name a.
>>>
>>> with PostgreSQL I get table test2 with name d.
>>>
>>> Thank you very much for your time!
>>>
>>>
>>> Richard Hipp je 22.12.2017 ob 20:06 napisal:
>>>
>>>> On 12/22/17, Radovan Antloga <[hidden email]> wrote:
>>>>
>>>>> My point is you do not have to change anything regards
>>>>> to how select works. SELECT statement is working just
>>>>> great or OK or fine.
>>>>>
>>>>> CREATE TABLE AS
>>>>> SELECT ....
>>>>>
>>>>> does not give same name as SELECT does.
>>>>>
>>>>> SELECT give name d
>>>>>
>>>>> CREATE TABLE AS SELECT
>>>>> create table with name a instead of d.
>>>>>
>>>> Can you provide a simple test case for this behavior?
>>>>
>>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Radovan Antloga
Thank you Peter!

I like sqlite so much and I think we all benefit if
errors are fixed. I see I was not very clear with my
first post. Will do better next time. Yes would
be nice if people would try to understand first
not just think on title text.

Talking about sqlite I use it for web development,
desktop apps. I also created tool or gui sqlite manager
and will try to sell it in near future. I needed very
good tool because I work with sqlite every day. It
is just for windows platform. I support different
datetime formats, blobs, compress etc. See this page:

http://www.arsistemi.si/izdelki/sqlite-4-all.html

Every report is created with sqlite tables. I read
data from different RDBMS to sqlite and then work
with data as needed.

petern je 22.12.2017 ob 23:19 napisal:

> Radovan.  Thank you for sticking to your guns.  Your appeal to expected
> behavior under other DB engines was also a very good post.  I see the usual
> suspects of the echo chamber uselessly piled on against you to clog the
> forum nevertheless.  That happens too frequently.
>
> BTW, here is a simpler test suite which pinpoints the problem:
>
> SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c));
> d
> 1
>
> CREATE TABLE test AS SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c));
> SELECT * FROM test;
> c
> 1
>
> CREATE TABLE test AS SELECT c AS d FROM (SELECT 1 AS c);
> SELECT * FROM test;
> d
> 1
>
> And still one more that illustrates the contradictory and unstable
> behavior. Use '*' instead of 'd' and it works as expected.
>
> CREATE TABLE test AS SELECT * FROM (SELECT c AS d FROM (SELECT 1 AS c));
> SELECT * FROM test;
> d
> 1
>
> Obviously the most immediate well formed alias 'd' should be carried
> through irrespective of nesting.
>
> FYI, there was a checkin just now with some changes to track intermediate
> aliases:
>
> http://www.sqlite.org/src/info/5efd854fe2147033
>
> Peter
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12