Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Refinements to NULL processing: NULLs are indistinct for DISTINCT and UNION. Multiplying a NULL by zero yields zero. In a CASE expression, a NULL comparison is considered false, not NULL. With these changes, NULLs in SQLite now work the same as in PostgreSQL and in Oracle. (CVS 600) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
da61aa1d238539dff9c43fd9f464d311 |
User & Date: | drh 2002-05-31 15:51:25.000 |
Context
2002-06-01
| ||
21:41 | Multiplying NULL by zero gives NULL, not zero. I misread the test data and coded it wrong. This check-in fixes the problem. (CVS 601) (check-in: df9cc852ad user: drh tags: trunk) | |
2002-05-31
| ||
15:51 | Refinements to NULL processing: NULLs are indistinct for DISTINCT and UNION. Multiplying a NULL by zero yields zero. In a CASE expression, a NULL comparison is considered false, not NULL. With these changes, NULLs in SQLite now work the same as in PostgreSQL and in Oracle. (CVS 600) (check-in: da61aa1d23 user: drh tags: trunk) | |
2002-05-30
| ||
12:27 | Once it is opened, leave the checkpoint journal file open for the duration of a transaction, rather than closing it and reopening it for each statement. (Ticket #53) (CVS 599) (check-in: 7a24336d50 user: drh tags: trunk) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** 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. ** | | | 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.66 2002/05/31 15:51:25 drh Exp $ */ #include "sqliteInt.h" /* ** Construct a new expression node and return a pointer to it. Memory ** for this node is obtained from sqliteMalloc(). The calling function |
︙ | ︙ | |||
929 930 931 932 933 934 935 | } case TK_AS: { sqliteExprCode(pParse, pExpr->pLeft); break; } case TK_CASE: { int expr_end_label; | < < < < < | > | < < < < < < < < > > > < < < < < | 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 | } case TK_AS: { sqliteExprCode(pParse, pExpr->pLeft); break; } case TK_CASE: { int expr_end_label; int jumpInst; int addr; int nExpr; int i; assert(pExpr->pList); assert((pExpr->pList->nExpr % 2) == 0); assert(pExpr->pList->nExpr > 0); nExpr = pExpr->pList->nExpr; expr_end_label = sqliteVdbeMakeLabel(v); if( pExpr->pLeft ){ sqliteExprCode(pParse, pExpr->pLeft); } for(i=0; i<nExpr; i=i+2){ sqliteExprCode(pParse, pExpr->pList->a[i].pExpr); if( pExpr->pLeft ){ sqliteVdbeAddOp(v, OP_Dup, 1, 1); jumpInst = sqliteVdbeAddOp(v, OP_Ne, 1, 0); sqliteVdbeAddOp(v, OP_Pop, 1, 0); }else{ jumpInst = sqliteVdbeAddOp(v, OP_IfNot, 1, 0); } sqliteExprCode(pParse, pExpr->pList->a[i+1].pExpr); sqliteVdbeAddOp(v, OP_Goto, 0, expr_end_label); addr = sqliteVdbeCurrentAddr(v); sqliteVdbeChangeP2(v, jumpInst, addr); } if( pExpr->pLeft ){ sqliteVdbeAddOp(v, OP_Pop, 1, 0); } if( pExpr->pRight ){ sqliteExprCode(pParse, pExpr->pRight); }else{ sqliteVdbeAddOp(v, OP_String, 0, 0); } sqliteVdbeResolveLabel(v, expr_end_label); } break; } } /* ** Generate code for a boolean expression such that a jump is made |
︙ | ︙ |
Changes to src/func.c.
︙ | ︙ | |||
12 13 14 15 16 17 18 | ** This file contains the C functions that implement various SQL ** functions of SQLite. ** ** There is only one exported symbol in this file - the function ** sqliteRegisterBuildinFunctions() found at the bottom of the file. ** All other code has file scope. ** | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** This file contains the C functions that implement various SQL ** functions of SQLite. ** ** There is only one exported symbol in this file - the function ** sqliteRegisterBuildinFunctions() found at the bottom of the file. ** All other code has file scope. ** ** $Id: func.c,v 1.19 2002/05/31 15:51:25 drh Exp $ */ #include <ctype.h> #include <math.h> #include <stdlib.h> #include <assert.h> #include "sqliteInt.h" |
︙ | ︙ | |||
358 359 360 361 362 363 364 | /* ** Routines to implement min() and max() aggregate functions. */ static void minStep(sqlite_func *context, int argc, const char **argv){ MinMaxCtx *p; p = sqlite_aggregate_context(context, sizeof(*p)); if( p==0 || argc<1 || argv[0]==0 ) return; | | | | 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 | /* ** Routines to implement min() and max() aggregate functions. */ static void minStep(sqlite_func *context, int argc, const char **argv){ MinMaxCtx *p; p = sqlite_aggregate_context(context, sizeof(*p)); if( p==0 || argc<1 || argv[0]==0 ) return; if( p->z==0 || sqliteCompare(argv[0],p->z)<0 ){ int len; if( p->z && p->z!=p->zBuf ){ sqliteFree(p->z); } len = strlen(argv[0]); if( len < sizeof(p->zBuf) ){ p->z = p->zBuf; }else{ p->z = sqliteMalloc( len+1 ); if( p->z==0 ) return; } strcpy(p->z, argv[0]); } } static void maxStep(sqlite_func *context, int argc, const char **argv){ MinMaxCtx *p; p = sqlite_aggregate_context(context, sizeof(*p)); if( p==0 || argc<1 || argv[0]==0 ) return; if( p->z==0 || sqliteCompare(argv[0],p->z)>0 ){ int len; if( p->z && p->z!=p->zBuf ){ sqliteFree(p->z); } len = strlen(argv[0]); if( len < sizeof(p->zBuf) ){ p->z = p->zBuf; |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** 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. ** | | | 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 C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** ** $Id: select.c,v 1.89 2002/05/31 15:51:25 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. */ |
︙ | ︙ | |||
322 323 324 325 326 327 328 | } /* If the DISTINCT keyword was present on the SELECT statement ** and this row has been seen before, then do not make this row ** part of the result. */ if( distinct>=0 && pEList && pEList->nExpr>0 ){ | > > > > | > | 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 | } /* If the DISTINCT keyword was present on the SELECT statement ** and this row has been seen before, then do not make this row ** part of the result. */ if( distinct>=0 && pEList && pEList->nExpr>0 ){ /* For the purposes of the DISTINCT keyword to a SELECT, NULLs ** are indistinct. This was confirmed by experiment in Oracle ** and PostgreSQL. It seems contradictory, but it appears to be ** true. ** sqliteVdbeAddOp(v, OP_IsNull, -pEList->nExpr,sqliteVdbeCurrentAddr(v)+7); */ sqliteVdbeAddOp(v, OP_MakeKey, pEList->nExpr, 1); sqliteVdbeAddOp(v, OP_Distinct, distinct, sqliteVdbeCurrentAddr(v)+3); sqliteVdbeAddOp(v, OP_Pop, pEList->nExpr+1, 0); sqliteVdbeAddOp(v, OP_Goto, 0, iContinue); sqliteVdbeAddOp(v, OP_String, 0, 0); sqliteVdbeAddOp(v, OP_PutStrKey, distinct, 0); } |
︙ | ︙ | |||
354 355 356 357 358 359 360 | sqliteVdbeAddOp(v, OP_SortPut, 0, 0); }else /* In this mode, write each query result to the key of the temporary ** table iParm. */ if( eDest==SRT_Union ){ | | | | 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 | sqliteVdbeAddOp(v, OP_SortPut, 0, 0); }else /* In this mode, write each query result to the key of the temporary ** table iParm. */ if( eDest==SRT_Union ){ sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0); sqliteVdbeAddOp(v, OP_String, 0, 0); sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0); }else /* Store the result as data using a unique key. */ if( eDest==SRT_Table || eDest==SRT_TempTable ){ sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0); sqliteVdbeAddOp(v, OP_NewRecno, iParm, 0); sqliteVdbeAddOp(v, OP_Pull, 1, 0); sqliteVdbeAddOp(v, OP_PutIntKey, iParm, 0); }else /* Construct a record from the query result, but instead of ** saving that record, use it as a key to delete elements from ** the temporary table iParm. */ if( eDest==SRT_Except ){ int addr = sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0); sqliteVdbeAddOp(v, OP_NotFound, iParm, addr+3); sqliteVdbeAddOp(v, OP_Delete, iParm, 0); }else /* If we are creating a set for an "expr IN (SELECT ...)" construct, ** then there should be a single item on the stack. Write this ** item into the set table with bogus data. |
︙ | ︙ |
Changes to src/util.c.
︙ | ︙ | |||
10 11 12 13 14 15 16 | ** ************************************************************************* ** Utility functions used throughout sqlite. ** ** This file contains functions for allocating memory, comparing ** strings, and stuff like that. ** | | | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ** ************************************************************************* ** Utility functions used throughout sqlite. ** ** This file contains functions for allocating memory, comparing ** strings, and stuff like that. ** ** $Id: util.c,v 1.45 2002/05/31 15:51:25 drh Exp $ */ #include "sqliteInt.h" #include <stdarg.h> #include <ctype.h> /* ** If malloc() ever fails, this global variable gets set to 1. |
︙ | ︙ | |||
724 725 726 727 728 729 730 731 732 733 734 735 736 737 | */ int sqliteSortCompare(const char *a, const char *b){ int len; int res = 0; int isNumA, isNumB; while( res==0 && *a && *b ){ isNumA = sqliteIsNumber(&a[1]); isNumB = sqliteIsNumber(&b[1]); if( isNumA ){ double rA, rB; if( !isNumB ){ res = -1; break; | > > > > > > > | 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 | */ int sqliteSortCompare(const char *a, const char *b){ int len; int res = 0; int isNumA, isNumB; while( res==0 && *a && *b ){ if( a[1]==0 ){ res = -1; break; }else if( b[1]==0 ){ res = +1; break; } isNumA = sqliteIsNumber(&a[1]); isNumB = sqliteIsNumber(&b[1]); if( isNumA ){ double rA, rB; if( !isNumB ){ res = -1; break; |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
26 27 28 29 30 31 32 | ** type to the other occurs as necessary. ** ** Most of the code in this file is taken up by the sqliteVdbeExec() ** function which does the work of interpreting a VDBE program. ** But other routines are also provided to help in building up ** a program instruction by instruction. ** | | | 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | ** type to the other occurs as necessary. ** ** Most of the code in this file is taken up by the sqliteVdbeExec() ** function which does the work of interpreting a VDBE program. ** But other routines are also provided to help in building up ** a program instruction by instruction. ** ** $Id: vdbe.c,v 1.151 2002/05/31 15:51:26 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** The following global variable is incremented every time a cursor ** moves, either by the OP_MoveTo or the OP_Next opcode. The test |
︙ | ︙ | |||
1765 1766 1767 1768 1769 1770 1771 1772 1773 | case OP_Multiply: case OP_Divide: case OP_Remainder: { int tos = p->tos; int nos = tos - 1; VERIFY( if( nos<0 ) goto not_enough_stack; ) if( ((aStack[tos].flags | aStack[nos].flags) & STK_Null)!=0 ){ POPSTACK; Release(p, nos); | > > > > > > > > > > > > > > > > > > > | | 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 | case OP_Multiply: case OP_Divide: case OP_Remainder: { int tos = p->tos; int nos = tos - 1; VERIFY( if( nos<0 ) goto not_enough_stack; ) if( ((aStack[tos].flags | aStack[nos].flags) & STK_Null)!=0 ){ int resultType = STK_Null; if( pOp->opcode==OP_Multiply ){ /* Special case: multiplying NULL by zero gives a zero result, not a ** NULL result as it would normally. */ if( (aStack[tos].flags & (STK_Int|STK_Real))!=0 || ((aStack[tos].flags & STK_Str)!=0 && isNumber(zStack[tos])) ){ Integerify(p,tos); if( aStack[tos].i==0 ){ resultType = STK_Int; aStack[nos].i = 0; } }else if( (aStack[nos].flags & (STK_Int|STK_Real))!=0 || ((aStack[nos].flags & STK_Str)!=0 && isNumber(zStack[nos])) ){ Integerify(p,nos); if( aStack[nos].i==0 ){ resultType = STK_Int; } } } POPSTACK; Release(p, nos); aStack[nos].flags = resultType; }else if( (aStack[tos].flags & aStack[nos].flags & STK_Int)==STK_Int ){ int a, b; a = aStack[tos].i; b = aStack[nos].i; switch( pOp->opcode ){ case OP_Add: b += a; break; case OP_Subtract: b -= a; break; |
︙ | ︙ | |||
2342 2343 2344 2345 2346 2347 2348 2349 2350 2351 2352 2353 2354 2355 | ** that go into building the record is NULL, then add some extra ** bytes to the record to make it distinct for other entries created ** during the same run of the VDBE. The extra bytes added are a ** counter that is reset with each run of the VDBE, so records ** created this way will not necessarily be distinct across runs. ** But they should be distinct for transient tables (created using ** OP_OpenTemp) which is what they are intended for. */ case OP_MakeRecord: { char *zNewRecord; int nByte; int nField; int i, j; int idxWidth; | > > > > | 2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 | ** that go into building the record is NULL, then add some extra ** bytes to the record to make it distinct for other entries created ** during the same run of the VDBE. The extra bytes added are a ** counter that is reset with each run of the VDBE, so records ** created this way will not necessarily be distinct across runs. ** But they should be distinct for transient tables (created using ** OP_OpenTemp) which is what they are intended for. ** ** (Later:) The P2==1 option was intended to make NULLs distinct ** for the UNION operator. But I have since discovered that NULLs ** are indistinct for UNION. So this option is never used. */ case OP_MakeRecord: { char *zNewRecord; int nByte; int nField; int i, j; int idxWidth; |
︙ | ︙ |
Changes to test/expr.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2001 September 15 # # 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 file is testing expressions. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2001 September 15 # # 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 file is testing expressions. # # $Id: expr.test,v 1.24 2002/05/31 15:51:26 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table to work with. # execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)} |
︙ | ︙ | |||
333 334 335 336 337 338 339 | test_expr expr-6.66 {t1='ac', t2=NULL} {t1 NOT GLOB t2} {{}} test_expr expr-case.1 {i1=1, i2=2} \ {CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} ne test_expr expr-case.2 {i1=2, i2=2} \ {CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} eq test_expr expr-case.3 {i1=NULL, i2=2} \ | | | | | | | 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 | test_expr expr-6.66 {t1='ac', t2=NULL} {t1 NOT GLOB t2} {{}} test_expr expr-case.1 {i1=1, i2=2} \ {CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} ne test_expr expr-case.2 {i1=2, i2=2} \ {CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} eq test_expr expr-case.3 {i1=NULL, i2=2} \ {CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} ne test_expr expr-case.4 {i1=2, i2=NULL} \ {CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} ne test_expr expr-case.5 {i1=2} \ {CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'error' END} two test_expr expr-case.6 {i1=1} \ {CASE i1 WHEN 1 THEN 'one' WHEN NULL THEN 'two' ELSE 'error' END} one test_expr expr-case.7 {i1=2} \ {CASE i1 WHEN 1 THEN 'one' WHEN NULL THEN 'two' ELSE 'error' END} error test_expr expr-case.8 {i1=3} \ {CASE i1 WHEN 1 THEN 'one' WHEN NULL THEN 'two' ELSE 'error' END} error test_expr expr-case.9 {i1=3} \ {CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'error' END} error test_expr expr-case.10 {i1=3} \ {CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' END} {{}} test_expr expr-case.11 {i1=null} \ {CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 3 END} 3 test_expr expr-case.12 {i1=1} \ {CASE i1 WHEN 1 THEN null WHEN 2 THEN 'two' ELSE 3 END} {{}} test_expr expr-case.13 {i1=7} \ { CASE WHEN i1 < 5 THEN 'low' WHEN i1 < 10 THEN 'medium' WHEN i1 < 15 THEN 'high' ELSE 'error' END} medium |
︙ | ︙ |
Changes to test/minmax.test.
︙ | ︙ | |||
9 10 11 12 13 14 15 | # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing SELECT statements that contain # aggregate min() and max() functions and which are handled as # as a special case. # | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing SELECT statements that contain # aggregate min() and max() functions and which are handled as # as a special case. # # $Id: minmax.test,v 1.4 2002/05/31 15:51:26 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test minmax-1.0 { execsql { BEGIN; |
︙ | ︙ | |||
124 125 126 127 128 129 130 | do_test minmax-4.1 { execsql { SELECT coalesce(min(x),-1), coalesce(max(x),-1) FROM (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y') } } {1 20} | > > > > > | > > > > > > > > > | 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 | do_test minmax-4.1 { execsql { SELECT coalesce(min(x),-1), coalesce(max(x),-1) FROM (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y') } } {1 20} do_test minmax-4.2 { execsql { SELECT y, sum(x) FROM (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1) GROUP BY y ORDER BY y; } } {1 1 2 5 3 22 4 92 5 90 6 0} do_test minmax-4.3 { execsql { SELECT y, count(x), count(*) FROM (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1) GROUP BY y ORDER BY y; } } {1 1 1 2 2 3 3 4 5 4 8 9 5 5 6 6 0 1} finish_test |
Added test/null.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 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 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 | # 2001 September 15 # # 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. # # This file implements tests for proper treatment of the special # value NULL. # set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table and some data to work with. # do_test null-1.0 { execsql { begin; create table t1(a,b,c); insert into t1 values(1,0,0); insert into t1 values(2,0,1); insert into t1 values(3,1,0); insert into t1 values(4,1,1); insert into t1 values(5,null,0); insert into t1 values(6,null,1); insert into t1 values(7,null,null); commit; select * from t1; } } {1 0 0 2 0 1 3 1 0 4 1 1 5 {} 0 6 {} 1 7 {} {}} # Check for how arithmetic expressions handle NULL # do_test null-1.1 { execsql { select ifnull(a+b,99) from t1; } } {1 2 4 5 99 99 99} do_test null-1.2 { execsql { select ifnull(b*c,99) from t1; } } {0 0 0 1 0 99 99} do_test null-1.2.1 { execsql { select ifnull(c*b,99) from t1; } } {0 0 0 1 0 99 99} # Check to see how the CASE expression handles NULL values. The # first WHEN for which the test expression is TRUE is selected. # FALSE and UNKNOWN test expressions are skipped. # do_test null-2.1 { execsql { select ifnull(case when b<>0 then 1 else 0 end, 99) from t1; } } {0 0 1 1 0 0 0} do_test null-2.2 { execsql { select ifnull(case when not b<>0 then 1 else 0 end, 99) from t1; } } {1 1 0 0 0 0 0} do_test null-2.3 { execsql { select ifnull(case when b<>0 and c<>0 then 1 else 0 end, 99) from t1; } } {0 0 0 1 0 0 0} do_test null-2.4 { execsql { select ifnull(case when not (b<>0 and c<>0) then 1 else 0 end, 99) from t1; } } {1 1 1 0 1 0 0} do_test null-2.5 { execsql { select ifnull(case when b<>0 or c<>0 then 1 else 0 end, 99) from t1; } } {0 1 1 1 0 1 0} do_test null-2.6 { execsql { select ifnull(case when not (b<>0 or c<>0) then 1 else 0 end, 99) from t1; } } {1 0 0 0 0 0 0} do_test null-2.7 { execsql { select ifnull(case b when c then 1 else 0 end, 99) from t1; } } {1 0 0 1 0 0 0} do_test null-2.8 { execsql { select ifnull(case c when b then 1 else 0 end, 99) from t1; } } {1 0 0 1 0 0 0} # Check to see that NULL values are ignored in aggregate functions. # do_test null-3.1 { execsql { select count(*), count(b), count(c), sum(b), sum(c), avg(b), avg(c), min(b), max(b) from t1; } } {7 4 6 2 3 0.5 0.5 0 1} # Check to see how WHERE clauses handle NULL values. A NULL value # is the same as UNKNOWN. The WHERE clause should only select those # rows that are TRUE. FALSE and UNKNOWN rows are rejected. # do_test null-4.1 { execsql { select a from t1 where b<10 } } {1 2 3 4} do_test null-4.2 { execsql { select a from t1 where not b>10 } } {1 2 3 4} do_test null-4.3 { execsql { select a from t1 where b<10 or c=1; } } {1 2 3 4 6} do_test null-4.4 { execsql { select a from t1 where b<10 and c=1; } } {2 4} do_test null-4.5 { execsql { select a from t1 where not (b<10 and c=1); } } {1 3 5} # The DISTINCT keyword on a SELECT statement should treat NULL values # as distinct # do_test null-5.1 { execsql { select distinct b from t1 order by b; } } {{} 0 1} # A UNION to two queries should treat NULL values # as distinct # do_test null-6.1 { execsql { select b from t1 union select c from t1 order by c; } } {{} 0 1} # The UNIQUE constraint only applies to non-null values # do_test null-7.1 { execsql { create table t2(a, b unique on conflict ignore); insert into t2 values(1,1); insert into t2 values(2,null); insert into t2 values(3,null); insert into t2 values(4,1); select a from t2; } } {1 2 3} do_test null-7.2 { execsql { create table t3(a, b, c, unique(b,c) on conflict ignore); insert into t3 values(1,1,1); insert into t3 values(2,null,1); insert into t3 values(3,null,1); insert into t3 values(4,1,1); select a from t3; } } {1 2 3} finish_test |
Changes to test/select4.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # 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 UNION, INTERSECT and EXCEPT operators # in SELECT statements. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # 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 UNION, INTERSECT and EXCEPT operators # in SELECT statements. # # $Id: select4.test,v 1.9 2002/05/31 15:51:26 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build some test data # set fd [open data1.txt w] |
︙ | ︙ | |||
245 246 247 248 249 250 251 | SELECT log, count(*) FROM t1 GROUP BY log UNION SELECT log, n FROM t1 WHERE n=7 ORDER BY count(*), log; } } {0 1 1 1 2 2 3 4 3 7 4 8 5 15} | > | | | | | | | 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 | SELECT log, count(*) FROM t1 GROUP BY log UNION SELECT log, n FROM t1 WHERE n=7 ORDER BY count(*), log; } } {0 1 1 1 2 2 3 4 3 7 4 8 5 15} # NULLs are indistinct for the UNION operator. # Make sure the UNION operator recognizes this # do_test select4-6.3 { execsql { SELECT NULL UNION SELECT NULL UNION SELECT 1 UNION SELECT 2 AS 'x' ORDER BY x; } } {{} 1 2} do_test select4-6.3.1 { execsql { SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT 1 UNION ALL SELECT 2 AS 'x' ORDER BY x; } } {{} {} 1 2} # Make sure the DISTINCT keyword treats NULLs as indistinct. # do_test select4-6.4 { execsql { SELECT * FROM ( SELECT NULL, 1 UNION ALL SELECT NULL, 1 ); } } {{} 1 {} 1} do_test select4-6.5 { execsql { SELECT DISTINCT * FROM ( SELECT NULL, 1 UNION ALL SELECT NULL, 1 ); } } {{} 1} do_test select4-6.6 { execsql { SELECT DISTINCT * FROM ( SELECT 1,2 UNION ALL SELECT 1,2 ); } } {1 2} # Test distinctness of NULL in other ways. # do_test select4-6.7 { execsql { SELECT NULL EXCEPT SELECT NULL } } {} # Make sure column names are correct when a compound select appears as # an expression in the WHERE clause. # do_test select4-7.1 { execsql { |
︙ | ︙ |