`--format=csv` in `codeql database interpret-results` can produce a malformed `.csv`

[k4lizen]

Description of the issue

See #21673 and #21670 (comment). A query like this:

class MyStuct extends Struct {
  MyFlow::PathNode source;
  MyFlow::PathNode sink;
  MyStuct() {
      MyFlow::flowPath(source, sink) and
      some_predicate(this, sink) and
  }
  MyFlow::PathNode getSink() { result = sink }
  MyFlow::PathNode getSource() { result = source }
}
from MyStuct s, MyFlow::PathNode source, MyFlow::PathNode sink
where
  source = s.getSource() and sink = s.getSink() 
select sink.getNode(), source, sink, "aaa"

will have lots of rows with unrelated sinks and sources, and interpret-results combusts in figuring this out.

codeql database interpret-results  ./source/source-db/ ./queries/my-query.ql --format=csv --output=./a.csv

Will give me a .csv that looks like this:

,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa
aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa
aaa
aaa
aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"
,,"warning","aaa","/sources/my-source/myfile.c","1","2","3","4"

(with different filenames and locations, I just stubbed them out, but you can see the formatting is bad)

Supposedly the sarif format at least somewhat works, since the vscode extension uses that and can provide a somewhat sensible output, but there are two results it shows which it should not show (48 results are shown, but only 46 are valid).

[jketema]

Hi @k4lizen,

Are you referring contents of a field spanning multiple lines? Like in:

,,"warning","aaa
aaa
aaa
aaa","/sources/my-source/myfile.c","1","2","3","4"

Or are you referring to something else?

The field spanning multiple lines, as long at it is in between double quotes, is widely accepted. See, for example, point 6 of https://datatracker.ietf.org/doc/html/rfc4180#section-2:

Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes.

[k4lizen]

interesting, i didn't know that

but why are there new lines in the output of the query in the first place?

[jketema]

but why are there new lines in the output of the query in the first place?

That's hard to tell from the information you provided, and I would need a reproducing example. My best guess without a reproducing example: Do you happen to have multiple results for the locations where this is happening?