/ Check-in [88a4e400]
Login
☰Home Timeline Branches

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

Overview
Comment:When comparing two expressions do not apply affinity to either operand. Ticket #805. (CVS 1810)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 88a4e400f916d7af088b39a9653d6452b3666577
User & Date: drh 2004-07-19 00:39:45
Context
2004-07-19
00:56
A failed ATTACH leaves the database in a consistent state. Ticket #811. (CVS 1811) (check-in: c22f9feb user: drh tags: trunk)
00:39
When comparing two expressions do not apply affinity to either operand. Ticket #805. (CVS 1810) (check-in: 88a4e400 user: drh tags: trunk)
2004-07-18
23:47
Unsuccessful attempt to reproduce ticket #807. (CVS 1809) (check-in: d7e9772c 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
..
89
90
91
92
93
94
95
96
97
98

99
100
101
102
103
104
105
106

**    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.150 2004/06/28 01:11:47 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

char const *sqlite3AffinityString(char affinity){
  switch( affinity ){
    case SQLITE_AFF_INTEGER: return "i";
................................................................................
      return SQLITE_AFF_INTEGER;
    }else if( aff1==SQLITE_AFF_NUMERIC || aff2==SQLITE_AFF_NUMERIC ){
      return SQLITE_AFF_NUMERIC;
    }else{
      return SQLITE_AFF_NONE;
    }
  }else if( !aff1 && !aff2 ){
    /* Neither side of the comparison is a column. Use numeric affinity
    ** for the comparison.
    */

    return SQLITE_AFF_NUMERIC;
  }else{
    /* One side is a column, the other is not. Use the columns affinity. */
    return (aff1 + aff2);
  }
}

/*








|







 







|
|

>
|








8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

**    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.151 2004/07/19 00:39:45 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

char const *sqlite3AffinityString(char affinity){
  switch( affinity ){
    case SQLITE_AFF_INTEGER: return "i";
................................................................................
      return SQLITE_AFF_INTEGER;
    }else if( aff1==SQLITE_AFF_NUMERIC || aff2==SQLITE_AFF_NUMERIC ){
      return SQLITE_AFF_NUMERIC;
    }else{
      return SQLITE_AFF_NONE;
    }
  }else if( !aff1 && !aff2 ){
    /* Neither side of the comparison is a column.  Compare the
    ** results directly.
    */
    /* return SQLITE_AFF_NUMERIC;  // Ticket #805 */
    return SQLITE_AFF_NONE;
  }else{
    /* One side is a column, the other is not. Use the columns affinity. */
    return (aff1 + aff2);
  }
}

/*

Changes to test/misc1.test. 

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.28 2004/06/19 00:16:31 drh Exp $

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

# Mimic the SQLite 2 collation type NUMERIC.
db collate numeric numeric_collate
proc numeric_collate {lhs rhs} {
................................................................................

# Make sure string comparisons really do compare strings in format4+.
# Similar tests in the format3.test file show that for format3 and earlier
# all comparisions where numeric if either operand looked like a number.
#
do_test misc1-12.1 {
  execsql {SELECT '0'=='0.0'}
} {1}
do_test misc1-12.2 {
  execsql {SELECT '0'==0.0}
} {1}
do_test misc1-12.3 {
  execsql {SELECT '12345678901234567890'=='12345678901234567891'}
} {0}
do_test misc1-12.4 {
  execsql {
    CREATE TABLE t6(a INT UNIQUE, b TEXT UNIQUE);
    INSERT INTO t6 VALUES('0','0.0');








|







 







|


|








9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.29 2004/07/19 00:39:46 drh Exp $

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

# Mimic the SQLite 2 collation type NUMERIC.
db collate numeric numeric_collate
proc numeric_collate {lhs rhs} {
................................................................................

# Make sure string comparisons really do compare strings in format4+.
# Similar tests in the format3.test file show that for format3 and earlier
# all comparisions where numeric if either operand looked like a number.
#
do_test misc1-12.1 {
  execsql {SELECT '0'=='0.0'}
} {0}
do_test misc1-12.2 {
  execsql {SELECT '0'==0.0}
} {0}
do_test misc1-12.3 {
  execsql {SELECT '12345678901234567890'=='12345678901234567891'}
} {0}
do_test misc1-12.4 {
  execsql {
    CREATE TABLE t6(a INT UNIQUE, b TEXT UNIQUE);
    INSERT INTO t6 VALUES('0','0.0');

Changes to test/misc4.test. 

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
37
38
39
40
41
42
43

44
45

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc4.test,v 1.1 2004/07/18 23:47:53 drh Exp $

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

# Prepare a statement that will create a temporary table.  Then do
# a rollback.  Then try to execute the prepared statement.
#
................................................................................
    ROLLBACK;
  }
  sqlite3_step $stmt
  execsql {
    SELECT * FROM temp.t2;
  }
} {1}


finish_test








|







 







>



9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
37
38
39
40
41
42
43
44
45
46

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc4.test,v 1.2 2004/07/19 00:39:46 drh Exp $

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

# Prepare a statement that will create a temporary table.  Then do
# a rollback.  Then try to execute the prepared statement.
#
................................................................................
    ROLLBACK;
  }
  sqlite3_step $stmt
  execsql {
    SELECT * FROM temp.t2;
  }
} {1}
catch {sqlite3_finalize $stmt}

finish_test

Changes to test/quick.test. 

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
..
23
24
25
26
27
28
29

30
31
32
33
34
35
36

#    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 runs all tests.
#
# $Id: quick.test,v 1.26 2004/06/29 13:41:21 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
rename finish_test really_finish_test
proc finish_test {} {}
set ISQUICK 1

................................................................................
  quick.test
  btree2.test
  malloc.test
  memleak.test
  misuse.test
  format3.test
  crash.test

}

if {[sqlite3 -has-codec]} {
  lappend EXCLUDE \
    attach.test \
    attach2.test \
    auth.test \








|







 







>








6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
..
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

#    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 runs all tests.
#
# $Id: quick.test,v 1.27 2004/07/19 00:39:46 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
rename finish_test really_finish_test
proc finish_test {} {}
set ISQUICK 1

................................................................................
  quick.test
  btree2.test
  malloc.test
  memleak.test
  misuse.test
  format3.test
  crash.test
  utf16.test
}

if {[sqlite3 -has-codec]} {
  lappend EXCLUDE \
    attach.test \
    attach2.test \
    auth.test \

Changes to test/types2.test. 

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
56
57
58
59
60
61
62



63
64
65
66
67
68
69
70
71
72
73
...
153
154
155
156
157
158
159

160
161
162
163
164
165
166
167
168
169
170

#    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 the interaction of manifest types, type affinity
# and comparison expressions.
#
# $Id: types2.test,v 1.3 2004/05/20 12:41:20 drh Exp $

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

# Tests in this file are organized roughly as follows:
#
# types2-1.*: The '=' operator in the absence of an index.
................................................................................
  do_test $t.1 "execsql {SELECT $e FROM t1}" $r
  do_test $t.2 "execsql {SELECT 1 FROM t1 WHERE $expr}" [expr $r?"1":""]
  do_test $t.3 "execsql {SELECT 1 FROM t1 WHERE NOT ($e)}" [expr $r?"":"1"]
}

# Compare literals against literals. This should always use a numeric
# comparison.



test_bool types2-1.1 "" {500 = 500.0} 1
test_bool types2-1.2 "" {'500' = 500.0} 1
test_bool types2-1.3 "" {500 = '500.0'} 1
test_bool types2-1.4 "" {'500' = '500.0'} 1

# Compare literals against a column with TEXT affinity
test_bool types2-1.5 {t1=500} {500 = t1} 1
test_bool types2-1.6 {t1=500} {'500' = t1} 1
test_bool types2-1.7 {t1=500} {500.0 = t1} 0
test_bool types2-1.8 {t1=500} {'500.0' = t1} 0
test_bool types2-1.9 {t1='500'} {500 = t1} 1
................................................................................

test_boolset types2-3.1 {o < 20} {1 2}
test_boolset types2-3.2 {o < 20.0} {1 2}
test_boolset types2-3.3 {o < '20'} {1 2 3 4 5 6 9 10}
test_boolset types2-3.3 {o < '20.0'} {1 2 3 4 5 6 7 9 10}

# Compare literals against literals (always a numeric comparison).

test_bool types2-4.1 "" {500 > 60.0} 1
test_bool types2-4.2 "" {'500' > 60.0} 1
test_bool types2-4.3 "" {500 > '60.0'} 1
test_bool types2-4.4 "" {'500' > '60.0'} 1

# Compare literals against a column with TEXT affinity
test_bool types2-4.5 {t1=500.0} {t1 > 500} 1
test_bool types2-4.6 {t1=500.0} {t1 > '500' } 1
test_bool types2-4.7 {t1=500.0} {t1 > 500.0 } 0
test_bool types2-4.8 {t1=500.0} {t1 > '500.0' } 0
test_bool types2-4.9 {t1='500.0'} {t1 > 500 } 1








|







 







>
>
>

|
|
|







 







>


|
|








8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
...
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174

#    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 the interaction of manifest types, type affinity
# and comparison expressions.
#
# $Id: types2.test,v 1.4 2004/07/19 00:39:46 drh Exp $

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

# Tests in this file are organized roughly as follows:
#
# types2-1.*: The '=' operator in the absence of an index.
................................................................................
  do_test $t.1 "execsql {SELECT $e FROM t1}" $r
  do_test $t.2 "execsql {SELECT 1 FROM t1 WHERE $expr}" [expr $r?"1":""]
  do_test $t.3 "execsql {SELECT 1 FROM t1 WHERE NOT ($e)}" [expr $r?"":"1"]
}

# Compare literals against literals. This should always use a numeric
# comparison.
#
# Changed by ticket #805:  Use no affinity for literal comparisons.
#
test_bool types2-1.1 "" {500 = 500.0} 1
test_bool types2-1.2 "" {'500' = 500.0} 0
test_bool types2-1.3 "" {500 = '500.0'} 0
test_bool types2-1.4 "" {'500' = '500.0'} 0

# Compare literals against a column with TEXT affinity
test_bool types2-1.5 {t1=500} {500 = t1} 1
test_bool types2-1.6 {t1=500} {'500' = t1} 1
test_bool types2-1.7 {t1=500} {500.0 = t1} 0
test_bool types2-1.8 {t1=500} {'500.0' = t1} 0
test_bool types2-1.9 {t1='500'} {500 = t1} 1
................................................................................

test_boolset types2-3.1 {o < 20} {1 2}
test_boolset types2-3.2 {o < 20.0} {1 2}
test_boolset types2-3.3 {o < '20'} {1 2 3 4 5 6 9 10}
test_boolset types2-3.3 {o < '20.0'} {1 2 3 4 5 6 7 9 10}

# Compare literals against literals (always a numeric comparison).
# Change (by ticket #805):  No affinity in comparisons
test_bool types2-4.1 "" {500 > 60.0} 1
test_bool types2-4.2 "" {'500' > 60.0} 1
test_bool types2-4.3 "" {500 > '60.0'} 0
test_bool types2-4.4 "" {'500' > '60.0'} 0

# Compare literals against a column with TEXT affinity
test_bool types2-4.5 {t1=500.0} {t1 > 500} 1
test_bool types2-4.6 {t1=500.0} {t1 > '500' } 1
test_bool types2-4.7 {t1=500.0} {t1 > 500.0 } 0
test_bool types2-4.8 {t1=500.0} {t1 > '500.0' } 0
test_bool types2-4.9 {t1='500.0'} {t1 > 500 } 1

Changes to www/datatype3.tcl. 

1
2
3
4
5
6
7
8
...
191
192
193
194
195
196
197
198
199

200
201
202
203
204
205
206
207

set rcsid {$Id: datatype3.tcl,v 1.6 2004/06/17 19:04:17 drh Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

................................................................................
	the expression before the comparison takes place.</P>

	<LI><P>When two column values are compared, if one column has
	INTEGER or NUMERIC affinity and the other does not, the NUMERIC
	affinity is applied to any values with storage class TEXT extracted
	from the non-NUMERIC column.</P>

	<LI><P>When the results of two expressions are compared, the NUMERIC
	affinity is applied to both values before the comparison takes

	place.</P>
</UL>

<P>
In SQLite, the expression "a BETWEEN b AND c" is equivalent to "a &gt;= b
AND a &lt;= c", even if this means that different affinities are applied to
'a' in each of the comparisons required to evaluate the expression.
</P>

|







 







|
|
>
|








1
2
3
4
5
6
7
8
...
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208

set rcsid {$Id: datatype3.tcl,v 1.7 2004/07/19 00:39:46 drh Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

................................................................................
	the expression before the comparison takes place.</P>

	<LI><P>When two column values are compared, if one column has
	INTEGER or NUMERIC affinity and the other does not, the NUMERIC
	affinity is applied to any values with storage class TEXT extracted
	from the non-NUMERIC column.</P>

	<LI><P>When the results of two expressions are compared, the no
        conversions occur.  The results are compared as is.  If a string
        is compared to a number, the number will always be less than the
        string.</P>
</UL>

<P>
In SQLite, the expression "a BETWEEN b AND c" is equivalent to "a &gt;= b
AND a &lt;= c", even if this means that different affinities are applied to
'a' in each of the comparisons required to evaluate the expression.
</P>

This page was generated in about 0.03s by Fossil 2.12 [405457c161] 2020-07-03 20:54:08