SQLite

  ** preserves SQLite's legacy behaviour in the following two cases:
  **
  **   FROM ... WHERE random()>0;           -- eval random() once per row
  **   FROM ... WHERE (SELECT random())>0;  -- eval random() once overall
  */
  for(ii=0; ii<sWLB.pWC->nTerm; ii++){
    WhereTerm *pT = &sWLB.pWC->a[ii];
    if( pT->wtFlags & TERM_VIRTUAL ) continue;
    if( pT->prereqAll==0 && (nTabList==0 || exprIsDeterministic(pT->pExpr)) ){
      sqlite3ExprIfFalse(pParse, pT->pExpr, pWInfo->iBreak, SQLITE_JUMPIFNULL);
      pT->wtFlags |= TERM_CODED;
    }
  }

  if( wctrlFlags & WHERE_WANT_DISTINCT ){

    INSERT INTO t VALUES('{"foo":"meep","other":12345}');
    INSERT INTO t VALUES('{"foo":"bingo","alt":5.25}');
    SELECT * FROM t
     WHERE(EXISTS(SELECT 1 FROM json_each(t.json,"$.foo") j
                   WHERE j.value = 'meep'));
  } {{{"foo":"meep","other":12345}}}
}

# 2018-01-27
# Ticket https://sqlite.org/src/tktview/ec32177c99ccac2b180fd3ea2083
# Incorrect result when using the new OR clause factoring optimization
#
# This is the original test case as reported on the sqlite-users mailing
# list
#
do_execsql_test 7.1 {
  DROP TABLE IF EXISTS cd;
  CREATE TABLE cd ( cdid INTEGER PRIMARY KEY NOT NULL, genreid integer );
  CREATE INDEX cd_idx_genreid ON cd (genreid);
  INSERT INTO cd  ( cdid, genreid ) VALUES
                     ( 1,    1 ),
                     ( 2, NULL ),
                     ( 3, NULL ),
                     ( 4, NULL ),
                     ( 5, NULL );
  
  SELECT cdid
    FROM cd me
  WHERE 2 > (
    SELECT COUNT( * )
      FROM cd rownum__emulation
    WHERE
      (
        me.genreid IS NOT NULL
          AND
        rownum__emulation.genreid IS NULL
      )
        OR
      (
        me.genreid IS NOT NULL
          AND
        rownum__emulation.genreid IS NOT NULL
          AND
        rownum__emulation.genreid < me.genreid
      )
        OR
      (
        ( me.genreid = rownum__emulation.genreid OR ( me.genreid IS NULL
  AND rownum__emulation.genreid IS NULL ) )
          AND
        rownum__emulation.cdid > me.cdid
      )
  );
} {4 5}

# Simplified test cases from the ticket
#
do_execsql_test 7.2 {
  DROP TABLE IF EXISTS t1;
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
  INSERT INTO t1(a,b) VALUES(1,1);
  CREATE TABLE t2(aa INTEGER PRIMARY KEY, bb);
  INSERT INTO t2(aa,bb) VALUES(1,1),(2,NULL),(3,NULL);
  SELECT (
    SELECT COUNT(*) FROM t2
     WHERE ( t1.b IS NOT NULL AND t2.bb IS NULL )
        OR ( t2.bb < t1.b )
        OR ( t1.b IS t2.bb AND t2.aa > t1.a )
    )
    FROM t1;
} {2}


finish_test