Item14849: Strange formatted query results when match occurs in start of field names like time and timeStamp

Hi there, I am using SqlPlugin (09 Sep 2016, 3.03).

I got a unexpected result when formatting an %SQL query with:

format="|$tank|$timeStamp|$level|$time|"

I tracked the problem down to the retrieval of the hash keys in

Core.pm line 274: foreach my $key (keys %$res) {

The hash does not guarantee the order of retrieval of the keys. And hence sometimes $time will be retrieved and substituted before $timeStamp leading to the result: hh:mmStamp. The issue is resolved by reverse sorting the keys, so that longer keys come before shorter keys and hence $timeStamp is always substituted before $time.

Patch follows:

diff --git a/lib/Foswiki/Plugins/SqlPlugin/Core.pm b/lib/Foswiki/Plugins/SqlPlugin/Core.pm
index d766a14..41ddb0b 100644
--- a/lib/Foswiki/Plugins/SqlPlugin/Core.pm
+++ b/lib/Foswiki/Plugins/SqlPlugin/Core.pm
@@ -271,10 +271,10 @@ sub formatResult {
       next if $theSkip && $index <= $theSkip;
       my $line = $theFormat;
 
-      foreach my $key (keys %$res) {
+      foreach my $key (reverse sort keys %$res) {
         my $val = $res->{$key} || '';
         $line =~ s/\$index/$index/g;
-        $line =~ s/\$\Q$key/$val/g;
+        $line =~ s/\$\Q$key\E/$val/g;
       }
       push @lines, $line;
       last if $theLimit && $index >= $theLimit;

-- BramVanOosterhout - 02 Aug 2019

OIC. But then it should not depend on any key order as the one you are enforcing could lead to other problems on some other column names.

Best would be to use something like

$line =~ s/\$\Q$key\E\b/$val/g;

Could you try that and not sort keys? Thanks.

-- MichaelDaum - 02 Aug 2019

Hi Michael, Ah, very nice. That works for me. Here is the patch.

diff --git a/lib/Foswiki/Plugins/SqlPlugin/Core.pm b/lib/Foswiki/Plugins/SqlPlugin/Core.pm
index d766a14..f3ee118 100644
--- a/lib/Foswiki/Plugins/SqlPlugin/Core.pm
+++ b/lib/Foswiki/Plugins/SqlPlugin/Core.pm
@@ -274,7 +274,7 @@ sub formatResult {
       foreach my $key (keys %$res) {
         my $val = $res->{$key} || '';
         $line =~ s/\$index/$index/g;
-        $line =~ s/\$\Q$key/$val/g;
+        $line =~ s/\$\Q$key\E\b/$val/g;
       }
       push @lines, $line;
       last if $theLimit && $index >= $theLimit;

Also changed the summary to reflect the problem.

-- BramVanOosterhout - 02 Aug 2019

Commenting is disabled while not logged in