Debian Codegraph @

test/orderbyB.test

raw
1 # 2024-08-15
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.
12 #
13 # Specifically, it tests cases with order-by-subquery optimization in which
14 # an ORDER BY in a subquery is used to help resolve an ORDER BY in the
15 # outer query without having to do an extra sort.
16 #
17
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
20 set ::testprefix orderbyb
21
22 db null NULL
23 do_execsql_test 1.0 {
24 CREATE TABLE t1(a TEXT, b TEXT, c INT);
25 INSERT INTO t1 VALUES(NULL,NULL,NULL);
26 WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<7)
27 INSERT INTO t1(a,b,c) SELECT char(p,p), char(q,q), n FROM
28 (SELECT ((n-1)%4)+0x61 AS p, abs(n*2-9+(n>=5))+0x60 AS q, n FROM c);
29 UPDATE t1 SET b=upper(b) WHERE c=1;
30 CREATE TABLE t2(k TEXT PRIMARY KEY, v INT) WITHOUT ROWID;
31 WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<7)
32 INSERT INTO t2(k,v) SELECT char(0x60+n,0x60+n), n FROM c;
33 WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<7)
34 INSERT INTO t2(k,v) SELECT char(0x40+n,0x40+n), n FROM c;
35 SELECT a,b,c,tx.v AS 'v-a', ty.v AS 'v-b'
36 FROM t1 LEFT JOIN t2 AS tx ON tx.k=a
37 LEFT JOIN t2 AS ty ON ty.k=b
38 ORDER BY c;
39 } {
40 NULL NULL NULL NULL NULL
41 aa GG 1 1 7
42 bb ee 2 2 5
43 cc cc 3 3 3
44 dd aa 4 4 1
45 aa bb 5 1 2
46 bb dd 6 2 4
47 cc ff 7 3 6
48 }
49
50 do_eqp_execsql_test 1.1 {
51 WITH t3(x,y) AS (SELECT a, b FROM t1 ORDER BY a, b LIMIT 8)
52 SELECT x, y, v FROM t3 LEFT JOIN t2 ON k=t3.y ORDER BY x, y COLLATE nocase;
53 } {
54 QUERY PLAN
55 |--CO-ROUTINE t3
56 | |--SCAN t1
57 | `--USE TEMP B-TREE FOR ORDER BY
58 |--SCAN t3
59 |--SEARCH t2 USING PRIMARY KEY (k=?) LEFT-JOIN
60 `--USE TEMP B-TREE FOR LAST TERM OF ORDER BY
61 } {
62 NULL NULL NULL
63 aa bb 2
64 aa GG 7
65 bb dd 4
66 bb ee 5
67 cc cc 3
68 cc ff 6
69 dd aa 1
70 }
71
72 do_eqp_execsql_test 1.2 {
73 WITH t3(x,y) AS MATERIALIZED (SELECT a, b COLLATE nocase FROM t1 ORDER BY 1,2)
74 SELECT x, y, v FROM t3 LEFT JOIN t2 ON k=t3.y ORDER BY x,y;
75 } {
76 QUERY PLAN
77 |--MATERIALIZE t3
78 | |--SCAN t1
79 | `--USE TEMP B-TREE FOR ORDER BY
80 |--SCAN t3
81 `--SEARCH t2 USING PRIMARY KEY (k=?) LEFT-JOIN
82 } {
83 NULL NULL NULL
84 aa bb 2
85 aa GG 7
86 bb dd 4
87 bb ee 5
88 cc cc 3
89 cc ff 6
90 dd aa 1
91 }
92
93
94 finish_test