sqlite/test/bestindexG.test

# 2025-07-14
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# 

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix bestindexG

ifcapable !vtab {
  finish_test
  return
}


proc vtab_command {method args} {
  switch -- $method {
    xConnect {
      return "CREATE TABLE tt(id, k)"
    }

    xBestIndex {
      set hdl [lindex $args 0]
      set conslist [$hdl constraints]

      set ret [list]
      set idxnum 0
      set iCons 0
      foreach cons $conslist {
        array set c $cons
        if {$c(usable) && $c(op)=="eq" && $c(column)==0} {
          lappend ret use $iCons
          set idxnum 1
          break
        }
        incr iCons
      }

      return [concat idxnum $idxnum $ret]
    }

    xFilter {
      set idxnum [lindex $args 0]
      if {$idxnum==0} {
        return [list sql "SELECT * FROM t1"]
      }

      return [list sql "SELECT * FROM t1 WHERE id=[lindex $args 2 0]"]
    }
  }

  return {}
}

register_tcl_module db

do_execsql_test 1.0 {
  CREATE TABLE t1(rowid INTEGER PRIMARY KEY, id, k);
  INSERT INTO t1 VALUES(1, 1, 1);
  INSERT INTO t1 VALUES(2, 2, 2);
  INSERT INTO t1 VALUES(3, 3, 3);
  INSERT INTO t1 VALUES(4, 4, 4);

  INSERT INTO t1 VALUES(5, 1, 11);
  INSERT INTO t1 VALUES(6, 2, 22);
  INSERT INTO t1 VALUES(7, 3, 33);
  INSERT INTO t1 VALUES(8, 4, 44);

  CREATE VIRTUAL TABLE tt USING tcl(vtab_command)
}

do_execsql_test 1.1 {
  CREATE TABLE probe(id);
  CREATE TABLE pr(id, k);
  CREATE TABLE sl(id, dk);

  INSERT INTO probe VALUES(3), (44);

  INSERT INTO pr VALUES(3, 3);
  INSERT INTO sl VALUES(3, 3);
} {}

do_execsql_test 1.2 {
  with v as (
      select sl.id as id
      from tt
      left join pr on pr.k = tt.k
      join sl on sl.dk = tt.id
  )
  select count(*) from probe join v on v.id = probe.id;
} {2}

#-------------------------------------------------------------------------
# Report https://sqlite.org/bugs/forumpost/aec494fd56
#
reset_db 
ifcapable fts5 {
  sqlite3 db ""
  register_tcl_module db
  
  proc vtab_command {method args} {
    switch -- $method {
      xConnect {
        return "CREATE TABLE x(a PRIMARY KEY, b) WITHOUT ROWID"
      }
    }
    return {}
  }
  
  do_execsql_test 2.0 {
    CREATE VIRTUAL TABLE v40 USING fts5(c);
    BEGIN EXCLUSIVE;
      CREATE VIRTUAL TABLE v115 USING tcl(vtab_command);
  }
  
  do_catchsql_test 2.1 {
    CREATE VIRTUAL TABLE v123 USING fts5;
  } {1 {vtable constructor failed: v123}}
  
  do_catchsql_test 2.2 {
    CREATE TABLE v124 (
        a AS(a),
        b INT
    );
  } {1 {generated column loop on "a"}}
  
  do_execsql_test 2.3 {
    PRAGMA quick_check;
  } {ok}
  
  do_execsql_test 2.4 {
      INSERT INTO v40 VALUES('one two three');
      SELECT rowid FROM v40('one');
    COMMIT;
    SELECT rowid FROM v40('two');
  } {1 1}
}


finish_test