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 bestindexF

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}

finish_test