I've added an 'age' field for one of my ticket reports:

SELECT
CASE WHEN status IN ('Open','Verified') THEN '#f2dcdc'
       WHEN status='Review' THEN '#e8e8e8'
       WHEN status='Fixed' THEN '#cfe8bd'
       WHEN status='Tested' THEN '#bde5d6'
       WHEN status='Deferred' THEN '#cacae5'
       ELSE '#c8c8c8' END AS 'bgcolor',
  substr(tkt_uuid,1,10) AS '#',
  datetime(tkt_mtime) AS 'mtime',
  CAST ((julianday('now')-tkt_ctime) AS INTEGER) AS 'age',
  type,
  status,
  subsystem,
  title
FROM ticket
WHERE status in ('Open','Review','Verified')
ORDER BY mtime DESC

This shows me how many days old a ticket is.

When I click on the 'age', it changes the order of the tickets, but the result is not 'correct' in descending or ascending 'age' as I expect.

In fact, now I notice that it never really sorts correctly. The same colors seem to be grouped together.

This is with Fossil 2.18 [84f25d7eb1] 2022-02-23 13:22:20

Arrgh. It turns out sorting is alpha and not numeric, so I have to convert the number to a string. But strings are sorted lexicographically, so the numbers don't show up correctly.

The answer I hit upon was to do:

printf( '%04d' , (julianday('now')-tkt_ctime)) AS 'age'

I actually wanted space-padded numbers but couldn't get the printf to do what I needed.

According to the docs, I think you just need to drop the 0 from that format specifier.

If you meant left-justified values instead, then it'd be %-4d:

$ sqlite3 '' "select printf('[%4d]', 123), printf('[%-4d]', 123)" 
┌──────────────────────┬───────────────────────┐
│ printf('[%4d]', 123) │ printf('[%-4d]', 123) │
├──────────────────────┼───────────────────────┤
│ [ 123]               │ [123 ]                │
└──────────────────────┴───────────────────────┘

That is indeed what I expected to work, e.g. "printf('%4d'...)" but it doesn't seem to work within the SELECT. At least, the strings produced are still left-justified, and without leading spaces.

Trying it from the sqlite3 CLI, it works as expected (e.g. printf() does). I suspect some trimming is happening on the field internally?

I mean to say, not within the SELECT of the report.

I've given the code a quick look, and the core problem is that data-column-types in the generated HTML is set to a blank string in rptview_page_content() within src/report.c, so src/sorttable.js defaults to an ASCII sort for all columns.

I think you'd need to extract the SQLite type affinities from the query and set this value appropriately. Fixing this will require modifying Fossil. Patches thoughtfully considered.

That explains the sorting issue, but doesn't explain the printf() not producing a properly spaced string in the report. Could that be the js? I'll take a quick look there...

There is no rendering difference in HTML between "<td> 123</td>" and "<td>123 </td>". If you want right-aligned values, you either have to style them thus with CSS or use non-breaking space characters.

Oof. You're right, I forgot about that. But the raw values in the table cells should still be e.g. ' 12' and the sort should work then.

But yeah, you're right also that the 'data-column-types' needs to be fixed for it to 'just work' without all the nonsense I'm going through.

If HTML worked the way you wished, you'd get a newline and leading whitespace with this:

    <td>
         My cell data
    </td>

It strips leading and trailing whitespace on purpose.

I understand that, but the value of the cell should still be " 12" and not "12", regardless of how the HTML renders. So then the alpha sort should work on numbers.

But that isn't what happens.

Are you sure you aren’t just running into the classic case of 123 sorting ASCIIbetically before 23?

sigh

Yes, that's exactly what I'm running into. That is precisely the point.

My "solution" to the problem was to say, "OK, if I left-pad the strings being sorted, they'll sort correctly despite being numbers-sorting-ASCIIbetically". That's why I looked for a " 12" format.

My solution would be correct, but for the fact that for some reason, the actual data getting sorted are trim()ed so that my formatted " 12" becomes "12" and therefore, the sort cannot be correct with numbers.

There are (at least) two problems:

1. The column type is incorrectly set by the back-end, so that even though the column initially was CAST to INTEGER, it is still treated as if it were text.
2. Despite returning a string padded-left with spaces, somewhere in the flow from backend-through-HTML-to-JS the text is trimmed so the leading spaces are removed.

I had initially thought that #2 was due to a defect in the printf() function, but you correctly pointed out that HTML rendering is whitespace-stripping.

HTML rendering is whitespace-stripping

Also, from sorttable.js:

  this.sortText = function(a,b) {
    var i = thisObject.sortIndex;
    aa = a.cells[i].textContent.replace(/^\W+/,'').toLowerCase();
    ...

That means non-word characters at the start of the text are removed before the comparison.

Would changing that have an adverse effect?

I suppose the idea is that "-this" and "this" should sort together, but hmm...

That's debatable, but I don't see how that bears on the primary matter: this column should be marked as numeric so you don't have to depend on cutesy whitespace handling to make it sort as intended.

Yes, agreed; but this is also an undesired 'feature' IMO.

Why? The stock reports have columns that are either:

• Text, so the default is precisely correct.
• Hash prefixes, so they sort ASCIIbetically correctly
• ISO 8601 dates, so ditto

It's only your customization that caused the problem.

In the time you've spent arguing about this, you could've coded a solution.

And I have just finished coding one against the current trunk of Fossil.

Index: src/report.c
==================================================================
--- src/report.c
+++ src/report.c
@@ -703,10 +703,55 @@
   int iBg;         /* Index of column that defines background color */
   int wikiFlags;   /* Flags passed into wiki_convert() */
   const char *zWikiStart;    /* HTML before display of multi-line wiki */
   const char *zWikiEnd;      /* HTML after display of multi-line wiki */
 };
+
+/*
+ ** Get the column information:
+ */
+struct ColumnInfo {
+  int nCol;        /* Number of columns */
+  char * zInfo;    /* Column type information */
+};
+
+/*
+** The callback function for db_query to get the column types, only
+*/
+static int dont_generate_html(
+  void *pUser,     /* Pointer to output state */
+  int nArg,        /* Number of columns in this result row */
+  const char **azArg, /* Text of data in all columns */
+  const char **azName /* Names of the columns */
+){
+	int i;
+	int iType;
+	struct ColumnInfo * info = (struct ColumnInfo *) pUser;
+	sqlite3_stmt * pStmt = sqlite3_next_stmt(g.db, NULL);
+	info->nCol = nArg;
+	info->zInfo = (char *) fossil_malloc_zero(nArg+1);
+
+	for (i=0; i<nArg; i++) {
+		iType = sqlite3_column_type(pStmt, i+1);
+		switch (iType) {
+			case SQLITE_INTEGER:
+			case SQLITE_FLOAT:
+				info->zInfo[i] = 'n';
+				break;
+			case SQLITE_BLOB:
+			case SQLITE_NULL:
+				info->zInfo[i] = 'x';
+				break;
+			default:
+				info->zInfo[i] = 't';
+				break;
+		}
+	}
+
+	/* don't do any more work */
+	return 1;
+}
 
 /*
 ** The callback function for db_query
 */
 static int generate_html(
@@ -1079,10 +1124,11 @@
   }
 
   count = 0;
   if( !tabs ){
     struct GenerateHTML sState = { 0, 0, 0, 0, 0, 0, 0, 0, 0 };
+	struct ColumnInfo sColInfo = { 0, 0 };
     const char *zQS = PD("QUERY_STRING","");
 
     db_multi_exec("PRAGMA empty_result_callbacks=ON");
     style_set_current_feature("report");
     if( pageWrap ) {
@@ -1104,17 +1150,33 @@
       if( g.perm.NewTkt ){
         style_submenu_element("New Ticket", "%R/tktnew");
       }
       style_header("%s", zTitle);
     }
-    output_color_key(zClrKey, 1,
-        "border=\"0\" cellpadding=\"3\" cellspacing=\"0\" class=\"report\"");
-    @ <table border="1" cellpadding="2" cellspacing="0" class="report sortable"
-    @  data-column-types='' data-init-sort='0'>
+    sColInfo.nCol = 0;
+    sColInfo.zInfo = 0;
+    report_restrict_sql(&zErr1);
+    db_exec_readonly(g.db, zSql, dont_generate_html, &sColInfo, &zErr2);
+	if (sColInfo.zInfo) {
+		output_color_key(zClrKey, 1,
+			"border=\"0\" cellpadding=\"3\" cellspacing=\"0\" class=\"report\"");
+		cgi_printf(
+				"<table border='1' cellpadding='2' cellspacing='0' class='report sortable' data-column-types='%s' data-init-sort='0'>",
+				sColInfo.zInfo
+				);
+
+		fossil_free(sColInfo.zInfo);
+	} else {
+		output_color_key(zClrKey, 1,
+			"border=\"0\" cellpadding=\"3\" cellspacing=\"0\" class=\"report\"");
+		@ <table border="1" cellpadding="2" cellspacing="0" class="report sortable"
+		@  data-column-types='' data-init-sort='0'>
+	}
+
+
     sState.rn = rn;
     sState.nCount = 0;
-    report_restrict_sql(&zErr1);
     db_exec_readonly(g.db, zSql, generate_html, &sState, &zErr2);
     report_unrestrict_sql();
     @ </tbody></table>
     if( zErr1 ){
       @ <p class="reportError">Error: %h(zErr1)</p>