/ Check-in [9843c0dd]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Respect collation sequences in views. Ticket #1088. (CVS 2291)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9843c0dd795ceda3c260986f95b101a0cbc7cf64
User & Date: danielk1977 2005-01-30 11:11:44
Context
2005-01-30
22:10
User-contributed chagnes to tclinstaller so that it supports DESTDIR. (CVS 2292) check-in: ab8dbcf5 user: drh tags: trunk
11:11
Respect collation sequences in views. Ticket #1088. (CVS 2291) check-in: 9843c0dd user: danielk1977 tags: trunk
09:17
Have the optimization introduced in (2170) deal with OP_NullRow as well as OP_Column and OP_Recno. Fix for #1086. (CVS 2290) check-in: 356d31e0 user: danielk1977 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
...
852
853
854
855
856
857
858



859
860
861
862
863
864
865
...
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
**    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.235 2005/01/29 08:32:45 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  Table *pTab;
  int i, j;
  ExprList *pEList;
  Column *aCol, *pCol;

  if( prepSelectStmt(pParse, pSelect) ){
    return 0;



  }
  pTab = sqliteMalloc( sizeof(Table) );
  if( pTab==0 ){
    return 0;
  }
  pTab->zName = zTabName ? sqliteStrDup(zTabName) : 0;
  pEList = pSelect->pEList;
................................................................................
#ifndef SQLITE_OMIT_SUBQUERY
      /* A sub-query in the FROM clause of a SELECT */
      assert( pFrom->pSelect!=0 );
      if( pFrom->zAlias==0 ){
        pFrom->zAlias =
          sqlite3MPrintf("sqlite_subquery_%p_", (void*)pFrom->pSelect);
      }
      sqlite3SelectResolve(pParse, pFrom->pSelect, 0);
      pFrom->pTab = pTab = 
        sqlite3ResultSetOfSelect(pParse, pFrom->zAlias, pFrom->pSelect);
      if( pTab==0 ){
        return 1;
      }
      /* The isTransient flag indicates that the Table structure has been
      ** dynamically allocated and may be freed at any time.  In other words,







|







 







>
>
>







 







<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
...
988
989
990
991
992
993
994

995
996
997
998
999
1000
1001
**    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.236 2005/01/30 11:11:44 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  Table *pTab;
  int i, j;
  ExprList *pEList;
  Column *aCol, *pCol;

  if( prepSelectStmt(pParse, pSelect) ){
    return 0;
  }
  if( sqlite3SelectResolve(pParse, pSelect, 0) ){
    return 0;
  }
  pTab = sqliteMalloc( sizeof(Table) );
  if( pTab==0 ){
    return 0;
  }
  pTab->zName = zTabName ? sqliteStrDup(zTabName) : 0;
  pEList = pSelect->pEList;
................................................................................
#ifndef SQLITE_OMIT_SUBQUERY
      /* A sub-query in the FROM clause of a SELECT */
      assert( pFrom->pSelect!=0 );
      if( pFrom->zAlias==0 ){
        pFrom->zAlias =
          sqlite3MPrintf("sqlite_subquery_%p_", (void*)pFrom->pSelect);
      }

      pFrom->pTab = pTab = 
        sqlite3ResultSetOfSelect(pParse, pFrom->zAlias, pFrom->pSelect);
      if( pTab==0 ){
        return 1;
      }
      /* The isTransient flag indicates that the Table structure has been
      ** dynamically allocated and may be freed at any time.  In other words,

Changes to test/subquery.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
75
76
77
78
79
80
81
82
83
84
85
86












87
88
89
90
91
92
93
#    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.  The
# focus of this script is testing correlated subqueries
#
# $Id: subquery.test,v 1.3 2005/01/29 08:32:47 danielk1977 Exp $
#

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

ifcapable !subquery {
  finish_test
................................................................................
do_test subquery-1.7 {
  execsql {
    SELECT a, x FROM t2, t1 WHERE t1.a = (SELECT x);
  }
} {1 1 3 3 5 5 7 7}

# Try an aggregate in both the subquery and the parent query.
do_test subquery-1.6 {
  execsql {
    SELECT count(*) FROM t1 WHERE a > (SELECT count(*) FROM t2);
  }
} {2}














#------------------------------------------------------------------
# The following test cases - subquery-2.* - are not logically
# organized. They're here largely because they were failing during
# one stage of development of sub-queries.
#







|







 







|




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







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
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
#    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.  The
# focus of this script is testing correlated subqueries
#
# $Id: subquery.test,v 1.4 2005/01/30 11:11:44 danielk1977 Exp $
#

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

ifcapable !subquery {
  finish_test
................................................................................
do_test subquery-1.7 {
  execsql {
    SELECT a, x FROM t2, t1 WHERE t1.a = (SELECT x);
  }
} {1 1 3 3 5 5 7 7}

# Try an aggregate in both the subquery and the parent query.
do_test subquery-1.8 {
  execsql {
    SELECT count(*) FROM t1 WHERE a > (SELECT count(*) FROM t2);
  }
} {2}

# Test a correlated subquery disables the "only open the index" optimization.
do_test subquery-1.9.1 {
  execsql {
    SELECT (y*2)>b FROM t1, t2 WHERE a=x;
  }
} {0 1 1 1}
do_test subquery-1.9.2 {
  execsql {
    SELECT a FROM t1 WHERE (SELECT (y*2)>b FROM t2 WHERE a=x); 
  }
} {3 5 7}


#------------------------------------------------------------------
# The following test cases - subquery-2.* - are not logically
# organized. They're here largely because they were failing during
# one stage of development of sub-queries.
#

Changes to test/view.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
414
415
416
417
418
419
420
421





















422
#    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.  The
# focus of this file is testing VIEW statements.
#
# $Id: view.test,v 1.23 2005/01/29 08:32:47 danielk1977 Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Omit this entire file if the library is not configured with views enabled.
ifcapable !view {
  finish_test
  return
................................................................................
  }
} {1}
do_test view-10.2 {
  execsql {
    SELECT * FROM v_t3_b;
  }
} {2}






















finish_test







|







 








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

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
#    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.  The
# focus of this file is testing VIEW statements.
#
# $Id: view.test,v 1.24 2005/01/30 11:11:44 danielk1977 Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Omit this entire file if the library is not configured with views enabled.
ifcapable !view {
  finish_test
  return
................................................................................
  }
} {1}
do_test view-10.2 {
  execsql {
    SELECT * FROM v_t3_b;
  }
} {2}

do_test view-11.1 {
  execsql {
    CREATE TABLE t4(a COLLATE NOCASE);
    INSERT INTO t4 VALUES('This');
    INSERT INTO t4 VALUES('this');
    INSERT INTO t4 VALUES('THIS');
    SELECT * FROM t4 WHERE a = 'THIS';
  }
} {This this THIS}
do_test view-11.2 {
  execsql {
    SELECT * FROM (SELECT * FROM t4) WHERE a = 'THIS';
  }
} {This this THIS}
do_test view-11.3 {
  execsql {
    CREATE VIEW v11 AS SELECT * FROM t4;
    SELECT * FROM v11 WHERE a = 'THIS';
  }
} {This this THIS}

finish_test