merge step fails with `sqlite3.OperationalError: table FEATURE has 3 columns but 9 values were supplied`

[cbielow]

I am running the three PASS00779 mzMLs through OpenSwathWorkflow (current OpenMS develop 2.6beta) and postprocess the resulting osw files using pyprophet (version 2.1.5).

Running the second merge step in pyprophet results in

Running: pyprophet merge --template=model.osw --out=model_global.osw C:/Users/bielow/SwathWizardOut/olgas_K121026_007_SW_Wayne_R2_d00_copy.osw C:/Users/bielow/SwathWizardOut/olgas_K121026_013_SW_Wayne_R3_d00_copy.osw C:/Users/bielow/SwathWizardOut/olgas_K121026_001_SW_Wayne_R1_d00_copy.osw

Info: Merged runs of file C:/Users/bielow/SwathWizardOut/olgas_K121026_007_SW_Wayne_R2_d00_copy.osw to model_global.osw.
Info: Merged runs of file C:/Users/bielow/SwathWizardOut/olgas_K121026_013_SW_Wayne_R3_d00_copy.osw to model_global.osw.
Info: Merged runs of file C:/Users/bielow/SwathWizardOut/olgas_K121026_001_SW_Wayne_R1_d00_copy.osw to model_global.osw.

Traceback (most recent call last):
  File "C:\WinPython\3.7\python-3.7.0.amd64\lib\runpy.py", line 193, in _run_module_as_main
    "__main__", mod_spec)
  File "C:\WinPython\3.7\python-3.7.0.amd64\lib\runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "C:\WinPython\3.7\python-3.7.0.amd64\Scripts\pyprophet.exe\__main__.py", line 9, in <module>
  File "C:\WinPython\3.7\python-3.7.0.amd64\lib\site-packages\click\core.py", line 722, in __call__
    return self.main(*args, **kwargs)
  File "C:\WinPython\3.7\python-3.7.0.amd64\lib\site-packages\click\core.py", line 697, in main
    rv = self.invoke(ctx)
  File "C:\WinPython\3.7\python-3.7.0.amd64\lib\site-packages\click\core.py", line 1092, in invoke
    rv.append(sub_ctx.command.invoke(sub_ctx))
  File "C:\WinPython\3.7\python-3.7.0.amd64\lib\site-packages\click\core.py", line 895, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "C:\WinPython\3.7\python-3.7.0.amd64\lib\site-packages\click\core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "C:\WinPython\3.7\python-3.7.0.amd64\lib\site-packages\pyprophet\main.py", line 271, in merge
    merge_osw(infiles, outfile, templatefile, same_run)
  File "C:\WinPython\3.7\python-3.7.0.amd64\lib\site-packages\pyprophet\levels_contexts.py", line 494, in merge_osw
    merge_oswr(infiles, outfile, templatefile, same_run)
  File "C:\WinPython\3.7\python-3.7.0.amd64\lib\site-packages\pyprophet\levels_contexts.py", line 735, in merge_oswr
    c.executescript('ATTACH DATABASE "%s" AS sdb; INSERT INTO FEATURE SELECT * FROM sdb.FEATURE; DETACH DATABASE sdb;' % infile)
sqlite3.OperationalError: table FEATURE has 3 columns but 9 values were supplied

Not sure why this happens. This is how I got there (this is c++ code, but the calls should be recognizable). The last call fails (see above). osws is a list of 3 input files in osw format.

QString pp = "pyprophet";
// list of calls to make: exe, args, [optional] list of args to append one-by-one in a loop
std::vector<std::tuple<QString, QStringList, QStringList>> calls;
// merge all osws ...
calls.emplace_back(pp, QStringList() << "merge" << "--template=" + library << "--out=model.osw" << osws, QStringList());
// to build a common model --> creates merged_ms1ms2_report.pdf
calls.emplace_back(pp, QStringList() << "score" << "--in=model.osw" << "--level=ms1ms2", QStringList());
// apply in loop
calls.emplace_back(pp, QStringList() << "score" << "--apply_weights=model.osw" << "--level=ms1ms2" << "--in", osws);
// merge again for peptide and protein error rate control
calls.emplace_back(pp, QStringList() << "merge" << "--template=model.osw" << "--out=model_global.osw" << osws, QStringList());

Do I need additonal flags (maybe even when running OSW?)??

[grosenberger]

This is probably an issue because pyprophet reduce was not run before the merge step and the current merge function might not be compatible yet with the full files.

Could you please check, whether the current master version resolves the issue (there might have been some changes recently) and whether pyprophet reduce helps?

We will probably have to tweak this function: https://github.com/PyProphet/pyprophet/blob/fa86fcc5c9185083540ca5be3f1f7a740c0fba41/pyprophet/levels_contexts.py#L724

Adjusting the SQL query to be specific to the needed columns might be enough, e.g.:

https://github.com/PyProphet/pyprophet/blob/fa86fcc5c9185083540ca5be3f1f7a740c0fba41/pyprophet/levels_contexts.py#L815

[cbielow]

the current master shows the same problem. However, adding a reduce-step fixes the problem.