SQLite

Check-in [fc9e04609b]
Login

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

Overview
Comment:When the left-hand side of an IN operator is constant and the right-hand side is a SELECT, recognize that the IN operator is not constant. Ticket #1380. (CVS 2624)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: fc9e04609b6968fc5039a6f9f808aac681f4fc41
User & Date: drh 2005-08-25 12:45:04.000
Context
2005-08-27
01:50
Widen the opcode column of explain output in the shell. (CVS 2625) (check-in: dd3b00aa0b user: drh tags: trunk)
2005-08-25
12:45
When the left-hand side of an IN operator is constant and the right-hand side is a SELECT, recognize that the IN operator is not constant. Ticket #1380. (CVS 2624) (check-in: fc9e04609b user: drh tags: trunk)
2005-08-24
18:04
Fix the --enable-threadsafe option to the configure script. Ticket #1378. (CVS 2623) (check-in: 76ec0b3d3a user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.220 2005/08/24 16:54:05 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.221 2005/08/25 12:45:04 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
620
621
622
623
624
625
626


627
628
629
630
631
632
633
** The return value from xFunc determines whether the tree walk continues.
** 0 means continue walking the tree.  1 means do not walk children
** of the current node but continue with siblings.  2 means abandon
** the tree walk completely.
**
** The return value from this routine is 1 to abandon the tree walk
** and 0 to continue.


*/
static int walkExprList(ExprList *, int (*)(void *, Expr*), void *);
static int walkExprTree(Expr *pExpr, int (*xFunc)(void*,Expr*), void *pArg){
  int rc;
  if( pExpr==0 ) return 0;
  rc = (*xFunc)(pArg, pExpr);
  if( rc==0 ){







>
>







620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
** The return value from xFunc determines whether the tree walk continues.
** 0 means continue walking the tree.  1 means do not walk children
** of the current node but continue with siblings.  2 means abandon
** the tree walk completely.
**
** The return value from this routine is 1 to abandon the tree walk
** and 0 to continue.
**
** NOTICE:  This routine does *not* descend into subqueries.
*/
static int walkExprList(ExprList *, int (*)(void *, Expr*), void *);
static int walkExprTree(Expr *pExpr, int (*xFunc)(void*,Expr*), void *pArg){
  int rc;
  if( pExpr==0 ) return 0;
  rc = (*xFunc)(pArg, pExpr);
  if( rc==0 ){
692
693
694
695
696
697
698





699
700
701
702
703
704
705
    case TK_AGG_FUNCTION:
#ifndef SQLITE_OMIT_SUBQUERY
    case TK_SELECT:
    case TK_EXISTS:
#endif
      *((int*)pArg) = 0;
      return 2;





    default:
      return 0;
  }
}

/*
** Walk an expression tree.  Return 1 if the expression is constant







>
>
>
>
>







694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
    case TK_AGG_FUNCTION:
#ifndef SQLITE_OMIT_SUBQUERY
    case TK_SELECT:
    case TK_EXISTS:
#endif
      *((int*)pArg) = 0;
      return 2;
    case TK_IN:
      if( pExpr->pSelect ){
        *((int*)pArg) = 0;
        return 2;
      }
    default:
      return 0;
  }
}

/*
** Walk an expression tree.  Return 1 if the expression is constant
Changes to test/subquery.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2005 January 19
#
# 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.  The
# focus of this script is testing correlated subqueries
#
# $Id: subquery.test,v 1.11 2005/07/21 03:48:20 drh Exp $
#

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

ifcapable !subquery {
  finish_test













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2005 January 19
#
# 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.  The
# focus of this script is testing correlated subqueries
#
# $Id: subquery.test,v 1.12 2005/08/25 12:45:04 drh Exp $
#

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

ifcapable !subquery {
  finish_test
381
382
383
384
385
386
387























388
389
390
391
392
do_test subquery-5.2 {
  # This is the key test.  The subquery should have only run once.  If
  # The double-quoted identifier "two" were causing the subquery to be
  # processed as a correlated subquery, then it would have run 4 times.
  set callcnt
} {1}




























finish_test







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





381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
do_test subquery-5.2 {
  # This is the key test.  The subquery should have only run once.  If
  # The double-quoted identifier "two" were causing the subquery to be
  # processed as a correlated subquery, then it would have run 4 times.
  set callcnt
} {1}


# Ticket #1380.  Make sure correlated subqueries on an IN clause work
# correctly when the left-hand side of the IN operator is constant.
#
do_test subquery-6.1 {
  set callcnt 0
  execsql {
    SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=y)
  }
} {one two three four}
do_test subquery-6.2 {
  set callcnt
} {4}
do_test subquery-6.3 {
  set callcnt 0
  execsql {
    SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=1)
  }
} {one two three four}
do_test subquery-6.4 {
  set callcnt
} {1}





finish_test