Preview of source data from sql statement fails due to appended/replacement of rownum clause

[kbraak]

What steps will reproduce the problem?
1. The preview function appends "where rownum <= 10" to sql statements if rownum is
not used in the query and/or subquery and can cause multiple where clauses to occur
at the end of the query (i.e. instead of "where <clause1> and rownum..." it results
in "where <clause1> where rownum..." which is syntactically wrong.

2. If rownum is already used in the sql statement, the preview function replaces any
instance of "rownum <operator><number>" with "rownum <=10" which can lead to a "single
row subquery returning multiple rows" error, and causes the IPT to classify the query
as unstable even if it works properly in a regular sql environment (eg. sql developer).

What version of the provider software are you using? The version should be
displayed in the footer of any page.
IPT Version 2.2-r5184

What browser are you using?
Chrome

Please provide any additional information below.
Temporary workarounds:
1) This issue can be dealt with by selecting-all from your selection, e.g. "select
* from (select <your fields> from <your tables> where <your clause(s)>)". This will
result in the IPT preview appending the "WHERE rownum <=10" to your query outside the
bracketed query. This ensures the syntactically correct format of only one "where"
per query.

2) The workaround for this issue is to "trick" the IPT into accepting a query that
contains all the necessary data fields for mapping to a Darwin Core Archive, but which
does not suffer from problems caused by rownum. This is essentially a spurious query,
whose data would not actually be uploaded. However, it lets the IPT accept the query
as readable and previewable after its analysis. After the IPT has accepted the query
as readable, change the query's parameters (but not the number or names of the result
fields) so that it actually will pull the desired data from the database and save the
query, but do not click on the "analyse" button or preview the data (that part is important,
for if you do those things you are back to square one with the rownum problem). After
that the data is publishable, as the IPT does not alter the query in any way when passing
it to the database during the actual publishing process; the rownum replacement issue
only takes place during previewing/analysis of the query results.

Original issue reported on code.google.com by bryanbrunet on 2015-05-05 17:26:17

[kbraak]

Thank you for reporting this issue, and for providing detailed workaround instructions.
This issue will be addressed following the next release (2.3).

Original issue reported on code.google.com by kyle.braak on 2015-05-06 07:51:42

• Labels added: Usability, Milestone-Release2.X, Priority-High