AryaWu/sqlite

1# 2024-08-152#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.12#13# Specifically, it tests cases with order-by-subquery optimization in which14# an ORDER BY in a subquery is used to help resolve an ORDER BY in the15# outer query without having to do an extra sort.16# 17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20set ::testprefix orderbyb21 22db null NULL23do_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 FROM28            (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=a37            LEFT JOIN t2 AS ty ON ty.k=b38   ORDER BY c;39} {40  NULL  NULL  NULL  NULL  NULL41  aa    GG    1     1     742  bb    ee    2     2     543  cc    cc    3     3     344  dd    aa    4     4     145  aa    bb    5     1     246  bb    dd    6     2     447  cc    ff    7     3     648}49 50do_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 PLAN55  |--CO-ROUTINE t356  |  |--SCAN t157  |  `--USE TEMP B-TREE FOR ORDER BY58  |--SCAN t359  |--SEARCH t2 USING PRIMARY KEY (k=?) LEFT-JOIN60  `--USE TEMP B-TREE FOR LAST TERM OF ORDER BY61} {62  NULL  NULL  NULL63  aa    bb    264  aa    GG    765  bb    dd    466  bb    ee    567  cc    cc    368  cc    ff    669  dd    aa    170}71 72do_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 PLAN77  |--MATERIALIZE t378  |  |--SCAN t179  |  `--USE TEMP B-TREE FOR ORDER BY80  |--SCAN t381  `--SEARCH t2 USING PRIMARY KEY (k=?) LEFT-JOIN82} {83  NULL  NULL  NULL84  aa    bb    285  aa    GG    786  bb    dd    487  bb    ee    588  cc    cc    389  cc    ff    690  dd    aa    191}92 93 94finish_test95