Comment too, should be allowed to be added along with a new column using "Alter table t_name Add Column ... ".

Consider the following table:

Create table Loan
( id integer primary key
, Lender text -- Lending Company
, Principal Real
, Date text
, Period text
, Maturity_value Real -- The Amount to be returned
, Details text
);

The amount is compounded bimonthly instead of quarterly. After filling large amount of data over a period of time, the company decides to add Bimonthly rate of interest column and tries the following unsuccessful query:

Alter table Loan add column Interest Real -- bimonthly rate of interest;

Conclusion, many times comments in DDL are unavoidable hence, add column query should also allow adding Comment.

Thanks, but why not single line double hyphen comments?

Actually, purpose of adding comment in DDL is solved but I think both types of comments should be allowed. Such simple inabilities might give bad impression to SQLite.

I am new to this forum, do Documentation authors and SQLite developers read posts on this forum?

Yes, but not every second of the day...

OK, then they might fix this short coming.

OK, then they might fix this short coming.

Until you can demonstrate that it is a genuine shortcoming, it seems unlikely that this corner-case "must have" will be treated as a "must have." Given that /*...*/ works, there is a sensible workaround for your request which doesn't require any code changes. That option, combined with the fact that (to the best of my fallible recollection) nobody else has ever said that such comments in an ALTER statement are a "must", there would seem to be little justification/motivation for adding additional code for that specific capability for the benefit of a single user.

Personally, I build my tables in the host language, and put the comments there, thus avoiding bulking up the schema with extraneous text.

Until you can demonstrate that it is a genuine shortcoming

Then why were double hyphen comments introduced in SQLite since very old times? Why not remove this functionality completely?

Even if they were taken, they aren't saved as part of the schema, so the comment is lost - sometimes it would be nice to have additional comments at the end of a table that are part of the database, and not necessarily the code that created the database.

they aren't saved as part of the schema, so the comment is lost

Comments are saved as part of Schema but they are ignored by the parser when database is connected. When you browse SQLite_master table, you will see your saved comments in SQL column.

Also for a very large table definition it may be inconvenient to scroll back and forth to comment at end and code part above.

Then why were double hyphen comments introduced in SQLite since very old times? Why not remove this functionality completely?

Because double-hyphens are part of the SQL standard.

Because double-hyphens are part of the SQL standard.

Then this standard should be maintained to its full.

adding additional code for that specific capability for the benefit of a single user.

A matter or bug reported by single user doesn't mean that it is for his benefit only.

Sunny Saini, well spotted.

You can check development activity using this link:

https://www.sqlite.org/src/timeline

Note the reference to your Forum post.

For date: 2022-08-17 at time: 20:18, Dr. Hipp added:

In the ".dump" command of the CLI, if a schema statement ends with an unterminated comment, try to terminate that comment prior to appending the ";" at the end. Forum post d7be961c5c. (Leaf check-in: 96e112da user: drh tags: trunk)

If you wish to check the Documentation repository, use:

https://www.sqlite.org/docsrc/timeline

https://www.sqlite.org/src/timeline

https://www.sqlite.org/docsrc/timeline

Kevin Charles, Thank you for these links, I didn't know about this feature earlier.

I tried adding a new line to see if it would acknowledge the new line,

sqlite> create temp table t ( c text, v integer );
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> alter table t add column
   ...>   x blob -- comment
   ...> ;
Run Time: real 0.000 user 0.000000 sys 0.000000
Runtime error: error in table t after add column: incomplete input

apparently, the -- breaks the command. Interestingly enough, this works,

sqlite> create temp table t0
   ...> (
   ...>  c text, -- comment 0
   ...>  v integer -- comment 1
   ...> );
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite>

Just thinking out-loud.

While creating DDL, double hyphen comments always work but, not later on with new column addition.

The root of the problem is that the it is apparently wanted to "add a comment" by embedding the comment in a command to ALTER TABLE.

Clearly this cannot work. The comment would be stripped BEFORE the command is processed and therefore cannot be treated as a "text string" payload.

However, the handling is inconsistent. This should not be an error:

alter table t add column
  x blob -- comment
;

The comment should be stripped (it applies to the command ALTER TABLE and is not a payload string). That is, it should be processed exactly the same as:

alter table t add column
  x blob
;

This is why non-stripped comments work (/* ... */) -- they are merely payload.

Technically, stripped comments should be removed and not stored in the schema at all. However, a historical design decision decided that CREATE statements which create schema entries should just store the entire statement sans any processing.

This has led to a state of affairs where some want to treat comments as payload when they are not payload.

This is Syntax error, the comment must be closed with */

In Android SQLite v3.8.6 I tried it as:

create table foo ( id integer primary key ) without rowid /* Bobby Tables Was Here ;

Table was created correctly and even adding values work. But SQLite_master showed extra text that already existed after ";" of my editor window.

I am not a programmer but I think this should be easy to fix by developers for Alter table code part.

Fortunately, the non-malicious case when all comments are properly terminated is easy to fix.

1. In src/parse.y, when running the action for a complete CREATE statement, pParse->sLastToken will always be a semicolon token, either an organic one or an empty synthetic one generated by the tokeniser at the end of the input. So including it in the text to be processed is pointless.

2. In sqlite3AlterFinishAddColumn in src/alter.c, stripping trailing semicolons is unnecessary (see above), and stripping trailing whitespace could remove the terminating newline from a single-line comment. Kill that loop entirely.

Index: src/alter.c
==================================================================
--- src/alter.c
+++ src/alter.c
@@ -404,14 +404,10 @@
 
 
   /* Modify the CREATE TABLE statement. */
   zCol = sqlite3DbStrNDup(db, (char*)pColDef->z, pColDef->n);
   if( zCol ){
-    char *zEnd = &zCol[pColDef->n-1];
-    while( zEnd>zCol && (*zEnd==';' || sqlite3Isspace(*zEnd)) ){
-      *zEnd-- = '\0';
-    }
     /* substr() operations on characters, but addColOffset is in bytes. So we
     ** have to use printf() to translate between these units: */
     assert( IsOrdinaryTable(pTab) );
     assert( IsOrdinaryTable(pNew) );
     sqlite3NestedParse(pParse, 

Index: src/parse.y
==================================================================
--- src/parse.y
+++ src/parse.y
@@ -1673,11 +1673,11 @@
 cmd ::= ALTER TABLE fullname(X) RENAME TO nm(Z). {
   sqlite3AlterRenameTable(pParse,X,&Z);
 }
 cmd ::= ALTER TABLE add_column_fullname
         ADD kwcolumn_opt columnname(Y) carglist. {
-  Y.n = (int)(pParse->sLastToken.z-Y.z) + pParse->sLastToken.n;
+  Y.n = (int)(pParse->sLastToken.z-Y.z);
   sqlite3AlterFinishAddColumn(pParse, &Y);
 }
 cmd ::= ALTER TABLE fullname(X) DROP kwcolumn_opt nm(Y). {
   sqlite3AlterDropColumn(pParse, X, &Y);
 }

That should have been "a complete CREATE or ALTER statement", of course. I blame my keyboard.

For me the most problematic part of double-hyphen comments is that they cannot refactor.

It's often needed to reformat a query removing line-breaks or copying between windows and unix systems which treat line-breaks different.

There is no path to go from:

CREATE TABLE t(
  AA TEX, -- Col1
  BB      -- Col2
);

into

CREATE TABLE t(AA TEX, -- Col1  BB      -- Col2);

which will always fail, and there is no algorithm for mending it. Whereas this example works in all formats:

CREATE TABLE t(
  AA TEX, /* Col1 */
  BB      /* Col2 */
);

and

CREATE TABLE t(AA TEX, /* Col1 */  BB   /* Col2 */);

works perfectly just the same.

Oh, so it appears it will be difficult for developers to fix it

Very useful tip.

I am sure I tried ths before and encountered errors; glad to have your example.

When I want to store DDL related comments, I define a meta table

CREATE TABLE table_meta (
    table_name TEXT NOT NULL COLLATE NOCASE,
    column_name TEXT NOT NULL COLLATE NOCASE,
    comment TEXT NOT NULL COLLATE NOCASE
)

and store the necessary information in this table. No problems with alterations or whatever else. Simply always follow this approach and your problem should be solved.

+1. Well, not this precise thing. More the underlying concept.

If you want to store something in a SQLite database, make a table for it and put in in the table. Don't rely on a weird, non-standard, barely-documented feature which preserves or deletes stuff as it wishes.

NOTE: After storing a comment in a table, use ALTER TABLE ADD COLUMN … and see what happens.

Actually, double hyphen comments is a standard, it only requires small improvement.

This is good idea.