SQLite

Check-in [29ba812825]
Login

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

Overview
Comment:Ensure that aggregate functions are not used when evaluating a default value for a table column. Candidate fix for ticket [3a88d85f36704eebe134f7].
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 29ba812825bf06ef230f2480bba0579653f0a52d
User & Date: drh 2014-08-05 21:31:08.768
Context
2014-08-06
00:29
A simpler fix for ticket [3a88d85f36704eebe1] - one that uses less code. The error message is not quite as good, but as this error has apparently not previously occurred in over 8 years of heavy use, that is not seen as a serious problem. (check-in: 0ad1ed8ef0 user: drh tags: trunk)
2014-08-05
21:31
Ensure that aggregate functions are not used when evaluating a default value for a table column. Candidate fix for ticket [3a88d85f36704eebe134f7]. (check-in: 29ba812825 user: drh tags: trunk)
19:16
Add the ability to evaluate IN operators as a sequence of comparisons as an alternative to the long-standing algorithm of building a lookup table. Use the new implementation in circumstances where it is likely to be faster, such as when the RHS of the IN changes between successive evaluations. (check-in: 9528682168 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
2763
2764
2765
2766
2767
2768
2769





2770
2771
2772
2773
2774
2775
2776
      assert( !ExprHasProperty(pExpr, EP_IntValue) );
      zId = pExpr->u.zToken;
      nId = sqlite3Strlen30(zId);
      pDef = sqlite3FindFunction(db, zId, nId, nFarg, enc, 0);
      if( pDef==0 ){
        sqlite3ErrorMsg(pParse, "unknown function: %.*s()", nId, zId);
        break;





      }

      /* Attempt a direct implementation of the built-in COALESCE() and
      ** IFNULL() functions.  This avoids unnecessary evalation of
      ** arguments past the first non-NULL argument.
      */
      if( pDef->funcFlags & SQLITE_FUNC_COALESCE ){







>
>
>
>
>







2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
2775
2776
2777
2778
2779
2780
2781
      assert( !ExprHasProperty(pExpr, EP_IntValue) );
      zId = pExpr->u.zToken;
      nId = sqlite3Strlen30(zId);
      pDef = sqlite3FindFunction(db, zId, nId, nFarg, enc, 0);
      if( pDef==0 ){
        sqlite3ErrorMsg(pParse, "unknown function: %.*s()", nId, zId);
        break;
      }
      if( pDef->xFunc==0 ){
        sqlite3ErrorMsg(pParse, "misuse of aggregate function: %.*s()",
                        nId, zId);
        break;
      }

      /* Attempt a direct implementation of the built-in COALESCE() and
      ** IFNULL() functions.  This avoids unnecessary evalation of
      ** arguments past the first non-NULL argument.
      */
      if( pDef->funcFlags & SQLITE_FUNC_COALESCE ){
Changes to test/table.test.
721
722
723
724
725
726
727














































728
729
do_test table-15.2 {
  execsql {BEGIN}
  for {set i 0} {$i<2000} {incr i} {
    execsql "DROP TABLE tbl$i"
  }
  execsql {COMMIT}
} {}















































finish_test







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


721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
do_test table-15.2 {
  execsql {BEGIN}
  for {set i 0} {$i<2000} {incr i} {
    execsql "DROP TABLE tbl$i"
  }
  execsql {COMMIT}
} {}

# Ticket 3a88d85f36704eebe134f7f48aebf00cd6438c1a (2014-08-05)
# The following SQL script segfaults while running the INSERT statement:
#
#    CREATE TABLE t1(x DEFAULT(max(1)));
#    INSERT INTO t1(rowid) VALUES(1);
#
# The problem appears to be the use of an aggregate function as part of
# the default value for a column. This problem has been in the code since
# at least 2006-01-01 and probably before that. This problem was detected
# and reported on the sqlite-users@sqlite.org mailing list by Zsbán Ambrus. 
#
do_execsql_test table-16.1 {
  CREATE TABLE t16(x DEFAULT(max(1)));
  INSERT INTO t16(x) VALUES(123);
  SELECT rowid, x FROM t16;
} {1 123}
do_catchsql_test table-16.2 {
  INSERT INTO t16(rowid) VALUES(4);
} {1 {misuse of aggregate function: max()}}
do_execsql_test table-16.3 {
  DROP TABLE t16;
  CREATE TABLE t16(x DEFAULT(abs(1)));
  INSERT INTO t16(rowid) VALUES(4);
  SELECT rowid, x FROM t16;
} {4 1}
do_catchsql_test table-16.4 {
  DROP TABLE t16;
  CREATE TABLE t16(x DEFAULT(avg(1)));
  INSERT INTO t16(rowid) VALUES(123);
  SELECT rowid, x FROM t16;
} {1 {misuse of aggregate function: avg()}}
do_catchsql_test table-16.5 {
  DROP TABLE t16;
  CREATE TABLE t16(x DEFAULT(count()));
  INSERT INTO t16(rowid) VALUES(123);
  SELECT rowid, x FROM t16;
} {1 {misuse of aggregate function: count()}}
do_catchsql_test table-16.6 {
  DROP TABLE t16;
  CREATE TABLE t16(x DEFAULT(group_concat('x',',')));
  INSERT INTO t16(rowid) VALUES(123);
  SELECT rowid, x FROM t16;
} {1 {misuse of aggregate function: group_concat()}}



finish_test