SLAVE-DOWN Issue on joins

[stoch]

For odpi I get on sbs1

for

SELECT CON_ID, CON_CODE, CON_OPKEY, CIOT_IWDID, CIOT_TRCTID, CIOT_CURID FROM contract, CONIOT WHERE CIOT_TRCTID = 'SMS' AND CIOT_CONID = CON_ID AND CON_ESID = 'A' AND NOT (CON_ESID IN ('R', 'D')) AND con_etid = 'TOC'

where this is the result for oci

[stoch]

{slave_down,[{dderlodpi,run_query,6,[{file,"c:/Users/Bikram/Documents/projects/git/kxn/dderl/src/dderlodpi.erl"},{line,402}]},{dderlodpi,exec,4,[{file,"c:/Users/Bikram/Documents/projects/git/kxn/dderl/src/dderlodpi.erl"},{line,79}]},{odpi_adapter,process_query,3,[{file,"c:/Users/Bikram/Documents/projects/git/kxn/dderl/src/odpi_adapter.erl"},{line,742}]},{odpi_adapter,process_cmd,6,[{file,"c:/Users/Bikram/Documents/projects/git/kxn/dderl/src/odpi_adapter.erl"},{line,307}]},{dderl_session,spawn_process_call,8,[{file,"c:/Users/Bikram/Documents/projects/git/kxn/dderl/src/dderl_session.erl"},{line,659}]}]} 2020-04-29 18:24:13.434 [error] [dderl<0.4822.6>@odpi_adapter:process_query:777] query error {error,slave_down} for <<"SELECT\r\n CON_ID, CON_CODE, CON_OPKEY, CON_HUB, CON_ULNRP, CON_NAME, CON_ESID,\r\n CON_DATESTART, CON_DATEEND, CON_VIRTUAL, CIOT_IWDID, CIOT_TRCTID, CIOT_CURID\r\n , CIOTE_PRICE\r\nFROM\r\n contract,\r\n CONIOT,\r\n CONIOTE\r\nWHERE\r\n CIOTE_CIOTID = CIOT_ID\r\n AND CIOT_IWDID = 'ORIG'\r\n AND CIOT_TRCTID = 'SMS'\r\n AND CIOT_CONID = CON_ID\r\n AND CON_ESID = 'A'\r\n AND NOT (CON_ESID IN ('R', 'D'))\r\n AND con_etid = 'TOC'\r\n AND (CON_DATESTART IS NULL\r\n OR CON_DATESTART < sysdate)\r\n AND (CON_DATEEND IS NULL\r\n OR CON_DATEEND > TRUNC(sysdate, 'MONTH'))\r\nORDER BY\r\n 2 ASC">> whith bind values undefined

[stoch]

Issue confirmed today (same odpi installation) for original query. After that, the dderl session is lost (new query SELECT FROM DUAL fails with {error,slave_down}. After Re-Login the following is working: SELECT FROM CONTRACT SELECT CIOT_ID, CIOT_CONID, CIOT_CURID, CIOT_IWDID, CIOT_TRCTID, CIOT_CHNGCNT, CIOT_DATECRE, CIOT_ACIDCRE, CIOT_DATEMOD, CIOT_ACIDMOD FROM CONIOT But this fails with {error,slave_down}: SELECT CIOT_CONID FROM contract, CONIOT WHERE rownum < 20 Same with SELECT CON_ID FROM contract, BDETAIL WHERE rownum < 20

CREATE TABLE join_test ( A VARCHAR2(10), B VARCHAR2(10) )

INSERT INTO join_test ( A, B) SELECT rownum, rownum + 1 FROM all_objects

SELECT COUNT(*) FROM join_test

69306

This works SELECT J1.A, J2.B FROM join_test J1, join_test J2 SELECT J1.A, CON_ID FROM join_test J1, CONTRACT

But this fails with {error,slave_down}: SELECT J1.A FROM join_test J1, CONIOT

This works: SELECT rownum FROM coniot, dual

But this fails with {error,slave_down}: SELECT rownum FROM dual, coniot

In TOAD, as well as with OCI driver, this returns 3573 rows

similar behaviour for other tables:

This works SELECT rownum FROM BDETAIL, dual

but this fails SELECT rownum FROM dual, BDETAIL

This does not crash immediately: SELECT rownum FROM join_test, dual

but fails to fetch all the rows. I see 3100 (of 69306) when it stops fetching (but does not crash the session). Fetch >> works until 11200 when I presses >| (which crahsed the statement.

where this immediately crashes the session:

SELECT rownum FROM dual, join_test

@KarlKeiser Please reproduce the issue like this:

CREATE TABLE join_test ( A VARCHAR2(10), B VARCHAR2(10) )

INSERT INTO join_test ( A, B) SELECT rownum, rownum + 1 FROM all_objects

SELECT rownum FROM dual, join_test

[KarlKeiser]

Works in sqlplus, also works in oranif with the following script:

#!/user/bin/escript
%% -*- erlang -*-
%%! -pa _build/default/lib/oranif/ebin

-include_lib("eunit/include/eunit.hrl").
-define(DPI_MAJOR_VERSION, 3).
-define(DPI_MINOR_VERSION, 0).
-define(TNS, <<>>).

fetch_all(Stmt, Acc) ->
    #{found := Found} = dpi:stmt_fetch(Stmt),
    case Found of true ->
        #{data := Result} = dpi:stmt_getQueryValue(Stmt, 1),
        fetch_all(Stmt, Acc ++ [dpi:data_get(Result)]);
    _ -> Acc end.

main([]) ->
    dpi:load_unsafe(),
    Context = dpi:context_create(?DPI_MAJOR_VERSION, ?DPI_MINOR_VERSION),
    Conn = dpi:conn_create(
        Context, <<"foo">>, <<"bar">>, ?TNS,
        #{encoding => "AL32UTF8", nencoding => "AL32UTF8"}, #{}
    ),
    SqlCreation = <<"CREATE TABLE
                    join_test (
                    A VARCHAR2(10),
                    B VARCHAR2(10)
                    )">>,
    StmtC = dpi:conn_prepareStmt(Conn, false, SqlCreation, <<>>),
    0 = dpi:stmt_execute(StmtC, []),
    SQLInsert = <<"INSERT INTO
                    join_test (
                    A, B)
                    SELECT
                    rownum, rownum + 1
                    FROM
                    all_objects">>,
    StmtI = dpi:conn_prepareStmt(Conn, false, SQLInsert, <<>>),
    0 = dpi:stmt_execute(StmtI, []),
    Sql = <<"SELECT rownum FROM dual, join_test">>,
    Stmt = dpi:conn_prepareStmt(Conn, false, Sql, <<>>),

    1 = dpi:stmt_execute(Stmt, []),
    [io:format("got em ~p~n", [X]) || X <- fetch_all(Stmt, [])],
    StmtD = dpi:conn_prepareStmt(Conn, false, <<"drop table join_test">>, <<>>),
    0 = dpi:stmt_execute(StmtD, []),
    io:format("Test successful.~n"),

    % cleanup
    ok = dpi:stmt_close(StmtC, <<>>),
    ok = dpi:stmt_close(Stmt, <<>>),
    ok = dpi:stmt_close(StmtI, <<>>),
    ok = dpi:stmt_close(StmtD, <<>>),
    ok = dpi:conn_close(Conn, [], <<>>),
    ok = dpi:context_destroy(Context),
    halt(1).

I will now try to find the issue in dderl.

[stoch]

Could you reproduce the issue in your local DDerl with latest oranif?

[KarlKeiser]

Yes, I can reproduce the error.

[KarlKeiser]

Looks like it can't handle the cross join for some reason. It works when replacing it with an equivalent full join:

SELECT
    rownum
FROM
    dual
    FULL OUTER JOIN
    join_test
    ON 1 = 1

The cross join works on simple tables (I tried making a table with an integer, filling two rows and cross joining it with itself). I'm now trying to find out why it sometimes works and sometimes does not.

[KarlKeiser]

The issue is caused by a parameter in dpiConn_prepareStmt called "scrollable". This parameter was set to false in the dderl code, and setting it to true fixes that issue. However, setting it to true causes an OCI error when the statement is not of type SELECT, so the fix will use an odpi call to check if a statement is a SELECT call or not and set the parameter accordingly.

According to the odpi documentation the parameter should be ignore for non-queries, but that doesn't seem to be the case.

[KarlKeiser]

Fixed in dderl by setting the parameter to true if it is a SELECT statement.