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)
FossilOrigin-Name: da61aa1d238539dff9c43fd9f464d311e28d669f
diff --git a/src/expr.c b/src/expr.c
index 25f0ebd..c414e39 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -12,7 +12,7 @@
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
-** $Id: expr.c,v 1.65 2002/05/30 02:35:12 drh Exp $
+** $Id: expr.c,v 1.66 2002/05/31 15:51:25 drh Exp $
*/
#include "sqliteInt.h"
@@ -933,9 +933,7 @@
}
case TK_CASE: {
int expr_end_label;
- int null_result_label;
int jumpInst;
- int nullBypassInst;
int addr;
int nExpr;
int i;
@@ -945,44 +943,32 @@
assert(pExpr->pList->nExpr > 0);
nExpr = pExpr->pList->nExpr;
expr_end_label = sqliteVdbeMakeLabel(v);
- null_result_label = sqliteVdbeMakeLabel(v);
if( pExpr->pLeft ){
sqliteExprCode(pParse, pExpr->pLeft);
- nullBypassInst = sqliteVdbeAddOp(v, OP_IsNull, -1, 0);
}
for(i=0; i<nExpr; i=i+2){
sqliteExprCode(pParse, pExpr->pList->a[i].pExpr);
- sqliteVdbeAddOp(v, OP_IsNull, -1, null_result_label);
if( pExpr->pLeft ){
sqliteVdbeAddOp(v, OP_Dup, 1, 1);
- jumpInst = sqliteVdbeAddOp(v, OP_Ne, 0, 0);
+ jumpInst = sqliteVdbeAddOp(v, OP_Ne, 1, 0);
+ sqliteVdbeAddOp(v, OP_Pop, 1, 0);
}else{
- jumpInst = sqliteVdbeAddOp(v, OP_IfNot, 0, 0);
+ jumpInst = sqliteVdbeAddOp(v, OP_IfNot, 1, 0);
}
sqliteExprCode(pParse, pExpr->pList->a[i+1].pExpr);
sqliteVdbeAddOp(v, OP_Goto, 0, expr_end_label);
- if( i>=nExpr-2 ){
- sqliteVdbeResolveLabel(v, null_result_label);
- sqliteVdbeAddOp(v, OP_Pop, 1, 0);
- if( pExpr->pRight!=0 ){
- sqliteVdbeAddOp(v, OP_String, 0, 0);
- 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);
- if( pExpr->pLeft ){
- sqliteVdbeAddOp(v, OP_Pull, 1, 0);
- sqliteVdbeAddOp(v, OP_Pop, 1, 0);
- sqliteVdbeChangeP2(v, nullBypassInst, sqliteVdbeCurrentAddr(v));
- }
}
break;
}
diff --git a/src/func.c b/src/func.c
index 8ba7db9..4295ea3 100644
--- a/src/func.c
+++ b/src/func.c
@@ -16,7 +16,7 @@
** sqliteRegisterBuildinFunctions() found at the bottom of the file.
** All other code has file scope.
**
-** $Id: func.c,v 1.18 2002/05/29 23:22:23 drh Exp $
+** $Id: func.c,v 1.19 2002/05/31 15:51:25 drh Exp $
*/
#include <ctype.h>
#include <math.h>
@@ -362,7 +362,7 @@
MinMaxCtx *p;
p = sqlite_aggregate_context(context, sizeof(*p));
if( p==0 || argc<1 || argv[0]==0 ) return;
- if( sqlite_aggregate_count(context)==1 || sqliteCompare(argv[0],p->z)<0 ){
+ if( p->z==0 || sqliteCompare(argv[0],p->z)<0 ){
int len;
if( p->z && p->z!=p->zBuf ){
sqliteFree(p->z);
@@ -381,7 +381,7 @@
MinMaxCtx *p;
p = sqlite_aggregate_context(context, sizeof(*p));
if( p==0 || argc<1 || argv[0]==0 ) return;
- if( sqlite_aggregate_count(context)==1 || sqliteCompare(argv[0],p->z)>0 ){
+ if( p->z==0 || sqliteCompare(argv[0],p->z)>0 ){
int len;
if( p->z && p->z!=p->zBuf ){
sqliteFree(p->z);
diff --git a/src/select.c b/src/select.c
index 4acee15..5a97be8 100644
--- a/src/select.c
+++ b/src/select.c
@@ -12,7 +12,7 @@
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
-** $Id: select.c,v 1.88 2002/05/27 12:24:48 drh Exp $
+** $Id: select.c,v 1.89 2002/05/31 15:51:25 drh Exp $
*/
#include "sqliteInt.h"
@@ -326,7 +326,12 @@
** part of the result.
*/
if( distinct>=0 && pEList && pEList->nExpr>0 ){
- sqliteVdbeAddOp(v, OP_IsNull, -pEList->nExpr, sqliteVdbeCurrentAddr(v)+7);
+ /* 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);
@@ -358,7 +363,7 @@
** table iParm.
*/
if( eDest==SRT_Union ){
- sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 1);
+ sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
sqliteVdbeAddOp(v, OP_String, 0, 0);
sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
}else
@@ -377,7 +382,7 @@
** the temporary table iParm.
*/
if( eDest==SRT_Except ){
- int addr = sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 1);
+ int addr = sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
sqliteVdbeAddOp(v, OP_NotFound, iParm, addr+3);
sqliteVdbeAddOp(v, OP_Delete, iParm, 0);
}else
diff --git a/src/util.c b/src/util.c
index 536f158..d14e6d3 100644
--- a/src/util.c
+++ b/src/util.c
@@ -14,7 +14,7 @@
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
-** $Id: util.c,v 1.44 2002/05/26 21:34:58 drh Exp $
+** $Id: util.c,v 1.45 2002/05/31 15:51:25 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
@@ -728,6 +728,13 @@
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 ){
diff --git a/src/vdbe.c b/src/vdbe.c
index 19d7ed7..3cf5452 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -30,7 +30,7 @@
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
-** $Id: vdbe.c,v 1.150 2002/05/27 01:04:51 drh Exp $
+** $Id: vdbe.c,v 1.151 2002/05/31 15:51:26 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
@@ -1769,9 +1769,28 @@
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 = STK_Null;
+ aStack[nos].flags = resultType;
}else if( (aStack[tos].flags & aStack[nos].flags & STK_Int)==STK_Int ){
int a, b;
a = aStack[tos].i;
@@ -2346,6 +2365,10 @@
** 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;