SQLite

Check-in [1902923308]
Login

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

Overview
Comment::-) (CVS 76)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 19029233082e319d4bfd94b22a694c917d8f0296
User & Date: drh 2000-06-08 00:28:52.000
Context
2000-06-08
01:55
:-) (CVS 77) (check-in: b3fb15ccde user: drh tags: trunk)
00:28
:-) (CVS 76) (check-in: 1902923308 user: drh tags: trunk)
00:19
:-) (CVS 75) (check-in: c47d552e7e user: drh tags: trunk)
Changes
Unified Diff Show Whitespace Changes Patch
Changes to src/select.c.
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements.
**
** $Id: select.c,v 1.17 2000/06/08 00:19:03 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/







|







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements.
**
** $Id: select.c,v 1.18 2000/06/08 00:28:52 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
505
506
507
508
509
510
511



512
513
514
515
516
517
518
      /* Convert the data in the temporary table into whatever form
      ** it is that we currently need.
      */      
      if( eDest!=priorOp ){
        int iCont, iBreak;
        assert( p->pEList );
        generateColumnNames(pParse, 0, p->pEList);



        iBreak = sqliteVdbeMakeLabel(v);
        iCont = sqliteVdbeAddOp(v, OP_Next, unionTab, iBreak, 0, 0);
        rc = selectInnerLoop(pParse, 0, unionTab, p->pEList->nExpr,
                             p->pOrderBy, -1, eDest, iParm, 
                             iCont, iBreak);
        if( rc ) return 1;
        sqliteVdbeAddOp(v, OP_Goto, 0, iCont, 0, 0);







>
>
>







505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
      /* Convert the data in the temporary table into whatever form
      ** it is that we currently need.
      */      
      if( eDest!=priorOp ){
        int iCont, iBreak;
        assert( p->pEList );
        generateColumnNames(pParse, 0, p->pEList);
        if( p->pOrderBy ){
          sqliteVdbeAddOp(v, OP_SortOpen, 0, 0, 0, 0);
        }
        iBreak = sqliteVdbeMakeLabel(v);
        iCont = sqliteVdbeAddOp(v, OP_Next, unionTab, iBreak, 0, 0);
        rc = selectInnerLoop(pParse, 0, unionTab, p->pEList->nExpr,
                             p->pOrderBy, -1, eDest, iParm, 
                             iCont, iBreak);
        if( rc ) return 1;
        sqliteVdbeAddOp(v, OP_Goto, 0, iCont, 0, 0);
554
555
556
557
558
559
560



561
562
563
564
565
566
567
      if( rc ) return rc;

      /* Generate code to take the intersection of the two temporary
      ** tables.
      */
      assert( p->pEList );
      generateColumnNames(pParse, 0, p->pEList);



      iBreak = sqliteVdbeMakeLabel(v);
      iCont = sqliteVdbeAddOp(v, OP_Next, tab1, iBreak, 0, 0);
      sqliteVdbeAddOp(v, OP_Key, tab1, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_NotFound, tab2, iCont, 0, 0);
      rc = selectInnerLoop(pParse, 0, tab1, p->pEList->nExpr,
                             p->pOrderBy, -1, eDest, iParm, 
                             iCont, iBreak);







>
>
>







557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
      if( rc ) return rc;

      /* Generate code to take the intersection of the two temporary
      ** tables.
      */
      assert( p->pEList );
      generateColumnNames(pParse, 0, p->pEList);
      if( p->pOrderBy ){
        sqliteVdbeAddOp(v, OP_SortOpen, 0, 0, 0, 0);
      }
      iBreak = sqliteVdbeMakeLabel(v);
      iCont = sqliteVdbeAddOp(v, OP_Next, tab1, iBreak, 0, 0);
      sqliteVdbeAddOp(v, OP_Key, tab1, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_NotFound, tab2, iCont, 0, 0);
      rc = selectInnerLoop(pParse, 0, tab1, p->pEList->nExpr,
                             p->pOrderBy, -1, eDest, iParm, 
                             iCont, iBreak);
Added test/select4.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
# Copyright (c) 1999, 2000 D. Richard Hipp
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public
# License as published by the Free Software Foundation; either
# version 2 of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
# 
# You should have received a copy of the GNU General Public
# License along with this library; if not, write to the
# Free Software Foundation, Inc., 59 Temple Place - Suite 330,
# Boston, MA  02111-1307, USA.
#
# Author contact information:
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.1 2000/06/08 00:28:52 drh Exp $

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

# Build some test data
#
do_test select4-1.0 {
  set fd [open data1.txt w]
  for {set i 1} {$i<32} {incr i} {
    for {set j 0} {pow(2,$j)<$i} {incr j} {}
    puts $fd "$i\t$j"
  }
  close $fd
  execsql {
    CREATE TABLE t1(n int, log int);
    COPY t1 FROM 'data1.txt'
  }
  file delete data1.txt
  execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
} {0 1 2 3 4 5}

# Union All operator
#
do_test select4-1.1a {
  lsort [execsql {SELECT DISTINCT log FROM t1}]
} {0 1 2 3 4 5}
do_test select4-1.1b {
  lsort [execsql {SELECT n FROM t1 WHERE log=3}]
} {5 6 7 8}
do_test select4-1.1c {
  execsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }
} {0 1 2 3 4 5 5 6 7 8}
do_test select4-1.2 {
  execsql {
    SELECT log FROM t1 WHERE n IN 
      (SELECT DISTINCT log FROM t1 UNION ALL
       SELECT n FROM t1 WHERE log=3)
    ORDER BY log;
  }
} {0 1 2 2 3 3 3 3}

# Union operator
#
do_test select4-2.1 {
  execsql {
    SELECT DISTINCT log FROM t1
    UNION
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }
} {0 1 2 3 4 5 6 7 8}
do_test select4-2.2 {
  execsql {
    SELECT log FROM t1 WHERE n IN 
      (SELECT DISTINCT log FROM t1 UNION
       SELECT n FROM t1 WHERE log=3)
    ORDER BY log;
  }
} {0 1 2 2 3 3 3 3}

# Except operator
#
do_test select4-3.1 {
  execsql {
    SELECT DISTINCT log FROM t1
    EXCEPT
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }
} {0 1 2 3 4}
do_test select4-3.2 {
  execsql {
    SELECT log FROM t1 WHERE n IN 
      (SELECT DISTINCT log FROM t1 EXCEPT
       SELECT n FROM t1 WHERE log=3)
    ORDER BY log;
  }
} {0 1 2 2}

# Intersect operator
#
do_test select4-4.1 {
  execsql {
    SELECT DISTINCT log FROM t1
    INTERSECT
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }
} {5}
do_test select4-4.2 {
  execsql {
    SELECT log FROM t1 WHERE n IN 
      (SELECT DISTINCT log FROM t1 INTERSECT
       SELECT n FROM t1 WHERE log=3)
    ORDER BY log;
  }
} {3}

finish_test