AryaWu/sqlite

1# 2024-10-052#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library.  The12# focus of this file is testing indexes on expressions.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix indexexpr318 19 20do_execsql_test 1.0 {21  CREATE TABLE t1(a, j);22  INSERT INTO t1 VALUES(1, '{x:"one"}');23  INSERT INTO t1 VALUES(2, '{x:"two"}');24  INSERT INTO t1 VALUES(3, '{x:"three"}');25 26  CREATE INDEX i1 ON t1( json_extract(j, '$.x') );27  CREATE INDEX i2 ON t1( a, json_extract(j, '$.x') );28}29 30proc do_hasfunction_test {tn sql res} {31  set nFunction 032  db eval "EXPLAIN $sql" x {33    if {$x(opcode)=="Function"} {34      incr nFunction 35    }36  }37 38  do_execsql_test $tn "39    SELECT $nFunction;40    $sql41  " $res42}43 44do_hasfunction_test 1.1 {45  SELECT json_extract(j, '$.x') FROM t1 ORDER BY 1;46} {47  0 one three two48}49 50do_hasfunction_test 1.2 {51  SELECT json_extract(j, '$.x') FROM t1 WHERE a=252} {53  0 two54}55 56do_hasfunction_test 1.3 {57  SELECT coalesce(json_extract(j, '$.x'), 'five') FROM t1 WHERE a=258} {59  0 two60}61 62do_hasfunction_test 1.4 {63  SELECT json_extract(j, '$.x') || '.two' FROM t1 WHERE a=264} {65  0 two.two66}67 68do_hasfunction_test 1.5 {69  SELECT json_insert( '{}', '$.y', json_extract(j, '$.x') ) FROM t1 WHERE a=270} {71  2 {{"y":"two"}}72}73 74do_hasfunction_test 1.6 {75  SELECT json_insert( '{}', '$.y', coalesce( json_extract(j, '$.x'), 'five' ) )76  FROM t1 WHERE a=277} {78  2 {{"y":"two"}}79}80 81#-------------------------------------------------------------------------82reset_db83do_execsql_test 2.0 {84  CREATE TABLE t1(a, b, j);85  CREATE INDEX i1 ON t1( a, json_extract(j, '$.x') );86}87 88do_eqp_test 2.1 {89  SELECT json_extract(j, '$.x') FROM t1 WHERE a=?90} {91  t1 USING COVERING INDEX i192}93 94do_eqp_test 2.2 {95  SELECT b, json_extract(j, '$.x') FROM t1 WHERE a=?96} {97  t1 USING INDEX i198}99 100do_eqp_test 2.3 {101  SELECT json_insert( '{}', json_extract(j, '$.x') ) FROM t1 WHERE a=?102} {103  t1 USING INDEX i1104}105 106do_eqp_test 2.4 {107  SELECT sum( json_extract(j, '$.x') ) FROM t1 WHERE a=?108} {109  t1 USING COVERING INDEX i1110}111 112do_eqp_test 2.5 {113  SELECT json_extract(j, '$.x'), sum( json_extract(j, '$.x') ) FROM t1 WHERE a=?114} {115  t1 USING INDEX i1116}117 118 119 120finish_test121 122