/ Check-in [20f7951b]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Allow SQLite to omit redundant ORDER BY sorts in the case where a SELECT statement has GROUP BY and ORDER BY clauses that use the same expressions, even when the ORDER BY expressions are marked "DESC".
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 20f7951bb238ddc0b8932a55145df426b6fdf7b8631e069345902c853c90f191
User & Date: dan 2019-09-21 15:44:12
Context
2019-09-21
17:31
Fix harmless compiler warnings. check-in: 8ea1dc72 user: drh tags: trunk
15:44
Allow SQLite to omit redundant ORDER BY sorts in the case where a SELECT statement has GROUP BY and ORDER BY clauses that use the same expressions, even when the ORDER BY expressions are marked "DESC". check-in: 20f7951b user: dan tags: trunk
13:34
Add --multithread, --serialized, and --singlethread options to the speed-check.sh test script. check-in: c17078af user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

6219
6220
6221
6222
6223
6224
6225
























6226
6227
6228
6229
6230
6231
6232
6233
6234
6235
6236
6237
6238
6239
6240
6241
6242
6243
6244
6245
6246
6247
6248
6249
        pItem->u.x.iAlias = 0;
      }
      for(k=pGroupBy->nExpr, pItem=pGroupBy->a; k>0; k--, pItem++){
        pItem->u.x.iAlias = 0;
      }
      assert( 66==sqlite3LogEst(100) );
      if( p->nSelectRow>66 ) p->nSelectRow = 66;
























    }else{
      assert( 0==sqlite3LogEst(1) );
      p->nSelectRow = 0;
    }

    /* If there is both a GROUP BY and an ORDER BY clause and they are
    ** identical, then it may be possible to disable the ORDER BY clause 
    ** on the grounds that the GROUP BY will cause elements to come out 
    ** in the correct order. It also may not - the GROUP BY might use a
    ** database index that causes rows to be grouped together as required
    ** but not actually sorted. Either way, record the fact that the
    ** ORDER BY and GROUP BY clauses are the same by setting the orderByGrp
    ** variable.  */
    if( sqlite3ExprListCompare(pGroupBy, sSort.pOrderBy, -1)==0 ){
      orderByGrp = 1;
    }
 
    /* Create a label to jump to when we want to abort the query */
    addrEnd = sqlite3VdbeMakeLabel(pParse);

    /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in
    ** sAggInfo for all TK_AGG_FUNCTION nodes in expressions of the
    ** SELECT statement.
    */







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>





<
<
<
<
<
<
<
<
<
<
<
<







6219
6220
6221
6222
6223
6224
6225
6226
6227
6228
6229
6230
6231
6232
6233
6234
6235
6236
6237
6238
6239
6240
6241
6242
6243
6244
6245
6246
6247
6248
6249
6250
6251
6252
6253
6254












6255
6256
6257
6258
6259
6260
6261
        pItem->u.x.iAlias = 0;
      }
      for(k=pGroupBy->nExpr, pItem=pGroupBy->a; k>0; k--, pItem++){
        pItem->u.x.iAlias = 0;
      }
      assert( 66==sqlite3LogEst(100) );
      if( p->nSelectRow>66 ) p->nSelectRow = 66;

      /* If there is both a GROUP BY and an ORDER BY clause and they are
      ** identical, then it may be possible to disable the ORDER BY clause 
      ** on the grounds that the GROUP BY will cause elements to come out 
      ** in the correct order. It also may not - the GROUP BY might use a
      ** database index that causes rows to be grouped together as required
      ** but not actually sorted. Either way, record the fact that the
      ** ORDER BY and GROUP BY clauses are the same by setting the orderByGrp
      ** variable.  */
      if( sSort.pOrderBy && pGroupBy->nExpr==sSort.pOrderBy->nExpr ){
        int i;
        /* The GROUP BY processing doesn't care whether rows are delivered in
        ** ASC or DESC order - only that each group is returned contiguously.
        ** So set the ASC/DESC flags in the GROUP BY to match those in the 
        ** ORDER BY to maximize the chances of rows being delivered in an 
        ** order that makes the ORDER BY redundant.  */
        for(i=0; i<pGroupBy->nExpr; i++){
          u8 sortFlags = sSort.pOrderBy->a[i].sortFlags & KEYINFO_ORDER_DESC;
          pGroupBy->a[i].sortFlags = sortFlags;
        }
        if( sqlite3ExprListCompare(pGroupBy, sSort.pOrderBy, -1)==0 ){
          orderByGrp = 1;
        }
      }
    }else{
      assert( 0==sqlite3LogEst(1) );
      p->nSelectRow = 0;
    }













    /* Create a label to jump to when we want to abort the query */
    addrEnd = sqlite3VdbeMakeLabel(pParse);

    /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in
    ** sAggInfo for all TK_AGG_FUNCTION nodes in expressions of the
    ** SELECT statement.
    */

Added test/orderbyA.test.







































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
# 2019-09-21
#
# 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# Specifically, it tests cases where the expressions in a GROUP BY 
# clause are the same as those in the ORDER BY clause.
# 

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::testprefix orderbyA

proc do_sortcount_test {tn sql cnt res} {
  set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
  set rcnt [regexp -all {USE TEMP} $eqp]
  uplevel [list do_test         $tn.1 [list set {} $rcnt] $cnt]
  uplevel [list do_execsql_test $tn.2 $sql $res]
}

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c);
  INSERT INTO t1 VALUES('one',   1, 11);
  INSERT INTO t1 VALUES('three', 7, 11);
  INSERT INTO t1 VALUES('one',   2, 11);
  INSERT INTO t1 VALUES('one',   3, 11);
  INSERT INTO t1 VALUES('two',   4, 11);
  INSERT INTO t1 VALUES('two',   6, 11);
  INSERT INTO t1 VALUES('three', 8, 11);
  INSERT INTO t1 VALUES('two',   5, 11);
  INSERT INTO t1 VALUES('three', 9, 11);
}

foreach {tn idx} {
  1 {}
  2 {CREATE INDEX i1 ON t1(a)}
  3 {CREATE INDEX i1 ON t1(a DESC)}
} {
  execsql { DROP INDEX IF EXISTS i1 }
  execsql $idx

  # $match is the number of temp-table sorts we expect if the GROUP BY
  # can use the same sort order as the ORDER BY. $nomatch is the number
  # of expected sorts if the GROUP BY and ORDER BY are not compatible.
  set match   1
  set nomatch 2
  if {$tn>=2} {
    set match   0
    set nomatch 1
  }

  do_sortcount_test 1.$tn.1.1 {
    SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a
  } $match {one 6 three 24 two 15}
  do_sortcount_test 1.$tn.1.2 {
    SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a DESC
  } $match {two 15 three 24 one 6}
  
  do_sortcount_test 1.$tn.2.1 {
    SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a||''
  } $nomatch {one 6 three 24 two 15}
  do_sortcount_test 1.$tn.2.2 {
    SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a||'' DESC
  } $nomatch {two 15 three 24 one 6}
  
  do_sortcount_test 1.$tn.3.1 {
    SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a NULLS LAST
  } $nomatch {one 6 three 24 two 15}
  do_sortcount_test 1.$tn.3.2 {
    SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a DESC NULLS FIRST
  } $nomatch {two 15 three 24 one 6}
}

#-------------------------------------------------------------------------
do_execsql_test 2.0 {
  CREATE TABLE t2(a, b, c);
  INSERT INTO t2 VALUES(1, 'one', 1);
  INSERT INTO t2 VALUES(1, 'two', 2);
  INSERT INTO t2 VALUES(1, 'one', 3);
  INSERT INTO t2 VALUES(1, 'two', 4);
  INSERT INTO t2 VALUES(1, 'one', 5);
  INSERT INTO t2 VALUES(1, 'two', 6);

  INSERT INTO t2 VALUES(2, 'one', 7);
  INSERT INTO t2 VALUES(2, 'two', 8);
  INSERT INTO t2 VALUES(2, 'one', 9);
  INSERT INTO t2 VALUES(2, 'two', 10);
  INSERT INTO t2 VALUES(2, 'one', 11);
  INSERT INTO t2 VALUES(2, 'two', 12);

  INSERT INTO t2 VALUES(NULL, 'one', 13);
  INSERT INTO t2 VALUES(NULL, 'two', 14);
  INSERT INTO t2 VALUES(NULL, 'one', 15);
  INSERT INTO t2 VALUES(NULL, 'two', 16);
  INSERT INTO t2 VALUES(NULL, 'one', 17);
  INSERT INTO t2 VALUES(NULL, 'two', 18);
}

foreach {tn idx} {
  1 {}

  2 { CREATE INDEX i2 ON t2(a, b)           }
  3 { CREATE INDEX i2 ON t2(a DESC, b DESC) }

  4 { CREATE INDEX i2 ON t2(a, b DESC)      }
  5 { CREATE INDEX i2 ON t2(a DESC, b)      }
} {
  execsql { DROP INDEX IF EXISTS i2 }
  execsql $idx


  set nSort [expr ($tn==2 || $tn==3) ? 0 : 1]
  do_sortcount_test 2.$tn.1.1 {
    SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a, b;
  } $nSort {{} one 45  {} two 48  1 one 9  1 two 12  2 one 27  2 two 30}
  do_sortcount_test 2.$tn.1.2 {
    SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a DESC, b DESC;
  } $nSort {2 two 30  2 one 27  1 two 12  1 one 9  {} two 48  {} one 45}

  set nSort [expr ($tn==4 || $tn==5) ? 0 : 1]
  do_sortcount_test 2.$tn.2.1 {
    SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a, b DESC;
  } $nSort { {} two 48  {} one 45  1 two 12  1 one 9  2 two 30 2 one 27 }
  do_sortcount_test 2.$tn.2.2 {
    SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a DESC, b;
  } $nSort { 2 one 27  2 two 30  1 one 9  1 two 12  {} one 45 {} two 48 }

  # ORDER BY can never piggyback on the GROUP BY sort if it uses 
  # non-standard NULLS behaviour.
  set nSort [expr $tn==1 ? 2 : 1]
  do_sortcount_test 2.$tn.3.1 {
    SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a, b DESC NULLS FIRST;
  } $nSort { {} two 48  {} one 45  1 two 12  1 one 9  2 two 30 2 one 27 }
  do_sortcount_test 2.$tn.3.2 {
    SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a DESC, b NULLS LAST;
  } $nSort { 2 one 27  2 two 30  1 one 9  1 two 12  {} one 45 {} two 48 }
}


finish_test

Changes to test/tkt-b75a9ca6b0.test.

56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
  6 "SELECT * FROM t1 GROUP BY y ORDER BY x"
  {1 3  2 2  3 1} {$tblscan*$grpsort*$sort}

  7 "SELECT * FROM t1 GROUP BY x, y ORDER BY x, y DESC"
  {1 3  2 2  3 1} {$idxscan*$sort}

  8 "SELECT * FROM t1 GROUP BY x, y ORDER BY x DESC, y DESC"
  {3 1  2 2  1 3} {$idxscan*$sort}

  9 "SELECT * FROM t1 GROUP BY x, y ORDER BY x ASC, y ASC"
  {1 3  2 2  3 1} {$idxscan}

  10 "SELECT * FROM t1 GROUP BY x, y ORDER BY x COLLATE nocase, y"
  {1 3  2 2  3 1} {$idxscan*$sort}








|







56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
  6 "SELECT * FROM t1 GROUP BY y ORDER BY x"
  {1 3  2 2  3 1} {$tblscan*$grpsort*$sort}

  7 "SELECT * FROM t1 GROUP BY x, y ORDER BY x, y DESC"
  {1 3  2 2  3 1} {$idxscan*$sort}

  8 "SELECT * FROM t1 GROUP BY x, y ORDER BY x DESC, y DESC"
  {3 1  2 2  1 3} {$idxscan}

  9 "SELECT * FROM t1 GROUP BY x, y ORDER BY x ASC, y ASC"
  {1 3  2 2  3 1} {$idxscan}

  10 "SELECT * FROM t1 GROUP BY x, y ORDER BY x COLLATE nocase, y"
  {1 3  2 2  3 1} {$idxscan*$sort}