Debian Codegraph @

test/indexexpr3.test

raw
1 # 2024-10-05
2 #
3 # The author disclaims copyright to this source code. In place of
4 # 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. The
12 # focus of this file is testing indexes on expressions.
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix indexexpr3
18
19
20 do_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
30 proc do_hasfunction_test {tn sql res} {
31 set nFunction 0
32 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 $sql
41 " $res
42 }
43
44 do_hasfunction_test 1.1 {
45 SELECT json_extract(j, '$.x') FROM t1 ORDER BY 1;
46 } {
47 0 one three two
48 }
49
50 do_hasfunction_test 1.2 {
51 SELECT json_extract(j, '$.x') FROM t1 WHERE a=2
52 } {
53 0 two
54 }
55
56 do_hasfunction_test 1.3 {
57 SELECT coalesce(json_extract(j, '$.x'), 'five') FROM t1 WHERE a=2
58 } {
59 0 two
60 }
61
62 do_hasfunction_test 1.4 {
63 SELECT json_extract(j, '$.x') || '.two' FROM t1 WHERE a=2
64 } {
65 0 two.two
66 }
67
68 do_hasfunction_test 1.5 {
69 SELECT json_insert( '{}', '$.y', json_extract(j, '$.x') ) FROM t1 WHERE a=2
70 } {
71 2 {{"y":"two"}}
72 }
73
74 do_hasfunction_test 1.6 {
75 SELECT json_insert( '{}', '$.y', coalesce( json_extract(j, '$.x'), 'five' ) )
76 FROM t1 WHERE a=2
77 } {
78 2 {{"y":"two"}}
79 }
80
81 #-------------------------------------------------------------------------
82 reset_db
83 do_execsql_test 2.0 {
84 CREATE TABLE t1(a, b, j);
85 CREATE INDEX i1 ON t1( a, json_extract(j, '$.x') );
86 }
87
88 do_eqp_test 2.1 {
89 SELECT json_extract(j, '$.x') FROM t1 WHERE a=?
90 } {
91 t1 USING COVERING INDEX i1
92 }
93
94 do_eqp_test 2.2 {
95 SELECT b, json_extract(j, '$.x') FROM t1 WHERE a=?
96 } {
97 t1 USING INDEX i1
98 }
99
100 do_eqp_test 2.3 {
101 SELECT json_insert( '{}', json_extract(j, '$.x') ) FROM t1 WHERE a=?
102 } {
103 t1 USING INDEX i1
104 }
105
106 do_eqp_test 2.4 {
107 SELECT sum( json_extract(j, '$.x') ) FROM t1 WHERE a=?
108 } {
109 t1 USING COVERING INDEX i1
110 }
111
112 do_eqp_test 2.5 {
113 SELECT json_extract(j, '$.x'), sum( json_extract(j, '$.x') ) FROM t1 WHERE a=?
114 } {
115 t1 USING INDEX i1
116 }
117
118
119
120 finish_test
121