/ Check-in [fc9e0460]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:fc9e04609b6968fc5039a6f9f808aac681f4fc41
User & Date: drh 2005-08-25 12:45:04
Context
2005-08-27
01:50
Widen the opcode column of explain output in the shell. (CVS 2625) check-in: dd3b00aa 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: fc9e0460 user: drh tags: trunk
2005-08-24
18:04
Fix the --enable-threadsafe option to the configure script. Ticket #1378. (CVS 2623) check-in: 76ec0b3d user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
620
621
622
623
624
625
626


627
628
629
630
631
632
633
...
692
693
694
695
696
697
698





699
700
701
702
703
704
705
**    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.
**
................................................................................
** 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 ){
................................................................................
    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







|







 







>
>







 







>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
...
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
**    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.
**
................................................................................
** 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 ){
................................................................................
    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.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
381
382
383
384
385
386
387























388
389
390
391
392
#    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
................................................................................
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







|







 







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





7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
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
#    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
................................................................................
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