GNU bug report logs - #26029
Problems with join

Previous Next

To add a comment to this bug, you must first unarchive it, by sending
a message to control AT debbugs.gnu.org, with unarchive 26029 in the body.
You can then email your comments to 26029 AT debbugs.gnu.org in the normal way.

Toggle the display of automated, internal messages from the tracker.

Message #5 received at submit <at> debbugs.gnu.org (full text, mbox):

From: "Peter Kluge" <linux-projekt <at> techno.ms>
To: bug-coreutils <at> gnu.org
Subject: Problems with join
Date: Wed, 8 Mar 2017 18:39:07 +0100

[Message part 1 (text/html, inline)]

[join-script (application/octet-stream, attachment)]

[sortierte-Zeiten (application/octet-stream, attachment)]

[teilnehmer (application/octet-stream, attachment)]

Message #8 received at 26029 <at> debbugs.gnu.org (full text, mbox):

From: Reuti <reuti <at> staff.uni-marburg.de>
To: Peter Kluge <linux-projekt <at> techno.ms>
Cc: 26029 <at> debbugs.gnu.org
Subject: Re: bug#26029: Problems with join
Date: Wed, 8 Mar 2017 19:17:14 +0100

[Message part 1 (text/plain, inline)]

Hi,

> Am 08.03.2017 um 18:39 schrieb Peter Kluge <linux-projekt <at> techno.ms>:
> 
>   Hello Mike Haertel,
> 
>   I've a problem with the join-command
>   2 Files , compare-field  in one file first in the other second filed.
>   Files attached
> 
>   I want as output to have the compare-file and then the rest-content of
>   file_1 followed by the rest-content of file-2
>   Doesn't work. See Script. (also attached)

AFAICS you will need two joins to get the desired output. Otherwise the unpairable records are interleaved in the output. Although getting the unpairable records in a second invocation will always have a fixed order, it's by accident the one you request (add "-v 1 -v 2" in the second invocation).

The strange thing seems to be, that "-j1 2" is handled like "-1 2". "-j 1" should already have an option and "2" would then become the first filename making the last filename in the command line superfluous.

-- Reuti

[signature.asc (application/pgp-signature, attachment)]

Message #11 received at 26029 <at> debbugs.gnu.org (full text, mbox):

From: Bernhard Voelker <mail <at> bernhard-voelker.de>
To: Peter Kluge <linux-projekt <at> techno.ms>, 26029 <at> debbugs.gnu.org
Subject: Re: bug#26029: Problems with join
Date: Thu, 9 Mar 2017 00:06:43 +0100

tag 26029 notabug
close 26029
stop

On 03/08/2017 06:39 PM, Peter Kluge wrote:
>    Hello Mike Haertel,
> 
>    I've a problem with the join-command
>    2 Files , compare-field  in one file first in the other second filed.
>    Files attached
> 
>    I want as output to have the compare-file and then the rest-content of
>    file_1 followed by the rest-content of file-2
>    Doesn't work. See Script. (also attached)
> 
>    My mistake?

As Reuti said, join(1) can not print matching lines first and then non-matching
ones in one go.  But if you wouldn't mind if the ones with missing entries
are interleaved, then you could do it like this:

  $ join -t: -1 1 -2 2 -e '--' -a 1 -a 2 -o 0,2.1,1.1,1.2,1.3,1.4,1.5,1.6  teilnehmer sortierte-Zeiten
  101:3h32'27:101: Willi Wild: Waltraud Weisnix: Mercedes:A:Profi
  102:2h55'11:102: Hans Hirnlos: Nici Blondie: Hummer: D:Profi
  104:4h05'00:104: Klaus Knall: Siegfried Schmerzfrei: Kia:D:Profi
  105:2h43'19:105: Georg Schnell: Dieter Durchblick: Mitsubishi:TN:Profi
  106:3h02'58:106: Stanislaw Oswandrik: Andrujek Jerin: Bolder:PL:Profi
  109:4h15'39:109: Richard Reich: Evi Schimmerlos: BMW:D:Profi
  113:5h05'41:--:--:--:--:--:--
  201:4h18'31:201: Gunther Genuegsam: Anne Sorglos: Suzuki:A: Amateur
  206:3h45'48:206: Oswald Ossi: Lars Ossi: Lada:CZ:Amateur
  210:--:210: Kurt Schrotti: NN: Range Rover:D:Amateuer
  213:3h49'59:213: Peter Kluge: Benjamin Kluge : Toyota:D:Amateuer
  215:7h20'00:215: Werner Weglos: Paul Planlos: Eigenbau:A:Amateur
  217:3h38'28:217: Otto Ohneplan:i Egon Egal: VW:D:Amateur
  222:5h10'05:222: Pit Profil: Jerry Thunder: Range Rover:GB:Amateur

The arguments "-a 1" and "-a 2" tell join to output also non-matching
lines, and "-e '--'" defines to print the string "--" for the fields
of non-matching columns.  In this case, you also need a more sophisticated
"-o" definition than "auto".

As this is not a bug in the program, but a wrong expectation of how the
tool works, I'm marking this issue as such in the bug tracker.  Of course,
you're still welcome to continue the discussion here.

Have a nice day,
Berny

Message #14 received at 26029 <at> debbugs.gnu.org (full text, mbox):

From: Reuti <reuti <at> staff.uni-marburg.de>
To: Peter Kluge <linux-projekt <at> techno.ms>
Cc: 26029 <at> debbugs.gnu.org
Subject: Re: bug#26029: Problems with join
Date: Thu, 9 Mar 2017 11:57:07 +0100

[Message part 1 (text/plain, inline)]

Hi,

> Am 08.03.2017 um 19:17 schrieb Reuti <reuti <at> staff.uni-marburg.de>:
> 
> […]
> The strange thing seems to be, that "-j1 2" is handled like "-1 2". "-j 1" should already have an option and "2" would then become the first filename making the last filename in the command line superfluous.

My investigations revealed: on a Mac the man page of `join` explains the behavior. The options -j, -j1 and -j2 are listed with the BSD version of `join` as being there for compatibility. This leads to the assumption, that nowadays -1 and -2 should better be used.

The coreutils version of `join` doesn't explain -j1 or -j2 anywhere AFAICS (but the `join` application covers them as special cases in the source). Neither on the man page, nor the info page it's mentioned. At least this should be noted somewhere. Personally I find it confusing, that "-j 3" is like "-j3", but "-j1" and -"j2" are special and need an argument. The info page even lists "-j3" in the examples (no intervening blank). It would be good if there would be a hint of this trap on the man page, as the plain statment:

       -j FIELD
              equivalent to '-1 FIELD -2 FIELD'

does not work in all cases essentially. (One could argue, that no one will use "-j1" standing alone and expecting it to work as "-j 1" as it's the default anyway, but at least "-j2" might give some surprise.)

-- Reuti

[signature.asc (application/pgp-signature, attachment)]

Message #17 received at 26029 <at> debbugs.gnu.org (full text, mbox):

From: Reuti <reuti <at> staff.uni-marburg.de>
Cc: 26029 <at> debbugs.gnu.org
Subject: Re: bug#26029: Problems with join
Date: Thu, 9 Mar 2017 17:29:13 +0100

[Message part 1 (text/plain, inline)]

Hi,

> Am 09.03.2017 um 16:32 schrieb Peter Kluge <linux-projekt <at> techno.ms>:
> 
> thanks for the explanation.
> I prefer the "POSIX"-Standard teaching to my participants.

Aha, I didn't check this. Then the "-j" option should be moved to a new section "Deprecated" in the man/info page of the coreutils version too. (And mention the special handling of -j1 resp. -j2, while -j3 … works as one expects.)


> […]
> but it will not work with my files
> peter <at> SLES11-3:~/kluge/join> cat tn
> 101: Willi Wild: Waltraud Weisnix: Mercedes:A:Profi
> 102: Hans Hirnlos: Nici Blondie: Hummer: D:Profi
> peter <at> SLES11-3:~/kluge/join> cat sz
> 3h32'27:101:
> 2h55'11:102:
> peter <at> SLES11-3:~/kluge/join> join  -t:  -j2 2  tn  sz
> :3h32'27:  Wild: Waltraud Weisnix: Mercedes:A:Profi
> :2h55'11: Hirnlos: Nici Blondie: Hummer: D:Profi
> peter <at> SLES11-3:~/kluge/join> join  -t:   -2 2  tn  sz
> :3h32'27:  Wild: Waltraud Weisnix: Mercedes:A:Profi
> :2h55'11: Hirnlos: Nici Blondie: Hummer: D:Profi
> peter <at> SLES11-3:~/kluge/join> join  -t:  -1  2  sz  tn
> : Willi Wild: Waltraud Weisnix: Mercedes:A:Profi
> : Hans Hirnlos: Nici Blondie: Hummer: D:Profi
> peter <at> SLES11-3:~/kluge/join>

For me it's working:

$ join  -t:  -1  2  sz  tn
101:3h32'27:: Willi Wild: Waltraud Weisnix: Mercedes:A:Profi
102:2h55'11:: Hans Hirnlos: Nici Blondie: Hummer: D:Profi

$ join --version
join (GNU coreutils) 8.17

What version do you have in SLES11-3?

-- Reuti

[signature.asc (application/pgp-signature, attachment)]

Message #20 received at 26029 <at> debbugs.gnu.org (full text, mbox):

From: Assaf Gordon <assafgordon <at> gmail.com>
To: Reuti <reuti <at> staff.uni-marburg.de>
Cc: 26029 <at> debbugs.gnu.org
Subject: Re: bug#26029: Problems with join
Date: Thu, 9 Mar 2017 17:20:43 +0000

Hello Reuti and all,

Reuti wrote:
>> […] The strange thing seems to be, that "-j1 2" is handled like "-1
>> 2". 
>
> My investigations revealed: on a Mac the man page of `join` explains
> the behavior. The options -j, -j1 and -j2 are listed with the BSD
> version of `join` as being there for compatibility. This leads to the
> assumption, that nowadays -1 and -2 should better be used.

Thanks for investigating and pointing this out!

Join's manual section was recently expanded, I wish I was aware of 
this nuance before I wrote the patch. I will send a patch with 
improved documentation.

On Thu, Mar 09, 2017 at 05:29:13PM +0100, Reuti wrote:

Reuti wrote:
>> Am 09.03.2017 um 16:32 schrieb Peter Kluge <linux-projekt <at> techno.ms>:
>>
>> I prefer the "POSIX"-Standard teaching to my participants.
>
>Aha, I didn't check this. Then the "-j" option should be moved to a new section "Deprecated" in the man/info page of the coreutils version too. (And mention the special handling of -j1 resp. -j2, while -j3 … works as one expects.)

I would humbly suggest other wording: I'm not sure '-j' is deprecated.
It is useful, and does work as expected in most cases.

But, it should be better documented to warn against this edge-case.

Reuti wrote:
> -j FIELD equivalent to '-1 FIELD -2 FIELD'
> 
> does not work in all cases essentially.

It 'just works' in most cases, but indeed we should improve the 
documentation about edge cases.

First,
this is the relevant section that handles the '-j' parameter:
https://git.savannah.gnu.org/cgit/coreutils.git/tree/src/join.c#n1079


Second,
Let's ensure '-jN' works in the common cases,
when it is *not* followed by a number:

Two input files:

   $ cat a.txt
   1 2 3 aaa
   2 3 4 bbb

   $ cat b.txt
   1 2 3 XXX
   2 3 4 YYY

'-j1' alone is equivalent to '-1 1 -2 1':

   $ join -1 1 -2 1 a.txt b.txt
   1 2 3 aaa 2 3 XXX
   2 3 4 bbb 3 4 YYY

   $ join -j1 a.txt b.txt
   1 2 3 aaa 2 3 XXX
   2 3 4 bbb 3 4 YYY

'-j2' alone is equivalent to '-1 2 -2 2':

   $ join -1 2 -2 2 a.txt b.txt
   2 1 3 aaa 1 3 XXX
   3 2 4 bbb 2 4 YYY

   $ join -j2 a.txt b.txt
   2 1 3 aaa 1 3 XXX
   3 2 4 bbb 2 4 YYY

'-j3' alone is equivalent to '-1 3 -2 3':

   $ join -1 3 -2 3 a.txt b.txt
   3 1 2 aaa 1 2 XXX
   4 2 3 bbb 2 3 YYY

   $ join -j3 a.txt b.txt
   3 1 2 aaa 1 2 XXX
   4 2 3 bbb 2 3 YYY

So, in the most common cases, '-jN' works for all Ns
(for "all" being 1,2,3 but really, who needs more than 3 numbers? :) ).
This is perhaps not like BSD's join.


Now comes the tricky part:
If the '-j1' or '-j2' is followed by another parameter,
and that parameter turns out *not* to be an valid field number,
It is treated like '-j 1' (or '-1 1 -2 1'), and join just "does the 
right thing":

   $ join -j2 -i a.txt b.txt
   2 1 3 aaa 1 3 XXX
   3 2 4 bbb 2 4 YYY

This is implemented here:
https://git.savannah.gnu.org/cgit/coreutils.git/tree/src/join.c#n1171
And the result is that most of the time, join "just works" (IMHO, but
other opinions welcomed).


If the '-j1' or '-j2' is followed by a number, this is were the 
unexpected behaviour occurs, as it sets the key field for that file 
alone. E.g. '-j1 2' is equivalent to '-1 2' (and the key for the second
file is not set, thus defaults to 1):

   $ join -j1 2 a.txt b.txt
   2 1 3 aaa 3 4 YYY

   $ join -1 2 a.txt b.txt
   2 1 3 aaa 3 4 YYY


Is the above a satisfactory explanation?
If so, it'll be more-or-less what I'll add to the manual.

I see that this has been implemented back in 2005, here:
https://git.savannah.gnu.org/cgit/coreutils.git/commit/src/join.c?id=f9118c1c2e35b
with the comment:
 "Parse obsolete options -j1 and -j2
  so that it is a pure extension to POSIX 1003.1-2001."

I can perhaps guestimate that since this usage is never
mentioned anywhere, it is considered undocumented and discouraged usage
(and indeed, I don't think I've ever encountered it, or previously
saw a bug-report or question about it - so it's rather rare).

We could add a warning to the man page - what do others think?

regards,
- assaf

Message #23 received at 26029 <at> debbugs.gnu.org (full text, mbox):

From: Reuti <reuti <at> staff.Uni-Marburg.DE>
To: Assaf Gordon <assafgordon <at> gmail.com>
Cc: 26029 <at> debbugs.gnu.org, Peter Kluge <linux-projekt <at> techno.ms>
Subject: Re: bug#26029: Problems with join
Date: Thu, 9 Mar 2017 19:24:40 +0100

[Message part 1 (text/plain, inline)]

Hi,

> Am 09.03.2017 um 18:20 schrieb Assaf Gordon <assafgordon <at> gmail.com>:
> 
>> […]
>> Aha, I didn't check this. Then the "-j" option should be moved to a new section "Deprecated" in the man/info page of the coreutils version too. (And mention the special handling of -j1 resp. -j2, while -j3 … works as one expects.)
> 
> I would humbly suggest other wording: I'm not sure '-j' is deprecated.
> It is useful, and does work as expected in most cases.

It's only mentioned in the addendum here:


http://pubs.opengroup.org/onlinepubs/9699919799//utilities/join.html

"Earlier versions  of  this  standard  allowed  -j, -j1, -j2 options, and a form of the -o option that allowed the list option-argument to be multiple arguments. These forms are  no longer specified by POSIX.1-2008 but may be present in some implementations.
…
The obsolescent -j options and the multi-argument -o option are removed in this version."


Therefore I still favor to move "-j" at the end of the man page in a separate section, also taking:

Q15: http://www.opengroup.org/austin/papers/posix_faq.html

into account.


> 
> But, it should be better documented to warn against this edge-case.
> 
> Reuti wrote:
>> -j FIELD equivalent to '-1 FIELD -2 FIELD'
>> does not work in all cases essentially.
> 
> It 'just works' in most cases, but indeed we should improve the documentation about edge cases.
> 
> First,
> this is the relevant section that handles the '-j' parameter:
> https://git.savannah.gnu.org/cgit/coreutils.git/tree/src/join.c#n1079

Yep, this I checked in the source too.


> 
> Second,
> Let's ensure '-jN' works in the common cases,
> when it is *not* followed by a number:
> 
> Two input files:
> 
>   $ cat a.txt
>   1 2 3 aaa
>   2 3 4 bbb
> 
>   $ cat b.txt
>   1 2 3 XXX
>   2 3 4 YYY
> 
> '-j1' alone is equivalent to '-1 1 -2 1':
> 
>   $ join -1 1 -2 1 a.txt b.txt
>   1 2 3 aaa 2 3 XXX
>   2 3 4 bbb 3 4 YYY
> 
>   $ join -j1 a.txt b.txt
>   1 2 3 aaa 2 3 XXX
>   2 3 4 bbb 3 4 YYY
> 
> '-j2' alone is equivalent to '-1 2 -2 2':
> 
>   $ join -1 2 -2 2 a.txt b.txt
>   2 1 3 aaa 1 3 XXX
>   3 2 4 bbb 2 4 YYY
> 
>   $ join -j2 a.txt b.txt
>   2 1 3 aaa 1 3 XXX
>   3 2 4 bbb 2 4 YYY
> 
> '-j3' alone is equivalent to '-1 3 -2 3':
> 
>   $ join -1 3 -2 3 a.txt b.txt
>   3 1 2 aaa 1 2 XXX
>   4 2 3 bbb 2 3 YYY
> 
>   $ join -j3 a.txt b.txt
>   3 1 2 aaa 1 2 XXX
>   4 2 3 bbb 2 3 YYY
> 
> So, in the most common cases, '-jN' works for all Ns
> (for "all" being 1,2,3 but really, who needs more than 3 numbers? :) ).
> This is perhaps not like BSD's join.
> 
> 
> Now comes the tricky part:
> If the '-j1' or '-j2' is followed by another parameter,
> and that parameter turns out *not* to be an valid field number,
> It is treated like '-j 1' (or '-1 1 -2 1'), and join just "does the right thing":
> 
>   $ join -j2 -i a.txt b.txt
>   2 1 3 aaa 1 3 XXX
>   3 2 4 bbb 2 4 YYY
> 
> This is implemented here:
> https://git.savannah.gnu.org/cgit/coreutils.git/tree/src/join.c#n1171

Aha, I didn't spot this. That's really tricky. I only observed the changing error message complaining about the remaining arguments depending on removing and adding an additional field number. And in case the filename is just a number it's even getting more convoluted, as also the overall number of arguments come into play then.

$ join -j1 1 2

generates no error, although -j1 got  a 1, but it predicts that it must be the name of a file, as otherwise one argument would be missing on the command line AFAICS.


> And the result is that most of the time, join "just works" (IMHO, but
> other opinions welcomed).
> 
> 
> If the '-j1' or '-j2' is followed by a number, this is were the unexpected behaviour occurs, as it sets the key field for that file alone. E.g. '-j1 2' is equivalent to '-1 2' (and the key for the second
> file is not set, thus defaults to 1):
> 
>   $ join -j1 2 a.txt b.txt
>   2 1 3 aaa 3 4 YYY
> 
>   $ join -1 2 a.txt b.txt
>   2 1 3 aaa 3 4 YYY
> 
> 
> Is the above a satisfactory explanation?

Yes, absolutely.


> If so, it'll be more-or-less what I'll add to the manual.
> 
> I see that this has been implemented back in 2005, here:
> https://git.savannah.gnu.org/cgit/coreutils.git/commit/src/join.c?id=f9118c1c2e35b
> with the comment:
> "Parse obsolete options -j1 and -j2
>  so that it is a pure extension to POSIX 1003.1-2001."
> 
> I can perhaps guestimate that since this usage is never
> mentioned anywhere, it is considered undocumented and discouraged usage
> (and indeed, I don't think I've ever encountered it, or previously
> saw a bug-report or question about it - so it's rather rare).
> 
> We could add a warning to the man page - what do others think?

+1

-- Reuti

[signature.asc (application/pgp-signature, attachment)]

Message #26 received at 26029 <at> debbugs.gnu.org (full text, mbox):

From: Assaf Gordon <assafgordon <at> gmail.com>
To: Reuti <reuti <at> staff.Uni-Marburg.DE>
Cc: 26029 <at> debbugs.gnu.org, Peter Kluge <linux-projekt <at> techno.ms>
Subject: Re: bug#26029: Problems with join
Date: Fri, 31 Mar 2017 03:54:19 +0000

[Message part 1 (text/plain, inline)]

Hello,

Attached a small patch mentioning the obsolete '-j1 N' syntax.

Becuse it is rather rare, I only added it to the texinfo
manual, not to the man-page (or --help screen).

I'm 50:50 about whether this justifies an entire section in the manual,
or perhaps it'll be enough to mention it in the 'gotcha' page.

Comments welcomed,
- assaf

[0001-doc-mention-obsolete-join-j1-j2-syntax.patch (text/x-diff, attachment)]

Message #29 received at 26029 <at> debbugs.gnu.org (full text, mbox):

From: Paul Eggert <eggert <at> cs.ucla.edu>
To: Assaf Gordon <assafgordon <at> gmail.com>, Reuti <reuti <at> staff.Uni-Marburg.DE>
Cc: 26029 <at> debbugs.gnu.org, Peter Kluge <linux-projekt <at> techno.ms>
Subject: Re: bug#26029: Problems with join
Date: Thu, 30 Mar 2017 21:39:24 -0700

On 03/30/2017 08:54 PM, Assaf Gordon wrote:
> Attached a small patch mentioning the obsolete '-j1 N' syntax. 

I'm somewhat inclined to leave this undocumented, as it's not portable 
and it is more likely to confuse than to help the reader. Generally 
speaking we don't have to document obsolete and somewhat-flaky usage 
when there is a portable and well-supported alternative. That's what 
POSIX does anyway, when documenting 'join'.

Previous Next

GNU bug tracking system
Copyright (C) 1999 Darren O. Benham, 1997,2003 nCipher Corporation Ltd, 1994-97 Ian Jackson.