Re: [sqlite] feature request -- enhance strftime() implementing %V, %g and %G for week of year according to ISO 8601

[Nißl Reinhard]

Hello Luuk,

in your mentionend man page, there is a similar sentence which is also wrong 
and needs to be corrected:

"When three of fewer days of the first calendar week of the new year fall 
within that year, then the ISO 8601 week-based system counts those days as part 
of week 53 of the preceding year."

A unit test should verify that ISO week for 2012-01-01 is 52 (not 53) and ISO 
year is 2011.

Nice to play with: https://www.timeanddate.com/date/weeknumber.html

Bye.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Luuk
Sent: Thursday, May 9, 2019 7:57 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] feature request -- enhance strftime() implementing %V, %g 
and %G for week of year according to ISO 8601


On 9-5-2019 18:20, Nißl Reinhard wrote:
> Hi,
>
> it would be nice, if sqlite3's strftime() would support the following 
> formatting codes:
> %gThe last 2 digits of the ISO 8601 week-based year as a decimal number 
> (00 - 99)
> %GThe ISO 8601 week-based year as a decimal number
> %VISO 8601 week number as a decimal number (00 - 53)
minimum value for %V is 01.
>
> The ISO 8601 week and week-based year produced by %V, %g, and %G, uses a week 
> that begins on Monday, where week 1 is the week that contains January 4th, 
> which is the first week that includes at least four days of the year. If the 
> first Monday of the year is the 2nd, 3rd, or 4th, the preceding days are part 
> of the last week of the preceding year. For those days, %V is replaced by the 
> last week of the preceding year, and both %g and %G are replaced by the 
> digits of the preceding year.
>
> The above text has originally been taken from this documentation and 
> corrected regarding "%V is replaced by 53":
> https://docs.microsoft.com/de-de/cpp/c-runtime-library/reference/strftime-wcsftime-strftime-l-wcsftime-l?view=vs-2019

better docs:

http://man7.org/linux/man-pages/man3/strftime.3.html

;-)

>
> Thanks.
>
> Bye.
> --
> Reinhard Nißl
> reinhard.ni...@fee.de
>
> ___
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] feature request -- enhance strftime() implementing %V, %g and %G for week of year according to ISO 8601

[Nißl Reinhard]

Hi,

it would be nice, if sqlite3's strftime() would support the following 
formatting codes:
%g  The last 2 digits of the ISO 8601 week-based year as a decimal number 
(00 - 99)
%G  The ISO 8601 week-based year as a decimal number
%V  ISO 8601 week number as a decimal number (00 - 53)

The ISO 8601 week and week-based year produced by %V, %g, and %G, uses a week 
that begins on Monday, where week 1 is the week that contains January 4th, 
which is the first week that includes at least four days of the year. If the 
first Monday of the year is the 2nd, 3rd, or 4th, the preceding days are part 
of the last week of the preceding year. For those days, %V is replaced by the 
last week of the preceding year, and both %g and %G are replaced by the digits 
of the preceding year.

The above text has originally been taken from this documentation and corrected 
regarding "%V is replaced by 53":
https://docs.microsoft.com/de-de/cpp/c-runtime-library/reference/strftime-wcsftime-strftime-l-wcsftime-l?view=vs-2019

Thanks.

Bye.
--
Reinhard Nißl
reinhard.ni...@fee.de

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

Re: [sqlite] sqlite3: .import command handles quotation incorrectly

[Nißl Reinhard]

Hi,

BTW: I hate that TOFU posting, but Outlook doesn't allow me to do it any 
better. I'm sorry for that.

Your sample [123, 9" Nail, ", Caliper, "set, up 5"", hold", 8 ] gives currently 
the following column values (I used > and < to indicate the string bounderies, 
so that any white spaces are noticeable):

>123< | > 9" Nail, "< | > Caliper< | > "set, up 5"", hold"< | > 8 <

Given that your input has been stripped by the leading spaces after a column 
separator, or if the column separator has been defined as >, < you currently 
get:

>123< | >9" Nail, "< | >Caliper< | >set, up 5", hold< | >8 <

With my approach you still get:

>123< | >9" Nail, "< | >Caliper< | >set, up 5", hold< | >8 <

You expect to get:

>123< | >9 Nail, < | >Caliper< | >set, up 5", hold< | >8 <

But this is not what .import currently does.

To get that, .import would have to ignore every " besides when the cell is 
actually quoted.

This is my current suggestion for the code, dealing with splitting the row into 
columns and dealing with quoting (just from the editor, haven't tested it yet):

  char *z, c;
  int honorQuote = 1;
  int inQuote = 0;
  lineno++;
  azCol[0] = zLine;
  for(i=0, z=zLine; (c = *z)!=0; z++){
if( c=='"' ){
  if( honorQuote )
  {
inQuote = 1;
honorQuote = 0;
  }
  else if( inQuote ){
inQuote = 0;
honorQuote = 1;
  }
}
else
  honorQuote = 0;
if( c=='\n' ) lineno++;
if( !inQuote && c==p->separator[0] && strncmp(z,p->separator,nSep)==0 ){
  *z = 0;
  i++;
  if( imailto:sqlite-users-boun...@sqlite.org] 
Im Auftrag von RSmith
Gesendet: Mittwoch, 26. Juni 2013 14:48
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] sqlite3: .import command handles quotation incorrectly

Expanding on what Jay replied to:
> Hi,
>
> well we could discuss endlessly, what well formed CSV files are.
Not really, the rules are pretty set in stone, it isn't open to interpretation.


> Given that we cannot fix the generation of the CSV file, why not making the 
> importer a little bit smarter?
>
> The .import command already treats " as literal data, when it doesn't appear 
> at the beginning of the cell, but it requires an even number of " in the same 
> cell to don't get confused in breaking up the row into cells.
>
> All I ask for is to treat an odd number of " in a cell as literal data except 
> when the rule for dequoting applies, i. e. when the cell starts with a ".

This is impossible - How do you know where the end of a cell is if you are 
ignoring/counting Quotes???

For instance, what is the resulting fields from this CSV: [123, 9" Nail, ", 
Caliper, "set, up 5"", hold", 8 ]
In REAL CSV terms that would be:123  |  9 Nail,  | Caliper | set, up 5", 
hold  |  8

How would you want that to be interpreted?  Once we encounter the first quote, 
how do we know at which quote the field ends? How do 
we know how many quotes are in the field? Or do we just ignore quotes 
altogether and break on every comma - in which case the above 
becomes:   123  |  9" Nail  |  "  |  Caliper  |  "set  |  up 5""  | hold"  |  8
Surely it's easy to see how this is incorrect?
How about your other suggestion about taking fields starting with quotes to be 
quoted in pairs (even numbers) but otherwise ignoring 
odd numbered quotes - how do we know at which quote to stop counting?
Going strictly by your suggestion It might be interpreted as:  123 |  9" Nail  
|  , Caliper,  |  up 5""  |  hold"  |  8  |
or maybe even as:  123  |  9" Nail  |  , Caliper, set  |  up 5""  | hold"  |  8 
 |   (the interpreter would never be sure)

The interpreter working like this is not "a little bit smarter" but rather is 
very much non-conforming.

You have a very specific case of weirdly created data - I already offered help 
to give you an interpreter specific to it and will be 
happy to, but there is no way the SQlite can be altered like this, it will read 
all other CSV files wrong - and making a case 
specifc SQLite is possible (you can alter the C code and compile your own 
easily), but all future updates will need this adjustment. 
Submitting a patch will require your version to work on all other CSV file too 
- which as desribed above - it won't.

is there no way to fix the output? Or use an in-between step of converting the 
data before import from the weird layout to proper 
CSV? (we can help with that).

Have a great day!

___
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] sqlite3: .import command handles quotation incorrectly

[Nißl Reinhard]

Hi,

well we could discuss endlessly, what well formed CSV files are. Given that we 
cannot fix the generation of the CSV file, why not making the importer a little 
bit smarter?

The .import command already treats " as literal data, when it doesn't appear at 
the beginning of the cell, but it requires an even number of " in the same cell 
to don't get confused in breaking up the row into cells.

All I ask for is to treat an odd number of " in a cell as literal data except 
when the rule for dequoting applies, i. e. when the cell starts with a ".

Bye.

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
Im Auftrag von Petite Abeille
Gesendet: Dienstag, 25. Juni 2013 19:27
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] sqlite3: .import command handles quotation incorrectly


On Jun 25, 2013, at 11:19 AM, Nißl Reinhard  wrote:

> because it stays in quotation mode until it finds a further ", which is 
> incorrect. Quotation mode may only get activated when " appears at the 
> beginning of a column value.

Meh. check the recent "escape quote for csv import" thread.

As mentioned multiple time, by multiple people, on multiple occasions.


  7.  If double-quotes are used to enclose fields, then a double-quote
  appearing inside a field must be escaped by preceding it with
  another double quote.  For example:

  "aaa","b""bb","ccc"


http://tools.ietf.org/html/rfc4180
___
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] sqlite3: .import command handles quotation incorrectly

[Nißl Reinhard]

Hi,

for example, it cannot import the column value

19" rack

because it stays in quotation mode until it finds a further ", which is 
incorrect. Quotation mode may only get activated when " appears at the 
beginning of a column value.

Once the line has been broken into column values, the import command properly 
handles that rule when dequoting each column value before inserting the row 
into the table.

Besides this bug, it would be nice if the quotation character could be 
specified like the separator, so that de-/quoting can even be turned off it the 
file hasn't been created appropriately. E. g. sqlserver bulk import does not 
support quoting, hence the files may not be created with quoting turned on.

Mit freundlichen Grüßen / Best regards

Reinhard Nißl
Softwareentwicklung

F.EE GmbH Informatik + Systeme
Industriestraße 6e, 92431 Neunburg  v. W.
Phone  +49 9672 506-198
Fax  +49 9672 506-10198
E-Mail: reinhard.ni...@fee.de
Internet: www.fee.de - 
www.fee-systemhaus.de - 
www.factwork.de

Geschäftsführung: J. u. G. Fleischmann
Geschäftssitz: Neunburg v. W., Amtsgericht Amberg HRB 1290
Diese E-Mail ist allein für den bezeichneten Adressaten bestimmt. Sie kann 
rechtlich vertrauliche Informationen enthalten. Wenn Sie diese E-Mail 
irrtümlich erhalten haben, informieren Sie bitte unverzüglich den Absender per 
E-Mail und löschen Sie diese E-Mail von Ihrem Computer, ohne Kopien 
anzufertigen. Vielen Dank.
This email is for the exclusive use of the addressee. It may contain legally 
privileged information. If you have received this message in error, please 
notify the sender by email immediately and delete the message from your 
computer without making any copies. Thank you.

___
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

[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) ){
 /

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

[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

[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

[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

[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  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.

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

[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