where2.test

sqlite-3.4.1,嵌入式数据库.是一个功能强大的开源数据库,给学习和研发以及小型公司的发展带来了全所未有的好处.

do_test where2-6.11 {
  # This will not attempt the OR optimization because of the a=b
  # comparison.
  queryplan {
    SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello';
  }
} {123 0123 nosort t2249b {} t2249a {}}
do_test where2-6.11.2 {
  # Permutations of the expression terms.
  queryplan {
    SELECT * FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello';
  }
} {123 0123 nosort t2249b {} t2249a {}}
do_test where2-6.11.3 {
  # Permutations of the expression terms.
  queryplan {
    SELECT * FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a;
  }
} {123 0123 nosort t2249b {} t2249a {}}
do_test where2-6.11.4 {
  # Permutations of the expression terms.
  queryplan {
    SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a;
  }
} {123 0123 nosort t2249b {} t2249a {}}
do_test where2-6.12 {
  # In this case, the +b disables the affinity conflict and allows
  # the OR optimization to be used again.  The result is now an empty
  # set, the same as in where2-6.9.
  queryplan {
    SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello';
  }
} {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
do_test where2-6.12.2 {
  # In this case, the +b disables the affinity conflict and allows
  # the OR optimization to be used again.  The result is now an empty
  # set, the same as in where2-6.9.
  queryplan {
    SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a;
  }
} {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
do_test where2-6.12.3 {
  # In this case, the +b disables the affinity conflict and allows
  # the OR optimization to be used again.  The result is now an empty
  # set, the same as in where2-6.9.
  queryplan {
    SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello';
  }
} {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
do_test where2-6.13 {
  # The addition of +a on the second term disabled the OR optimization.
  # But we should still get the same empty-set result as in where2-6.9.
  queryplan {
    SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello';
  }
} {nosort t2249b {} t2249a {}}

# Variations on the order of terms in a WHERE clause in order
# to make sure the OR optimizer can recognize them all.
do_test where2-6.20 {
  queryplan {
    SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a
  }
} {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
do_test where2-6.21 {
  queryplan {
    SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a OR y.a='hello'
  }
} {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
do_test where2-6.22 {
  queryplan {
    SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a=x.a OR y.a='hello'
  }
} {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
do_test where2-6.23 {
  queryplan {
    SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a='hello' OR x.a=y.a
  }
} {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}

# Unique queries (queries that are guaranteed to return only a single
# row of result) do not call the sorter.  But all tables must give
# a unique result.  If any one table in the join does not give a unique
# result then sorting is necessary.
#
do_test where2-7.1 {
  cksort {
    create table t8(a unique, b, c);
    insert into t8 values(1,2,3);
    insert into t8 values(2,3,4);
    create table t9(x,y);
    insert into t9 values(2,4);
    insert into t9 values(2,3);
    select y from t8, t9 where a=1 order by a, y;
  }
} {3 4 sort}
do_test where2-7.2 {
  cksort {
    select * from t8 where a=1 order by b, c
  }
} {1 2 3 nosort}
do_test where2-7.3 {
  cksort {
    select * from t8, t9 where a=1 and y=3 order by b, x
  }
} {1 2 3 2 3 sort}
do_test where2-7.4 {
  cksort {
    create unique index i9y on t9(y);
    select * from t8, t9 where a=1 and y=3 order by b, x
  }
} {1 2 3 2 3 nosort}

# Ticket #1807.  Using IN constrains on multiple columns of
# a multi-column index.
#
ifcapable subquery {
  do_test where2-8.1 {
    execsql {
      SELECT * FROM t1 WHERE x IN (20,21) AND y IN (1,2)
    }
  } {}
  do_test where2-8.2 {
    execsql {
      SELECT * FROM t1 WHERE x IN (1,2) AND y IN (-5,-6)
    }
  } {}
  execsql {CREATE TABLE tx AS SELECT * FROM t1}
  do_test where2-8.3 {
    execsql {
      SELECT w FROM t1
       WHERE x IN (SELECT x FROM tx WHERE rowid<0)
         AND +y IN (SELECT y FROM tx WHERE rowid=1)
    }
  } {}
  do_test where2-8.4 {
    execsql {
      SELECT w FROM t1
       WHERE x IN (SELECT x FROM tx WHERE rowid=1)
         AND y IN (SELECT y FROM tx WHERE rowid<0)
    }
  } {}
  #set sqlite_where_trace 1
  do_test where2-8.5 {
    execsql {
      CREATE INDEX tx_xyz ON tx(x, y, z, w);
      SELECT w FROM tx
       WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
         AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
         AND z IN (SELECT z FROM t1 WHERE w BETWEEN 12 AND 14)
    }
  } {12 13 14}
  do_test where2-8.6 {
    execsql {
      SELECT w FROM tx
       WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
         AND y IN (SELECT y FROM t1 WHERE w BETWEEN 12 AND 14)
         AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
    }
  } {12 13 14}
  do_test where2-8.7 {
    execsql {
      SELECT w FROM tx
       WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 12 AND 14)
         AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
         AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
    }
  } {10 11 12 13 14 15}
  do_test where2-8.8 {
    execsql {
      SELECT w FROM tx
       WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
         AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
         AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
    }
  } {10 11 12 13 14 15 16 17 18 19 20}
  do_test where2-8.9 {
    execsql {
      SELECT w FROM tx
       WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
         AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
         AND z IN (SELECT z FROM t1 WHERE w BETWEEN 2 AND 4)
    }
  } {}
  do_test where2-8.10 {
    execsql {
      SELECT w FROM tx
       WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
         AND y IN (SELECT y FROM t1 WHERE w BETWEEN 2 AND 4)
         AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
    }
  } {}
  do_test where2-8.11 {
    execsql {
      SELECT w FROM tx
       WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 2 AND 4)
         AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
         AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
    }
  } {}
  do_test where2-8.12 {
    execsql {
      SELECT w FROM tx
       WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
         AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
         AND z IN (SELECT z FROM t1 WHERE w BETWEEN -4 AND -2)
    }
  } {}
  do_test where2-8.13 {
    execsql {
      SELECT w FROM tx
       WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
         AND y IN (SELECT y FROM t1 WHERE w BETWEEN -4 AND -2)
         AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
    }
  } {}
  do_test where2-8.14 {
    execsql {
      SELECT w FROM tx
       WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN -4 AND -2)
         AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
         AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
    }
  } {}
  do_test where2-8.15 {
    execsql {
      SELECT w FROM tx
       WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
         AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
         AND z IN (SELECT z FROM t1 WHERE w BETWEEN 200 AND 300)
    }
  } {}
  do_test where2-8.16 {
    execsql {
      SELECT w FROM tx
       WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
         AND y IN (SELECT y FROM t1 WHERE w BETWEEN 200 AND 300)
         AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
    }
  } {}
  do_test where2-8.17 {
    execsql {
      SELECT w FROM tx
       WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 200 AND 300)
         AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
         AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
    }
  } {}
  do_test where2-8.18 {
    execsql {
      SELECT w FROM tx
       WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 10 AND 20)
         AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20)
         AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 200 AND 300)
    }
  } {}
  do_test where2-8.19 {
    execsql {
      SELECT w FROM tx
       WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 10 AND 20)
         AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 200 AND 300)
         AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20)
    }
  } {}
  do_test where2-8.20 {
    execsql {
      SELECT w FROM tx
       WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 200 AND 300)
         AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20)
         AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20)
    }
  } {}
}  

# Make sure WHERE clauses of the form A=1 AND (B=2 OR B=3) are optimized
# when we have an index on A and B.
#
ifcapable or_opt {
  do_test where2-9.1 {
    execsql {
      BEGIN;
      CREATE TABLE t10(a,b,c);
      INSERT INTO t10 VALUES(1,1,1);
      INSERT INTO t10 VALUES(1,2,2);
      INSERT INTO t10 VALUES(1,3,3);
    }
    for {set i 4} {$i<=1000} {incr i} {
      execsql {INSERT INTO t10 VALUES(1,$i,$i)}
    }
    execsql {
      CREATE INDEX i10 ON t10(a,b);
      COMMIT;
      SELECT count(*) FROM t10;
    }
  } 1000
  do_test where2-9.2 {
    count {
      SELECT * FROM t10 WHERE a=1 AND (b=2 OR b=3)
    }
  } {1 2 2 1 3 3 7}
}

finish_test