[odb-users] the same SQL query is handled differently by odb and some DBManager

[Артем Бабаев]

<div style="background-color:rgb( 255 , 255 , 255 );color:rgb( 0 , 0 , 0 );font-family:'ys text' , 'arial' , sans-serif;font-size:16px;font-style:normal;font-weight:400;text-decoration-style:initial;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">OS: Ubuntu 20.04.4 LTS</div><div style="background-color:rgb( 255 , 255 , 255 );color:rgb( 0 , 0 , 0 );font-family:'ys text' , 'arial' , sans-serif;font-size:16px;font-style:normal;font-weight:400;text-decoration-style:initial;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">package: libodb-sqlite-2.5.0</div><div style="background-color:rgb( 255 , 255 , 255 );color:rgb( 0 , 0 , 0 );font-family:'ys text' , 'arial' , sans-serif;font-size:16px;font-style:normal;font-weight:400;text-decoration-style:initial;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">compiler: gcc 9.4.0</div><div style="background-color:rgb( 255 , 255 , 255 );color:rgb( 0 , 0 , 0 );font-family:'ys text' , 'arial' , sans-serif;font-size:16px;font-style:normal;font-weight:400;text-decoration-style:initial;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">SQlite DB</div><div style="background-color:rgb( 255 , 255 , 255 );color:rgb( 0 , 0 , 0 );font-family:'ys text' , 'arial' , sans-serif;font-size:16px;font-style:normal;font-weight:400;text-decoration-style:initial;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px"> </div><div style="background-color:rgb( 255 , 255 , 255 );color:rgb( 0 , 0 , 0 );font-family:'ys text' , 'arial' , sans-serif;font-size:16px;font-style:normal;font-weight:400;text-decoration-style:initial;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">this SQL query works from some dbManager (DBeaver 21.3.4)<br />But doesnt works from odb::database (column and table names changed to simple little names)</div><div style="background-color:rgb( 255 , 255 , 255 );color:rgb( 0 , 0 , 0 );font-family:'ys text' , 'arial' , sans-serif;font-size:16px;font-style:normal;font-weight:400;text-decoration-style:initial;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px"> </div><div style="background-color:rgb( 255 , 255 , 255 );color:rgb( 0 , 0 , 0 );font-family:'ys text' , 'arial' , sans-serif;font-size:16px;font-style:normal;font-weight:400;text-decoration-style:initial;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px"><pre style="margin:0px;text-indent:0px"><span style="color:#c0c0c0">    </span><span style="color:#800080">QString</span><span style="color:#c0c0c0"> </span><span style="color:#092e64">s</span><span style="color:#c0c0c0"> </span>=<span style="color:#c0c0c0"> </span><span style="color:#808000">R"(</span></pre><pre style="margin:0px;text-indent:0px"><span style="color:#008000">    UPDATE "T1" SET "c1"="2"</span></pre><pre style="margin:0px;text-indent:0px"><span style="color:#008000">    FROM (SELECT rowid, "c2" FROM "T2" WHERE "c2" IN (51,52) AND "c3" = 59) AS t2</span></pre><pre style="margin:0px;text-indent:0px"><span style="color:#008000">    WHERE "c4" = t2.rowid</span></pre><pre style="margin:0px;text-indent:0px"><span style="color:#008000">    </span><span style="color:#808000">)"</span>;</pre><pre style="margin:0px;text-indent:0px">
 </pre><pre style="margin:0px;text-indent:0px"><span style="color:#c0c0c0">    </span><span style="color:#092e64">dbConn</span>.<span style="color:#00677c">execute</span>(<span style="color:#092e64">s</span>.<span style="color:#00677c">toStdString</span>());</pre><div> </div><div>i can do this query and it works:<pre style="margin:0px;text-indent:0px"><span style="color:#008000">WITH</span><span style="color:#c0c0c0"> </span><span style="color:#008000">t2</span><span style="color:#c0c0c0"> </span><span style="color:#008000">AS</span><span style="color:#c0c0c0"> </span><span style="color:#008000">(SELECT</span><span style="color:#c0c0c0"> </span><span style="color:#008000">rowid</span><span style="color:#c0c0c0"> </span><span style="color:#008000">FROM</span><span style="color:#c0c0c0"> </span><span style="color:#008000">"T2"</span><span style="color:#c0c0c0"> </span><span style="color:#008000">WHERE</span><span style="color:#c0c0c0"> </span><span style="color:#008000">"c2"</span><span style="color:#c0c0c0"> </span><span style="color:#008000">IN</span><span style="color:#c0c0c0"> </span><span style="color:#008000">(51,52)</span><span style="color:#c0c0c0"> </span><span style="color:#008000">AND</span><span style="color:#c0c0c0"> </span><span style="color:#008000">"c3"</span><span style="color:#c0c0c0"> </span><span style="color:#008000">=</span><span style="color:#c0c0c0"> </span><span style="color:#008000">59)</span></pre><div><span style="color:#008000">UPDATE "T1" SET "c1"="2"</span></div><div><span style="color:#008000">WHERE "c4" IN t2</span></div><div> </div><div>but eventually i need somethink like this, so i need to get reference to foreign table.</div><div> </div><div><pre style="margin:0px;text-indent:0px"><span style="color:#008000">UPDATE "T1"</span></pre><pre style="margin:0px;text-indent:0px"><span style="color:#008000">SET "c1"=CASE t2.c2 WHEN 51 THEN 2 WHEN 52 THEN 3 END</span></pre><pre style="margin:0px;text-indent:0px"><span style="color:#008000">FROM (SELECT rowid, "c2" FROM "T2" WHERE "c2" IN (51,52) AND "c3" = 59) AS t2</span></pre><pre style="margin:0px;text-indent:0px"><span style="color:#008000">WHERE "c4" = t2.rowid</span></pre></div><div> </div><div>i get an error on all of this attempts (keyword FROM in UPDATE query)</div><div><blockquote><div>odb::sqlite::database_exception 1: near "FROM": syntax error</div></blockquote></div></div></div><div>looks like there is some difference in the processing of SQL queries in odb and dbmanager</div><div> </div><div>The question is:<br />How to ensure that you can use SQL queries that work from the database manager?</div>