/ Check-in [b886eaa3]
Login

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

Overview
Comment:Make sure the names of all expressions in compound SELECT statements used as subqueries are correctly resolved. Ticket #2018. (CVS 3477)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:b886eaa334150262ce4d1a1d0470ca4cf623a396
User & Date: drh 2006-10-13 15:34:17
Context
2006-10-18
23:26
Fix a problems that arise if malloc() fails while compiling SELECT statements within a TRIGGER. (CVS 3478) check-in: ee4894b4 user: drh tags: trunk
2006-10-13
15:34
Make sure the names of all expressions in compound SELECT statements used as subqueries are correctly resolved. Ticket #2018. (CVS 3477) check-in: b886eaa3 user: drh tags: trunk
12:25
Avoid expanding %d contained in the action of a lemon parser rule. Ticket #1063. This is a fix for lemon only. It does not effect SQLite. (CVS 3476) check-in: 81daedcf user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2601
2602
2603
2604
2605
2606
2607






2608

2609
2610
2611
2612
2613
2614
2615
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.321 2006/09/29 14:01:05 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
        sqlite3ErrorMsg(pParse, "aggregate functions are not allowed in "
            "the GROUP BY clause");
        return SQLITE_ERROR;
      }
    }
  }







  return SQLITE_OK;

}

/*
** Reset the aggregate accumulator.
**
** The aggregate accumulator is a set of memory cells that hold
** intermediate results while calculating an aggregate.  This







|







 







>
>
>
>
>
>
|
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.322 2006/10/13 15:34:17 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
        sqlite3ErrorMsg(pParse, "aggregate functions are not allowed in "
            "the GROUP BY clause");
        return SQLITE_ERROR;
      }
    }
  }

  /* If this is one SELECT of a compound, be sure to resolve names
  ** in the other SELECTs.
  */
  if( p->pPrior ){
    return sqlite3SelectResolve(pParse, p->pPrior, pOuterNC);
  }else{
    return SQLITE_OK;
  }
}

/*
** Reset the aggregate accumulator.
**
** The aggregate accumulator is a set of memory cells that hold
** intermediate results while calculating an aggregate.  This

Changes to test/select7.test.

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
..
67
68
69
70
71
72
73
74
75



































#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing compute SELECT statements and nested
# views.
#
# $Id: select7.test,v 1.7 2005/03/29 03:11:00 danielk1977 Exp $


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

ifcapable compound {

................................................................................
  # } {1 {GROUP BY may only be used on aggregate queries}}
  do_test select7-3.1 {
    catchsql {
      SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name
    }
  } [list 0 [execsql {SELECT * FROM sqlite_master ORDER BY name}]]
}
finish_test











































|







 







<

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
..
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
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing compute SELECT statements and nested
# views.
#
# $Id: select7.test,v 1.8 2006/10/13 15:34:17 drh Exp $


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

ifcapable compound {

................................................................................
  # } {1 {GROUP BY may only be used on aggregate queries}}
  do_test select7-3.1 {
    catchsql {
      SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name
    }
  } [list 0 [execsql {SELECT * FROM sqlite_master ORDER BY name}]]
}


# Ticket #2018 - Make sure names are resolved correctly on all
# SELECT statements of a compound subquery.
#
ifcapable {subquery && compound} {
  do_test select7-4.1 {
    execsql {
      CREATE TABLE IF NOT EXISTS photo(pk integer primary key, x);
      CREATE TABLE IF NOT EXISTS tag(pk integer primary key, fk int, name);
    
      SELECT P.pk from PHOTO P WHERE NOT EXISTS ( 
           SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk 
           EXCEPT 
           SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%'
      );
    }
  } {}
  do_test select7-4.2 {
    execsql {
      INSERT INTO photo VALUES(1,1);
      INSERT INTO photo VALUES(2,2);
      INSERT INTO photo VALUES(3,3);
      INSERT INTO tag VALUES(11,1,'one');
      INSERT INTO tag VALUES(12,1,'two');
      INSERT INTO tag VALUES(21,1,'one-b');
      SELECT P.pk from PHOTO P WHERE NOT EXISTS ( 
           SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk 
           EXCEPT 
           SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%'
      );
    }
  } {2 3}

}

finish_test