Oracle nested statements incorrect behavior

[ristillu]

Following the documentation from http://soci.sourceforge.net/doc/master/backends/oracle/ regarding nested statements, a code example as per:

#include <iostream>
#include <string>
#include <soci/soci.h>

int main(int argc, char **argv)
{
    using namespace std::string_literals;
    try
    {
        auto connectionString = "************redacted************"s;
        soci::session sql("oracle", connectionString);
        auto query = R"(WITH cte1 AS (SELECT 1 AS id, 'one' AS name FROM DUAL
                                      UNION ALL
                                      SELECT 2, 'two' FROM DUAL),
                             cte2 AS (SELECT 1 AS id, 'a' AS name, 'one' AS parent FROM DUAL
                                      UNION ALL
                                      SELECT 2, 'b', 'one' FROM DUAL
                                      UNION ALL
                                      SELECT 3, 'c', 'two' FROM DUAL
                                      UNION ALL
                                      SELECT 4, 'd', 'two' FROM DUAL)
                        SELECT cte1.id,
                               cte1.name,
                               CURSOR(SELECT cte2.* FROM cte2
                                      INNER JOIN cte1 inner ON cte2.parent = inner.name
                                      WHERE inner.name = cte1.name)
                        FROM cte1)"s;
        int outerId;
        std::string outerName;
        soci::statement inner(sql);
        soci::statement outer = (sql.prepare << query, soci::into(outerId),
                                                       soci::into(outerName),
                                                       soci::into(inner));
        int innerId;
        std::string innerName;
        std::string parent;
        inner.exchange(soci::into(innerId));
        inner.exchange(soci::into(innerName));
        inner.exchange(soci::into(parent));
        outer.execute();
        std::cout << "outer id\touter name\tinner id\tinner name\tparent" << std::endl;
        while (outer.fetch())
        {
            while (inner.fetch())
            {
                std::cout << outerId << "\t\t" << outerName << "\t\t" << innerId << "\t\t" << innerName << "\t\t" << parent << std::endl;
            }
            std::cout << "---------------------------------------------------" << std::endl;
        }
    }
    catch (soci::soci_error& exc)
    {
        std::cout << "Exception caught: " << exc.what() << std::endl;
    }
    return 0;
}

returns:

outer id	outer name	inner id	inner name	parent
1		one		1		a		one
1		one		2		b		one
---------------------------------------------------
---------------------------------------------------

where the behavior should be:

outer id	outer name	inner id	inner name	parent
1		one		1		a		one
1		one		2		b		one
---------------------------------------------------
2		two		3		c		two
2		two		4		d		two
---------------------------------------------------

The SQL query run from within two different Oracle SQL managers returns the correct results (two rows corresponding to the outer 'loop' and an iterable cursor with two rows each time on the inner 'loop', i.e. four rows total results).

It may be that the inner statement/cursor needs to be reset in between calls to outer.fetch(). Certainly in raw SQL the inner is an iterable that needs to be reset after traversal if you want to read it multiple times.

There are workarounds for this behavior in the form of complicated or messy joins (basically the desired behavior is aggregated data per row) but the cursor functionality is much clearer if it were working correctly.

[mloskot]

I'm not experienced with this type of nested queries myself.

What I would do is try some typical path of researching a complex problem

• simplify the SQL
• simplify the example to make it similar to the one from the docs, to not to use prepared statements.
• try single fetch instead of iterative

Possibly, there may be caveats in general or SOCI implementation may be lacking.