Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

2011-02-14 Thread Nißl Reinhard 
Hi,

sorry for the noise. Again trying to supply attachments.

Bye.
--
Reinhard Nißl

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
Im Auftrag von Nißl Reinhard
Gesendet: Montag, 14. Februar 2011 13:56
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of 
column names for certain statements

Hi,

looks like the patch did got stripped. I've attached it again.

And please find attached a further patch which addresses TK_AGG_COLUMNS.

Bye.
--
Reinhard Nißl

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
Im Auftrag von Nißl Reinhard
Gesendet: Samstag, 12. Februar 2011 23:42
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of 
column names for certain statements

Hi,

it took me 6 hours to find the source location which behaves inconsistent.

Please find attached a fix for this bug.

Bye.
--
Reinhard Nißl

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
Im Auftrag von Nißl Reinhard
Gesendet: Freitag, 11. Februar 2011 10:42
An: j...@kreibi.ch; General Discussion of SQLite Database
Betreff: Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of 
column names for certain statements

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.

--- src/select.c
+++ src/select.c
@@ -2710,10 +2710,53 @@
 }
   }
 
   /* If we reach this point, flattening is permitted. */
 
+  pList = p->pEList;
+  {
+SrcList *pTabList = pSrc; /* List of tables to select from */
+int fullNames, shortNames;
+fullNames = (db->flags & SQLITE_FullColNames)!=0;
+shortNames = (db->flags & SQLITE_ShortColNames)!=0;
+/* generate alias names for columns that are consistent with 
generateColumnNames() */
+for(i=0; inExpr; i++){
+  if( pList->a[i].zName==0 ){
+Expr *p;
+p = pList->a[i].pExpr;
+
+if( (p->op==TK_COLUMN || p->op==TK_AGG_COLUMN) && pTabList && 
(shortNames || fullNames) ){
+  Table *pTab;
+  char *zCol;
+  int j, iCol = p->iColumn;
+  for(j=0; ALWAYS(jnSrc); j++){
+if( pTabList->a[j].iCursor==p->iTable ) break;
+  }
+  assert( jnSrc );
+  pTab = pTabList->a[j].pTab;
+  if( iCol<0 ) iCol = pTab->iPKey;
+  assert( iCol==-1 || (iCol>=0 && iColnCol) );
+  if( iCol<0 ){
+zCol = "rowid";
+  }else{
+zCol = pTab->aCol[iCol].zName;
+  }
+  if( fullNames ){
+pList->a[i].zName = sqlite3MPrintf(db, "%s.%s", pTab->zName, zCol);
+  }else{
+pList->a[i].zName = sqlite3DbStrDup(db, zCol);
+  }
+}else{
+  const char *zSpan = pList->a[i].zSpan;
+  if( ALWAYS(zSpan) ){
+pList->a[i].zName = sqlite3DbStrDup(db, zSpan);
+  }
+}
+  }
+}
+  }
+
   /* Authorize the subquery */
   pParse->zAuthContext = pSubitem->zName;
   sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0);
   pParse->zAuthContext = zSavedAuthContext;
 
@@ -2884,19 +2927,10 @@
 **\_ outer query __/
 **
 ** We look at every expression in the outer query and every place we see
 ** "a" we substitute "x*3" and every place we see "b" we substitute "y+10".
 */
-pList = pParent->pEList;
-for(i=0; inExpr; i++){
-  if( pList->a[i].zName==0 ){
-const char *zSpan = pList->a[i].zSpan;
-if( ALWAYS(zSpan) ){
-  pList->a[i].zName = sqlite3DbStrDup(db, zSpan);
-}
-  }
-}
 substExprList(db, pParent->pEList, iParent, pSub->pEList);
 if( isAgg ){
   substExprList(db, pParent->pGroupBy, iParent, pSub->pEList);
   pParent->pHaving = substExpr(db, pParent->pHaving, iParent, 
pSub->pEList);
 }

--- src/select.c
+++ src/select.c
@@ -89284,7 +89284,7 @@
   Expr *pColExpr = p;  /* The expression that is the result column name */
   Table *pTab; /* Table associated with this expression */
   while( pColExpr->op==TK_DOT ) pColExpr = pColExpr->pRight;
-  if( pColExpr->op==TK_COLUMN && ALWAYS(pColExpr->pTab!=0) ){
+  if( (pColExpr->op==TK_COLUMN || pColExpr->op==TK_AGG_COLUMN) && 
ALWAYS(pColExpr->pTab!=0) ){
 /* For columns u

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

2011-02-14 Thread Nißl Reinhard 
Hi,

looks like the patch did got stripped. I've attached it again.

And please find attached a further patch which addresses TK_AGG_COLUMNS.

Bye.
--
Reinhard Nißl

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
Im Auftrag von Nißl Reinhard
Gesendet: Samstag, 12. Februar 2011 23:42
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of 
column names for certain statements

Hi,

it took me 6 hours to find the source location which behaves inconsistent.

Please find attached a fix for this bug.

Bye.
--
Reinhard Nißl

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
Im Auftrag von Nißl Reinhard
Gesendet: Freitag, 11. Februar 2011 10:42
An: j...@kreibi.ch; General Discussion of SQLite Database
Betreff: Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of 
column names for certain statements

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.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

2011-02-12 Thread Nißl Reinhard 
Hi,

it took me 6 hours to find the source location which behaves inconsistent.

Please find attached a fix for this bug.

Bye.
--
Reinhard Nißl

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
Im Auftrag von Nißl Reinhard
Gesendet: Freitag, 11. Februar 2011 10:42
An: j...@kreibi.ch; General Discussion of SQLite Database
Betreff: Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of 
column names for certain statements

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.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

2011-02-11 Thread Edzard Pasma 
Op 11-feb-2011, om 10:41 heeft Nißl Reinhard het volgende geschreven:

> 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

Hi,

The engine already determines unquoted column names when creating  
views. This also applies with inline-vies. For instance:

sqlite>.mode line
sqlite> select * from (select [x].[a] from (select a from x) x);
 a = 123

This also cuts off the unnecessary table alias.

If column descriptions could be changed to be like this by default?

Edzard Pasma


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

2011-02-11 Thread Nißl Reinhard 
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

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

2011-02-10 Thread Jay A. Kreibich 
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

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

2011-02-10 Thread David Bicking 


--- On Thu, 2/10/11, Puneet Kishor  wrote:
> Date: Thursday, February 10, 2011, 10:35 AM
> 
> On Thursday, February 10, 2011 at 9:22 AM, Nißl Reinhard
> wrote: 
> > Hi,
> > 
> > I'm sorry Pavel, I think you've got me wrong.
> > 
> > > It's not "buggy". Name of the column in result
> set is not defined
> > > unless you use "as".
> > 
> Pavel is not wrong. SQLite is not buggy. Your expectation
> of what it should do is at fault here.
> > ..
> 
> 
> I used to think as you too, but then learned otherwise. The
> developers simply didn't add the code to dependably
> determine what the column should be called, because,
> surprisingly (or not surprisingly, if you think about it a
> bit), that code would be very complex to cover all the
> possible cases. The simplest solution is to depend upon AS
> aliasing, else, do something undependable (to us).
> 
> Puneet. 
> 

Not only is it complex, but as I recall, back when the developers did try to be 
consistent in how column names were presented, no matter how they coded it, 
someone would pipe up with an opinion that it should be done another way. There 
were pragmas to try to get the column names to match different expectations.

In the end, they decided to just call it undefined and let the user use AS to 
get what they wanted. I think that was the right decision.

David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

2011-02-10 Thread Puneet Kishor 

On Thursday, February 10, 2011 at 9:22 AM, Nißl Reinhard wrote: 
> Hi,
> 
> I'm sorry Pavel, I think you've got me wrong.
> 
> > It's not "buggy". Name of the column in result set is not defined
> > unless you use "as".
> 
Pavel is not wrong. SQLite is not buggy. Your expectation of what it should do 
is at fault here.
> ..


I used to think as you too, but then learned otherwise. The developers simply 
didn't add the code to dependably determine what the column should be called, 
because, surprisingly (or not surprisingly, if you think about it a bit), that 
code would be very complex to cover all the possible cases. The simplest 
solution is to depend upon AS aliasing, else, do something undependable (to us).

Puneet. 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

2011-02-10 Thread Pavel Ivanov 
> But why do I get different column names but the same result for these 
> statements?
>        select  a  from (select a from x);
>        select [a] from (select a from x);
>        select "a" from (select a from x);

Please show us documentation that says you should get the same column
name for each of these statements. I can understand that this is kind
of counter-intuitive for you but it behaves exactly how it's
documented, i.e. column name is undefined and can change from version
to version.

> I consider this a bug.

You can consider it a bug. But until SQLite developers consider it a
bug it won't be fixed. I'm not one of SQLite developers but I know how
they feel about this problem because it was raised on this list
numerous times.


Pavel

On Thu, Feb 10, 2011 at 10:22 AM, Nißl Reinhard <reinhard.ni...@fee.de> wrote:
> Hi,
>
> I'm sorry Pavel, I think you've got me wrong.
>
>> It's not "buggy". Name of the column in result set is not defined
>> unless you use "as".
>
> But why do I get different column names but the same result for these 
> statements?
>
>        select  a  from (select a from x);
>        select [a] from (select a from x);
>        select "a" from (select a from x);
>
> For all three statements the column name should be just >>a<<, as it is for 
> these statements:
>
>        select  a  from x;
>        select [a] from x;
>        select "a" from x;
>
> Why should I have to write to the statements like below to get what I want?
>
>        select  a   a  from (select a from x);
>        select  a  [a] from (select a from x);
>        select  a  "a" from (select a from x);
>        select [a]  a  from (select a from x);
>        select [a] [a] from (select a from x);
>        select [a] "a" from (select a from x);
>        select "a"  a  from (select a from x);
>        select "a" [a] from (select a from x);
>        select "a" "a" from (select a from x);
>
> I consider this a bug.
>
> Bye.
> --
> Reinhard Nißl
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
> Im Auftrag von Pavel Ivanov
> Gesendet: Donnerstag, 10. Februar 2011 13:48
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] BUG (3.7.5): identifier quotation characters get part 
> of column names for certain statements
>
>>        select [a] from (select * from x);
>> You'll get the following "buggy" output:
>>        [a]
>>        1
>
> It's not "buggy". Name of the column in result set is not defined
> unless you use "as".
>
>>        CREATE TABLE y("[a]" INT);
>> I came across this issue as statements like the following failed with the 
>> below mentioned error due to incorrect column names in the created tables:
>>        create index [y.a] on y ([a]);
>> Output in version 3.7.5:
>>        Error: table y has no column named a
>
> Because symbols [] have special meaning - quoting identifiers. So your
> statement basically looked the same as the following:
>
> create index "y.a" on y("a");
>
> And indeed table y doesn't have such column. The following statement
> should work:
>
> create index "y.a" on y("[a]");
>
>
> But best of all use "as" clause in your queries and never use "create
> ... as select ..." in any application (it can be useful only in some
> quick-and-dirty debugging). And also I would suggest not using
> confusing names for any table, index or column (e.g. as your "y.a").
>
>
> Pavel
>
> On Thu, Feb 10, 2011 at 6:25 AM, Nißl Reinhard <reinhard.ni...@fee.de> wrote:
>> Hi,
>>
>> identifier quotation characters ("[]) get part of column names for certain 
>> statements, i. e. it depends on the complexity of the statement to trigger 
>> this bug.
>>
>> To reproduce the bug, type the following in sqlite3:
>>
>>        .headers ON
>>        create table x(a int);
>>        insert into x values (1);
>>        select [a] from (select * from x);
>>
>> You'll get the following "buggy" output:
>>
>>        [a]
>>        1
>>
>> The correct output is returned for this statement:
>>
>>        select [a] from x;
>>
>> You'll get:
>>
>>        a
>>        1
>>
>> I've run into this bug after updating from 3.3.6 to 3.7.5. In 3.3.6, even 
>> the following statement returned an incorrect column name:
>>
>&g

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

2011-02-10 Thread Nißl Reinhard 
Hi,

I'm sorry Pavel, I think you've got me wrong.

> It's not "buggy". Name of the column in result set is not defined
> unless you use "as".

But why do I get different column names but the same result for these 
statements?

select  a  from (select a from x);
select [a] from (select a from x);
select "a" from (select a from x);

For all three statements the column name should be just >>a<<, as it is for 
these statements:

select  a  from x;
select [a] from x;
select "a" from x;

Why should I have to write to the statements like below to get what I want?
 
select  a   a  from (select a from x);
select  a  [a] from (select a from x);
select  a  "a" from (select a from x);
select [a]  a  from (select a from x);
select [a] [a] from (select a from x);
select [a] "a" from (select a from x);
select "a"  a  from (select a from x);
select "a" [a] from (select a from x);
select "a" "a" from (select a from x);

I consider this a bug.

Bye.
--
Reinhard Nißl

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
Im Auftrag von Pavel Ivanov
Gesendet: Donnerstag, 10. Februar 2011 13:48
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of 
column names for certain statements

>        select [a] from (select * from x);
> You'll get the following "buggy" output:
>        [a]
>        1

It's not "buggy". Name of the column in result set is not defined
unless you use "as".

>        CREATE TABLE y("[a]" INT);
> I came across this issue as statements like the following failed with the 
> below mentioned error due to incorrect column names in the created tables:
>        create index [y.a] on y ([a]);
> Output in version 3.7.5:
>        Error: table y has no column named a

Because symbols [] have special meaning - quoting identifiers. So your
statement basically looked the same as the following:

create index "y.a" on y("a");

And indeed table y doesn't have such column. The following statement
should work:

create index "y.a" on y("[a]");


But best of all use "as" clause in your queries and never use "create
... as select ..." in any application (it can be useful only in some
quick-and-dirty debugging). And also I would suggest not using
confusing names for any table, index or column (e.g. as your "y.a").


Pavel

On Thu, Feb 10, 2011 at 6:25 AM, Nißl Reinhard <reinhard.ni...@fee.de> wrote:
> Hi,
>
> identifier quotation characters ("[]) get part of column names for certain 
> statements, i. e. it depends on the complexity of the statement to trigger 
> this bug.
>
> To reproduce the bug, type the following in sqlite3:
>
>        .headers ON
>        create table x(a int);
>        insert into x values (1);
>        select [a] from (select * from x);
>
> You'll get the following "buggy" output:
>
>        [a]
>        1
>
> The correct output is returned for this statement:
>
>        select [a] from x;
>
> You'll get:
>
>        a
>        1
>
> I've run into this bug after updating from 3.3.6 to 3.7.5. In 3.3.6, even the 
> following statement returned an incorrect column name:
>
>        select [a] from x group by a;
>
> The 3.3.6 result was:
>
>        [a]
>        1
>
> The 3.7.5 correct result is:
>
>        a
>        1
>
> While I knew this bug for some years already it didn't matter much in my 
> software. In 3.7.5 it hurts me due to the corrected behavior for "create 
> table ... as select ..." statements. In 3.3.6 the column names were 
> implicitly dequoted (which in my current opinion was incorrect) so the below 
> statement created the table as shown:
>
>        create table y as select [a] from (select * from x);
>        .schema y
>
> Output in version 3.3.6:
>
>        CREATE TABLE y(a int);
>
> In 3.7.5 with corrected behavior, the output looks like that (and is correct 
> according to the buggy select statement):
>
>        CREATE TABLE y("[a]" INT);
>
> I came across this issue as statements like the following failed with the 
> below mentioned error due to incorrect column names in the created tables:
>
>        create index [y.a] on y ([a]);
>
> Output in version 3.7.5:
>
>        Error: table y has no column named a
>
> I really would like to get that fixed in 3.7.6. At least a patch would be 
> welcome during the next week.
>
> Attached you'l

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

2011-02-10 Thread Pavel Ivanov 
>        select [a] from (select * from x);
> You'll get the following "buggy" output:
>        [a]
>        1

It's not "buggy". Name of the column in result set is not defined
unless you use "as".

>        CREATE TABLE y("[a]" INT);
> I came across this issue as statements like the following failed with the 
> below mentioned error due to incorrect column names in the created tables:
>        create index [y.a] on y ([a]);
> Output in version 3.7.5:
>        Error: table y has no column named a

Because symbols [] have special meaning - quoting identifiers. So your
statement basically looked the same as the following:

create index "y.a" on y("a");

And indeed table y doesn't have such column. The following statement
should work:

create index "y.a" on y("[a]");


But best of all use "as" clause in your queries and never use "create
... as select ..." in any application (it can be useful only in some
quick-and-dirty debugging). And also I would suggest not using
confusing names for any table, index or column (e.g. as your "y.a").


Pavel

On Thu, Feb 10, 2011 at 6:25 AM, Nißl Reinhard  wrote:
> Hi,
>
> identifier quotation characters ("[]) get part of column names for certain 
> statements, i. e. it depends on the complexity of the statement to trigger 
> this bug.
>
> To reproduce the bug, type the following in sqlite3:
>
>        .headers ON
>        create table x(a int);
>        insert into x values (1);
>        select [a] from (select * from x);
>
> You'll get the following "buggy" output:
>
>        [a]
>        1
>
> The correct output is returned for this statement:
>
>        select [a] from x;
>
> You'll get:
>
>        a
>        1
>
> I've run into this bug after updating from 3.3.6 to 3.7.5. In 3.3.6, even the 
> following statement returned an incorrect column name:
>
>        select [a] from x group by a;
>
> The 3.3.6 result was:
>
>        [a]
>        1
>
> The 3.7.5 correct result is:
>
>        a
>        1
>
> While I knew this bug for some years already it didn't matter much in my 
> software. In 3.7.5 it hurts me due to the corrected behavior for "create 
> table ... as select ..." statements. In 3.3.6 the column names were 
> implicitly dequoted (which in my current opinion was incorrect) so the below 
> statement created the table as shown:
>
>        create table y as select [a] from (select * from x);
>        .schema y
>
> Output in version 3.3.6:
>
>        CREATE TABLE y(a int);
>
> In 3.7.5 with corrected behavior, the output looks like that (and is correct 
> according to the buggy select statement):
>
>        CREATE TABLE y("[a]" INT);
>
> I came across this issue as statements like the following failed with the 
> below mentioned error due to incorrect column names in the created tables:
>
>        create index [y.a] on y ([a]);
>
> Output in version 3.7.5:
>
>        Error: table y has no column named a
>
> I really would like to get that fixed in 3.7.6. At least a patch would be 
> welcome during the next week.
>
> Attached you'll find some statements to test with and the outputs of sqlite3 
> for versions 3.7.5 and 3.3.6.
>
> Bye.
> --
> Reinhard Nißl
>
> ___
> 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

[sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

2011-02-10 Thread Nißl Reinhard 
Hi,

identifier quotation characters ("[]) get part of column names for certain 
statements, i. e. it depends on the complexity of the statement to trigger this 
bug.

To reproduce the bug, type the following in sqlite3:

.headers ON
create table x(a int);
insert into x values (1);
select [a] from (select * from x);

You'll get the following "buggy" output:

[a]
1

The correct output is returned for this statement:

select [a] from x;

You'll get:

a
1

I've run into this bug after updating from 3.3.6 to 3.7.5. In 3.3.6, even the 
following statement returned an incorrect column name:

select [a] from x group by a;

The 3.3.6 result was:

[a]
1

The 3.7.5 correct result is:

a
1

While I knew this bug for some years already it didn't matter much in my 
software. In 3.7.5 it hurts me due to the corrected behavior for "create table 
... as select ..." statements. In 3.3.6 the column names were implicitly 
dequoted (which in my current opinion was incorrect) so the below statement 
created the table as shown:

create table y as select [a] from (select * from x);
.schema y

Output in version 3.3.6:

CREATE TABLE y(a int);

In 3.7.5 with corrected behavior, the output looks like that (and is correct 
according to the buggy select statement):

CREATE TABLE y("[a]" INT);

I came across this issue as statements like the following failed with the below 
mentioned error due to incorrect column names in the created tables:

create index [y.a] on y ([a]);

Output in version 3.7.5:

Error: table y has no column named a

I really would like to get that fixed in 3.7.6. At least a patch would be 
welcome during the next week.

Attached you'll find some statements to test with and the outputs of sqlite3 
for versions 3.7.5 and 3.3.6.

Bye.
--
Reinhard Nißl
.headers ON
create table x(a int);
insert into x values (1);
select [a] from x;
select [a] from x group by a;
select [a] from (select a from x);
select [a] from (select a from x) group by a;
create table y as select [a] from (select a from x) group by a;
create index [y.a] on y([a]);
select "a" from x;
select "a" from x group by a;
select "a" from (select a from x);
select "a" from (select a from x) group by a;
create table z as select "a" from (select a from x) group by a;
create index "z.a" on z("a");
.schema
drop table x;
drop table y;
drop table z;
create table x([a.b] int);
insert into x values (1);
select [a.b] from x;
select [a.b] from x group by [a.b];
select [a.b] from (select [a.b] from x);
select [a.b] from (select [a.b] from x) group by [a.b];
create table y as select [a.b] from (select [a.b] from x) group by [a.b];
create index [y.a.b] on y([a.b]);
select "a.b" from x;
select "a.b" from x group by "a.b";
select "a.b" from (select "a.b" from x);
select "a.b" from (select "a.b" from x) group by "a.b";
create table z as select "a.b" from (select "a.b" from x) group by "a.b";
create index "z.a.b" on z("a.b");
.schema
.quit
a
1
a
1
[a]
1
[a]
1
a
1
a
1
"a"
1
"a"
1
CREATE TABLE x(a int);
CREATE TABLE y("[a]" INT);
CREATE TABLE z("""a""" INT);
a.b
1
a.b
1
[a.b]
1
[a.b]
1
a.b
1
a.b
1
"a.b"
1
"a.b"
1
CREATE TABLE x([a.b] int);
CREATE TABLE y("[a.b]" INT);
CREATE TABLE z("""a.b""" INT);
Error: near line 9: table y has no column named a
Error: near line 15: table z has no column named a
Error: near line 27: table y has no column named a.b
Error: near line 33: table z has no column named a.b
a
1
[a]
1
[a]
1
[a]
1
a
1
"a"
1
"a"
1
"a"
1
CREATE TABLE x(a int);
CREATE TABLE y(a int);
CREATE TABLE z(a int);
CREATE INDEX [y.a] on y([a]);
CREATE INDEX "z.a" on z("a");
a.b
1
[a.b]
1
[a.b]
1
[a.b]
1
a.b
1
"a.b"
1
"a.b"
1
"a.b"
1
CREATE TABLE x([a.b] int);
CREATE TABLE y("a.b" int);
CREATE TABLE z("a.b" int);
CREATE INDEX [y.a.b] on y([a.b]);
CREATE INDEX "z.a.b" on z("a.b");
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users