title: Android SQLite queries marked as Error by Android Studio
body: <p>In one of my Android apps I use some <code>SQLite</code> queries that are starting to give problems since I've updated to <code>Android Studio 3.0</code>.</p>
  
  <p>Despite I can compile and run the app they are marked as error.</p>
  
  <p>These are the queries.</p>
  
  <pre><code> db.execSQL("INSERT INTO table1 (…) ... substr(replace(table2.field2, ',', ''), table1.field1, 1) … FROM table1 … WHERE …");
  </code></pre>
  
  <p>gives error in 'replace':</p>
  
  <blockquote>
    <p>')' or expression expected, got 'replace'</p>
  </blockquote>
  
  <p>I think this would work because <code>replace</code> returns a <code>String</code>.</p>
  
  <p>And:</p>
  
  <pre><code>db.execSQL("DELETE FROM table1 WHERE table1.rowid IN (…)");
  </code></pre>
  
  <p>gives error in 'rowid':</p>
  
  <blockquote>
    <p>column name or table name expected, got 'rowid'</p>
  </blockquote>
  
  <p>I think this would work too because <code>SQLite</code> adds an implicit <code>rowid</code> to each table.</p>
  
  <p>And:</p>
  
  <pre><code>db.execSQL("ATTACH DATABASE ? as newDB", new String[] { getDatabasePath(DATABASE_NAME_NEW).getPath() });
  </code></pre>
  
  <p>gives error in '?':</p>
  
  <blockquote>
    <p>expression expected, got '?'</p>
  </blockquote>
  
  <p>I've changed this query to this:</p>
  
  <pre><code>db.execSQL("ATTACH DATABASE '"+getDatabasePath(DATABASE_NAME_NEW).getPath()+"' as newDB");
  </code></pre>
  
  <p>Also:</p>
  
  <pre><code>db.execSQL("UPDATE table1 SET field1=0 WHERE field2 IN 
  (SELECT field2 FROM table2 WHERE field3 LIKE '%blablabla%' OR field4 LIKE '%blebleble%' OR (field5 LIKE '%bliblibli%' AND field6 NOT LIKE '%blobloblo%') 
  COLLATE NOCASE)");
  </code></pre>
  
  <p>Which gives these errors: </p>
  
  <p>In 'field6 NOT LIKE':</p>
  
  <blockquote>
    <p>')' or '.' expected, got 'NOT'</p>
  </blockquote>
  
  <p>In 'NOCASE)':</p>
  
  <blockquote>
    <p>BETWEEN, IN or semicolon expected, got ')'</p>
  </blockquote>
  
  <p>I don't know what is wrong with this one.</p>
  
  <p><strong>Are these queries correct?</strong> In iOS are identical and they are working well.</p>
  
  <p>UPDATE:</p>
  
  <p>These are the full queries (unfortunately due to my client's security policy I have to change the name of the tables and fields, but I hope is ok to detect the errors).</p>
  
  <pre><code>db.execSQL("INSERT INTO table1 (field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12, field13, field14) " +
                      "SELECT t6.field1, t7.field2, t3.field4, t3.field5, CASE WHEN t5.field1='1' THEN '11' " +
                      "WHEN t5.field1='2' THEN '22' WHEN t5.field1='3' THEN '33' WHEN t5.field1='4' THEN '44' " +
                      "ELSE t5.field1 END AS newfield1, t4.field1, CASE WHEN t2.field1 = '0' THEN t4.field2 " +
                      "ELSE substr(replace(t4.field3, ',', ''), t2.field1, 1) END AS newfield2, t2.field4, '0', -1, '0', '1', -1, t8.field1 " +
                      "FROM table2 AS t2, table3 AS t3, table4 AS t4, table5 AS t5, table6 AS t6, table7 AS t7, table8 AS t8 " +
                      "WHERE t2.field2=t3.field3 AND t2.field3=t4.field1 AND t2.field3=t5.field2 AND t3.field2=t7.field2 AND " +
                      "t3.field1=t6.field1 AND substr(t5.field1, 1, 1) IN ('1', '2', '3', '4') AND substr(t5.field1, 2, 1) IN ('1', '2', '3', '4') AND t2.field5=t8.field2");
  
  db.execSQL("DELETE FROM table1 WHERE table1.rowid IN (SELECT table1.rowid FROM table1, table4 AS t4 WHERE table1.field6=t4.field1 AND (((t4.field2 NOT LIKE '%%' || substr(table1.field5, 1,1) || '%%') AND (t4.field3 NOT LIKE '%%' || substr(table1.field5, 1,1) || '%%')) OR ((t4.field2 NOT LIKE '%%' || substr(table1.field5, 2,1) || '%%') AND (t4.field3 NOT LIKE '%%' || substr(table1.field5, 2,1) || '%%'))))");
  
  db.execSQL("ATTACH DATABASE ? as newDB", new String[] { getDatabasePath(DATABASE_NAME_NEW).getPath() });
  Changed to:
  db.execSQL("ATTACH DATABASE '"+getDatabasePath(DATABASE_NAME_NEW).getPath()+"' as newDB");
  And the error is gone.
  
  db.execSQL("UPDATE table1 SET field13=1 WHERE field2 IN (SELECT t7.field2 FROM table7 AS t7 WHERE t7.field1='Text1' OR t7.field1='Text2' OR t7.field1 LIKE '%TEXT3%' OR t7.field1 LIKE '%TEXT4%' OR t7.field1 LIKE '%TEXT5%' OR t7.field1 LIKE '%TEXT6%' OR (t7.field1 LIKE '%TEXT7%' AND t7.field1 NOT LIKE '%TEXT8%') COLLATE NOCASE)");
  </code></pre>