android - 当将selectionArgs与INNER JOIN一起使用时，rawQuery是否损坏

Question

编辑：在原始问题之后增加了分辨率。事实证明，该问题与临时表中的聚合函数有关，而不是与具有内部联接的参数标记有关。该决议中有更详细的说明。

这个问题不是重复的。该问题与IN子句的存在无关，我已通过从查询中删除IN子句来说明不良行为仍然独立于IN子句，从而说明了这一问题。

关于下面我的问题的确切位置，似乎有些困惑。问题不在IN子句中。问题是用pheno_temp1.pheno_count =？指定的参数？没有被替换。

为了解决对问题的这种误读，我重写了两个rawQuery调用，以说明该行为在内部联接的参数替换中。在下面的调用中没有IN子句，并且它们表现出与在最初的两个调用中指定的行为完全相同的行为（我保留以供参考）。

// More restrictive example. This call gives the correct result;
// note there are NO parameter markers.
cursor = _mendelDatabase.rawQuery("SELECT organism.org_id FROM organism INNER JOIN pheno_temp1 ON organism.org_id=pheno_temp1.org_id WHERE pheno_temp1.pheno_count=1",null);

// This call returns NO results. pheno_count=1 has been replaced with
// pheno_count=?
cursor = _mendelDatabase.rawQuery("SELECT organism.org_id FROM organism INNER JOIN pheno_temp1 ON organism.org_id=pheno_temp1.org_id WHERE pheno_temp1.pheno_count=?",new String[]{"1"});

// This query still gives the correct result; It includes a parameter
// marker for a column from the organism table.
cursor = _mendelDatabase.rawQuery("SELECT organism.org_id FROM organism INNER JOIN pheno_temp1 ON organism.org_id=pheno_temp1.org_id WHERE pheno_temp1.pheno_count=1 AND organism.bbch_stage=?",new String[]{"9"});

// This query still returns no rows. It includes 2 parameter markers;
// one for a column from organism (see above call which worked) and one
// for pheno_count from the inner table.
cursor = _mendelDatabase.rawQuery("SELECT organism.org_id FROM organism INNER JOIN pheno_temp1 ON organism.org_id=pheno_temp1.org_id WHERE pheno_temp1.pheno_count=? AND organism.bbch_stage=?",new String[]{"1","9"});

我正在观察执行rawQuery时的行为，该行为表明它在使用selectionArgs传递内部联接中指定的表上的where子句的值时出现错误。

我在SQLite数据库中有以下表格：

table organism
org_id  bbch_stage  org_name                            (other data)
-----------------------------------------------------------------------
     0           9  homozygous dominant starter plant   (miscellaneous)
     1           9  homozygous recessive starter plant  (miscellaneous)

table pheno_temp1
org_id   pheno_count
--------------------
     0             1

以下调用返回不同的结果集：

// This call returns the correct result set
cursor = _mendelDatabase.rawQuery("SELECT organism.org_id FROM organism INNER JOIN pheno_temp1 ON organism.org_id=pheno_temp1.org_id WHERE pheno_temp1.pheno_count=1 AND organism.bbch_stage IN (?)",new String[]{"9"});

// This call returns no rows
cursor = _mendelDatabase.rawQuery("SELECT organism.org_id FROM organism INNER JOIN pheno_temp1 ON organism.org_id=pheno_temp1.org_id WHERE pheno_temp1.pheno_count=? AND organism.bbch_stage IN (?)",new String[]{"1","9"});

对我来说，这似乎是异常行为。但是，在尝试提交错误报告之前，我想知道是否存在带有内部联接和selectionArgs的rawQuery的细微行为，这些行为在文档中不明显。

我在运行Android 4.1.1的HTC One上观察到了这种行为。开发环境为Android Studio 2.1.1。编译SDK 23，构建工具23.0.3，最小SDK 14，目标SDK23。Java1.8.0_91。

背景：

上面的查询是最简单的查询，用于说明应用程序中可能出现的问题。真实的查询可能会任意地包括生物体对母体和父系血统的限制，生物体“出生”日期（两个日期之前，之后，之中或之间），或多个烧烤阶段；指定多个表型，或指定来自遗传表的多个等位基因（在上面的示例中省略）。限制的任意性质使视图不合适。相反，我为表型和等位基因限制构建了工作表（如果需要），然后在内部联接中使用它们来限制从生物体表中选择的行（同样，如果需要的话）。

通过建立临时表来设置对表型和等位基因的限制，然后将这些临时表连接到生物体表。如果用户对表型设置了限制，则 pheno_temp1表将指定（在 pheno_count中）每种生物满足多少限制。因此，如果用户设置了三个表型限制，则只有 pheno_temp1中带有 pheno_count=3的条目才能满足用户的条件。在内部联接中， pheno_count上的where子句必须容纳一个变化的值。类似的（但明显更复杂）的机制用于限制等位基因的结果。

我有多种方法可以围绕rawQuery行为进行编码，而无需将计数值连接到where子句中（这涉及到临时表的更多步骤），因此我没有在寻找解决该问题的方法。我要寻找的是在打开错误报告之前确认行为是否存在错误。

有关本网站上其他位置的更一般的“我如何进行内部联接”问题的讨论，建议第二个查询（不返回行的查询）应该起作用，但是我看到的所有答案都说“这是一个示例...”，而不是“这是我为某些作品测试并知道的代码。” （...也许除了这个问题 SQLite rawquery selectionArgs not working）。

Answer 1

解析度

希望能在解决类似问题时帮助其他解决此问题的人，这就是我的决定。每个失败的查询（返回0行）有两个共同点。


所有查询都涉及一个内部联接，其中联接表中的一列将用于限制返回的行（通过inner_table.column = ?，其中selectionArgs用于提供替换值）；
每个用于限制返回的行的内部表列都是某种聚合函数（计数，总和）的结果。


所有失败查询都具有内部联接的事实使我看不到所有参数替换失败的列都是聚合函数这一事实。

这些是用于定义和填充查询中涉及的表的SQL命令：

CREATE TABLE IF NOT EXISTS `organism` (
  `org_id` INT NOT NULL,
  `org_name` INT NULL,
  `mat_id` INT NULL,
  `pat_id` INT NULL,
  `map_id` INT NOT NULL,
  `bbch_stage` INT NULL,
  -- other columns not relevant to problem
  PRIMARY KEY (`org_id`),
  -- constraints not relevant to problem
);
-- indexes not relevant to problem

CREATE TABLE IF NOT EXISTS `gene_sequence` (
  `org_id` INT NOT NULL,
  `gene_id` INT NOT NULL,
  `allele_id` INT NOT NULL,
  -- constraints not relevant to problem
);
-- indexes not relevant to problem

CREATE TABLE IF NOT EXISTS `organism_phenotype` (
  `org_id` INT NOT NULL,
  `map_id` INT NOT NULL,
  `phenogroup_id` INT NOT NULL,
  `phenotype_id` INT NOT NULL,
  -- constraints not relevant to problem
);
-- indexes not relevant to problem

INSERT INTO `organism`
   (`org_id`, `org_name`, `mat_id`, `pat_id`,  `map_id`, `bbch_stage` /* other columns omitted for brevity */)
   VALUES (0, "homozygous dominant starter plant", null, null, 0, 9 /* other columns omitted for brevity */);
-- Yes, gene_sequence contains duplicate records; this is valid for the
-- problem domain space 
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 0, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 0, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 1, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 1, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 2, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 2, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 3, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 3, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 4, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 4, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 5, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 5, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 6, 0);
INSERT INTO gene_sequence (`org_id`,`gene_id`,`allele_id`) VALUES (0, 6, 0);
INSERT INTO `organism_phenotype` (`org_id`,`map_id`,`phenogroup_id`,`phenotype_id`) VALUES (0, 0, 0, 0);
INSERT INTO `organism_phenotype` (`org_id`,`map_id`,`phenogroup_id`,`phenotype_id`) VALUES (0, 0, 1, 0);
INSERT INTO `organism_phenotype` (`org_id`,`map_id`,`phenogroup_id`,`phenotype_id`) VALUES (0, 0, 2, 0);
INSERT INTO `organism_phenotype` (`org_id`,`map_id`,`phenogroup_id`,`phenotype_id`) VALUES (0, 0, 3, 0);
INSERT INTO `organism_phenotype` (`org_id`,`map_id`,`phenogroup_id`,`phenotype_id`) VALUES (0, 0, 4, 0);
INSERT INTO `organism_phenotype` (`org_id`,`map_id`,`phenogroup_id`,`phenotype_id`) VALUES (0, 0, 5, 0);
INSERT INTO `organism_phenotype` (`org_id`,`map_id`,`phenogroup_id`,`phenotype_id`) VALUES (0, 0, 6, 0);

当从数据库中搜索生物时，用户可以指定一个或多个生物必须表现/拥有的表型值或等位基因。通过根据指定条件构建临时表来应用这些限制。对于表型，生成的查询字符串如下所示：

CREATE TEMP TABLE pheno_temp1 AS
  SELECT org_id,count(org_id) AS 'pheno_count'
    FROM organism_phenotype
    WHERE (phenogroup_id=? AND phenotype_id=?)
      -- the OR clause appears optionally for each phenotype specified
      -- beyond the first
      OR (phenogroup_id=? AND phenotype_id=?)
    GROUP BY org_id

该字符串通过String []传递给rawQuery，该String []指定参数的替换值。

因此，如果用户希望获得所有显示紫色花朵（表群= 0，表型= 0）和轴向花朵放置（表群= 1，表型= 0）的豌豆植物的列表，则软件将定义查询字符串和selectionArgs，如下所示：

String queryString =
  "CREATE TEMP TABLE pheno_temp1 AS" +
    " SELECT org_id,count(org_id) AS 'pheno_count'" +
      " FROM organism_phenotype" +
      " WHERE (phenogroup_id=? AND phenotype_id=?)" +
        " OR (phenogroup_id=? AND phenotype_id=?)" +
      " GROUP BY org_id";
String[] selectionArgs = {"0","0","1","0"};

这（通常）按预期工作，并创建具有预期内容的表：

table pheno_temp1
org_id   pheno_count
--------------------
     0             2

对等位基因进行筛选的过程类似，但涉及两个级别的聚合和DISTINCT操作（并且由于基本问题相同，因此在此省略了这些详细信息）。

构建临时表之后，该过程的下一步是通过将有机体加入pheno_temp1和geno_temp2（等位基因过滤的最终工作表）来选择满足所有用户标准的org_id值。该步骤的查询通常如下所示：

SELECT organism.org_id FROM organism
  INNER JOIN pheno_temp1 ON organism.org_id=pheno_temp1.org_id
  INNER JOIN geno_temp2 ON organism.org_id=geno_temp2.org_id
    WHERE
      -- the query will include one or more of these clauses depending
      -- on the user's selections; the software takes care of adding the
      -- appropriate clauses and necessary glue (AND) between clauses
      pheno_temp1.pheno_count=?
      AND geno_temp2.allele_sum=?
      -- the query will include at most one of the birth_date clauses
      AND organism.birth_date<?
      --AND organism.birth_date=?
      --AND organism.birth_date>?
      --AND organism.birth_date BETWEEN ? AND ?
      -- the bbch_stage clause includes one parameter marker for each
      -- bbch stage selected by the user
      AND organism.bbch_stage IN (?,?,?)
      -- maternal and paternal lineage
      AND organism.mat_id=?
      AND organism.pat_id=?
    ORDER BY organism.org_id

如果用户请求了一个表型（即phenocount = 1）和一个bbch阶段（9），则sql查询字符串和选择参数将定义如下：

String queryString =
  "SELECT organism.org_id FROM organism" +
    " INNER JOIN pheno_temp1 ON organism.org_id=pheno_temp1.org_id" +
      " WHERE pheno_temp1.pheno_count=?" +
        " AND organism.bbch_stage IN (?)" +
      " ORDER BY organism.org_id";
String[] selectionArgs = {"1","9"};

SQL查询和选择参数都正确；有机体和pheno_temp1中的数据表明rawQuery调用应返回org_id = 0的一行（严格来说，游标指向包含org_id = 0的有机体记录的一行的结果集上的游标，但我将假设从这一点出发，每个阅读此书的人都知道我的意思）。

这是问题所在：查询不返回任何行。出于某些原因， CREATE TABLE AS SELECT查询中的聚合列不能与rawQuery（）中的参数标记一起使用（实际上，我的测试表明它们也不能在query（）中使用）。

事实证明，该解决方案正在改变为表型和等位基因过滤构建临时表的方式。在较早的步骤中显式创建了表，而不是使用 CREATE TABLE AS SELECT语句：

_mendelDatabase.execSQL("CREATE TEMP TABLE pheno_temp1 ('org_id' INT NOT NULL,'pheno_count' INT NOT NULL");

然后将用于填充该表的查询字符串更改为：

String queryString =
  "INSERT INTO pheno_temp1 (org_id,pheno_count)" +
    " SELECT org_id,count(org_id) AS 'pheno_count'" +
      " FROM organism_phenotype" +
      " WHERE (phenogroup_id=? AND phenotype_id=?)" +
        " OR (phenogroup_id=? AND phenotype_id=?)" +
      " GROUP BY org_id";

（对selectionArgs无需更改。）

最后的观察结果：关于参数标记和选择参数的许多（并非全部）问题暗示它们只能在基础表列为字符串类型时使用。事实证明这是不正确的。上面的所有代码都是直接从我发现此问题的应用程序中提取的，用于参数替换的大多数列都是整数类型；我证明通过更改临时表的创建方式，代码可以正常工作。

我想感谢CL的时间和精力。尽管CL并未提供解决问题的实际方法，但最终还是导致了一个测试案例，该案例指向了答案的方向（并且公平地讲，CL的原始问题并未指出内部连接列是来自集合函数）。