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
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
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
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
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
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
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
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
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
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
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>Datatypes In SQLite</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
Datatypes In SQLite
</div>
<div class="fancy_toc">
<a onclick="toggle_toc()">
<span class="fancy_toc_mark" id="toc_mk">►</span>
Table Of Contents
</a>
<div id="toc_sub"><div class="fancy-toc1"><a href="#datatypes_in_sqlite">1. Datatypes In SQLite</a></div>
<div class="fancy-toc1"><a href="#storage_classes_and_datatypes">2. Storage Classes and Datatypes</a></div>
<div class="fancy-toc2"><a href="#boolean_datatype">2.1. Boolean Datatype</a></div>
<div class="fancy-toc2"><a href="#date_and_time_datatype">2.2. Date and Time Datatype</a></div>
<div class="fancy-toc1"><a href="#type_affinity">3. Type Affinity</a></div>
<div class="fancy-toc2"><a href="#determination_of_column_affinity">3.1. Determination Of Column Affinity</a></div>
<div class="fancy-toc3"><a href="#affinity_name_examples">3.1.1. Affinity Name Examples</a></div>
<div class="fancy-toc2"><a href="#affinity_of_expressions">3.2. Affinity Of Expressions</a></div>
<div class="fancy-toc2"><a href="#column_affinity_for_views_and_subqueries">3.3. Column Affinity For Views And Subqueries</a></div>
<div class="fancy-toc3"><a href="#column_affinity_for_compound_views">3.3.1. Column Affinity For Compound Views</a></div>
<div class="fancy-toc2"><a href="#column_affinity_behavior_example">3.4. Column Affinity Behavior Example</a></div>
<div class="fancy-toc1"><a href="#comparison_expressions">4. Comparison Expressions</a></div>
<div class="fancy-toc2"><a href="#sort_order">4.1. Sort Order</a></div>
<div class="fancy-toc2"><a href="#type_conversions_prior_to_comparison">4.2. Type Conversions Prior To Comparison</a></div>
<div class="fancy-toc2"><a href="#comparison_example">4.3. Comparison Example</a></div>
<div class="fancy-toc1"><a href="#operators">5. Operators</a></div>
<div class="fancy-toc1"><a href="#sorting_grouping_and_compound_selects">6. Sorting, Grouping and Compound SELECTs</a></div>
<div class="fancy-toc1"><a href="#collating_sequences">7. Collating Sequences</a></div>
<div class="fancy-toc2"><a href="#assigning_collating_sequences_from_sql">7.1. Assigning Collating Sequences from SQL</a></div>
<div class="fancy-toc2"><a href="#collation_sequence_examples">7.2. Collation Sequence Examples</a></div>
</div>
</div>
<script>
function toggle_toc(){
var sub = document.getElementById("toc_sub")
var mk = document.getElementById("toc_mk")
if( sub.style.display!="block" ){
sub.style.display = "block";
mk.innerHTML = "▼";
} else {
sub.style.display = "none";
mk.innerHTML = "►";
}
}
</script>
</div>
<h1 id="datatypes_in_sqlite"><span>1. </span>Datatypes In SQLite</h1>
<p>Most SQL database engines (every SQL database engine other than SQLite,
as far as we know) uses static, rigid typing. With static typing, the datatype
of a value is determined by its container - the particular column in
which the value is stored.</p>
<p>SQLite uses a more general dynamic type system. In SQLite, the datatype
of a value is associated with the value itself, not with its container.
The dynamic type system of SQLite is backwards
compatible with the more common static type systems of other database engines
in the sense that SQL statements that work on statically typed databases
work the same way in SQLite. However, the dynamic typing in SQLite allows
it to do things which are not possible in traditional rigidly typed
databases. <a href="flextypegood.html">Flexible typing is a feature</a> of SQLite, not a bug.</p>
<p>Update:
As of version 3.37.0 (2021-11-27), SQLite provides <a href="stricttables.html">STRICT tables</a>
that do rigid type enforcement, for developers who prefer that kind of thing.
<a name="storageclasses"></a>
</p><h1 id="storage_classes_and_datatypes"><span>2. </span>Storage Classes and Datatypes</h1>
<p>Each value stored in an SQLite database (or manipulated by the
database engine) has one of the following storage classes:</p>
<ul>
<li><p><b>NULL</b>.
The value is a NULL value.</p>
</li><li><p><b>INTEGER</b>. The value is a signed integer, stored in 0, 1,
2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.</p>
</li><li><p><b>REAL</b>. The value is a floating point value, stored as
an 8-byte IEEE floating point number.</p>
</li><li><p><b>TEXT</b>. The value is a text string, stored using the
database encoding (UTF-8, UTF-16BE or UTF-16LE).</p>
</li><li><p><b>BLOB</b>. The value is a blob of data, stored exactly as
it was input.</p>
</li></ul>
<p>A storage class is more general than a datatype.
The INTEGER storage class, for example, includes 7 different integer
datatypes of different lengths.
<a href="fileformat2.html#record_format">This makes a difference on disk.</a>
But as soon as INTEGER values are read off of disk and into memory for
processing, they are converted to the most general datatype
(8-byte signed integer).
And so for the most part, "storage class" is indistinguishable from
"datatype" and the two terms can be used interchangeably.</p>
<p>Any column in an SQLite version 3 database,
except an <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column, may be used to store a value
of any storage class.</p>
<p>All values in SQL statements, whether they are literals embedded in SQL
statement text or <a href="lang_expr.html#varparam">parameters</a> bound to
<a href="c3ref/stmt.html">precompiled SQL statements</a>
have an implicit storage class.
Under circumstances described below, the
database engine may convert values between numeric storage classes
(INTEGER and REAL) and TEXT during query execution.
</p>
<a name="boolean"></a>
<h2 id="boolean_datatype"><span>2.1. </span>Boolean Datatype</h2>
<p>SQLite does not have a separate Boolean storage class.
Instead, Boolean values are stored as integers 0 (false) and 1 (true).</p>
<p>SQLite recognizes the keywords "TRUE" and "FALSE",
as of version 3.23.0 (2018-04-02) but those keywords are
really just alternative spellings for the integer literals 1 and 0
respectively.
<a name="datetime"></a>
</p><h2 id="date_and_time_datatype"><span>2.2. </span>Date and Time Datatype</h2>
<p>SQLite does not have a storage class set aside for storing
dates and/or times.
Instead, the built-in <a href="lang_datefunc.html">Date And Time Functions</a> of SQLite are capable of
storing dates and times as TEXT, REAL, or INTEGER values:</p>
<ul>
<li><b>TEXT</b> as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
</li><li><b>REAL</b> as Julian day numbers, the number of days since
noon in Greenwich on November 24, 4714 B.C. according to the
proleptic Gregorian calendar.
</li><li><b>INTEGER</b> as Unix Time, the number of seconds since
1970-01-01 00:00:00 UTC.
</li></ul>
<p>Applications can choose to store dates and times in any of these
formats and freely convert between formats using the built-in
<a href="lang_datefunc.html">date and time functions</a>.</p>
<a name="affinity"></a>
<h1 id="type_affinity"><span>3. </span>Type Affinity</h1>
<p>
SQL database engines that use rigid typing will usually try to
automatically convert values to the appropriate datatype. Consider this:
</p><blockquote><pre>
CREATE TABLE t1(a INT, b VARCHAR(10));
INSERT INTO t1(a,b) VALUES('123',456);
</pre></blockquote>
<p>
Rigidly-typed database will convert the string '123' into an
integer 123 and the integer 456 into a string '456' prior to
doing the insert.
</p><p>
In order to maximize compatibility between SQLite and other database
engines, and so that the example above will work on SQLite as it does
on other SQL database engines,
SQLite supports the concept of "type affinity" on columns.
The type affinity of a column is the recommended type for data stored
in that column. The important idea here is that the type is recommended, not
required. Any column can still store any type of data.
It is just that some columns, given the choice, will prefer to use
one storage class over another. The preferred storage class for
a column is called its "affinity".
</p>
<p>Each column in an SQLite 3 database is assigned one of the
following type affinities:</p>
<ul>
<li>TEXT</li>
<li>NUMERIC</li>
<li>INTEGER</li>
<li>REAL</li>
<li>BLOB</li>
</ul>
<p>(Historical note: The "BLOB" type affinity used to be called "NONE".
But that term was easy to confuse with "no affinity" and so it was
renamed.)
</p><p>A column with TEXT affinity stores all data using storage classes
NULL, TEXT or BLOB. If numerical data is inserted into a column with
TEXT affinity it is converted into text form before being stored.</p>
<p>A column with NUMERIC affinity may contain values using all five
storage classes. When text data is inserted into a NUMERIC column, the
storage class of the text is converted to INTEGER or REAL (in order of
preference) if the text is a well-formed integer or real literal, respectively.
If the TEXT value is a well-formed integer literal that is too large
to fit in a 64-bit signed integer, it is converted to REAL.
For conversions between TEXT and REAL storage classes, only the first
15 significant decimal digits of the number are preserved.
If the TEXT value is not a well-formed integer or real literal,
then the value is stored as TEXT.
For the purposes of this paragraph, hexadecimal integer
literals are not considered well-formed and are stored as TEXT.
(This is done for historical compatibility with versions of SQLite
prior to <a href="releaselog/3_8_6.html">version 3.8.6</a> 2014-08-15 where hexadecimal integer
literals were first introduced into SQLite.)
If a floating point value that can be represented exactly as an integer
is inserted into a column with NUMERIC affinity, the value is
converted into an integer.
No attempt is made to convert NULL or BLOB values.</p>
<p>A string might look like a floating-point literal with
a decimal point and/or exponent notation but as long as
the value can be expressed as an integer, the NUMERIC affinity will convert
it into an integer. Hence, the string '3.0e+5' is stored in a
column with NUMERIC affinity as the integer 300000, not as the floating
point value 300000.0.</p>
<p>A column that uses INTEGER affinity behaves the same as a column
with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity
is only evident in a <a href="lang_expr.html#castexpr">CAST expression</a>: The expression
"CAST(4.0 AS INT)" returns an integer 4, whereas
"CAST(4.0 AS NUMERIC)" leaves the value as a floating-point 4.0.</p>
<p>A column with REAL affinity behaves like a column with NUMERIC
affinity except that it forces integer values into floating point
representation. (As an internal optimization, small floating point
values with no fractional component and stored in columns with REAL
affinity are written to disk as integers in order to take up less
space and are automatically converted back into floating point as
the value is read out.
This optimization is completely invisible at the SQL level and can only
be detected by examining the raw bits of the database file.)</p>
<p>A column with affinity BLOB does not prefer one storage class over
another and no attempt is made to coerce data from one storage class into
another.</p>
<a name="affname"></a>
<h2 id="determination_of_column_affinity"><span>3.1. </span>Determination Of Column Affinity</h2>
<p>For tables not declared as <a href="stricttables.html">STRICT</a>,
the affinity of a column is determined by the declared type
of the column, according to the following rules in the order shown:</p>
<ol>
<li><p>If the declared type contains the string "INT" then it
is assigned INTEGER affinity.</p>
</li><li><p>If the declared type of the column contains any of the strings
"CHAR", "CLOB", or "TEXT" then that
column has TEXT affinity. Notice that the type VARCHAR contains the
string "CHAR" and is thus assigned TEXT affinity.</p>
</li><li><p>If the declared type for a column
contains the string "BLOB" or if
no type is specified then the column has affinity BLOB.</p>
</li><li><p>If the declared type for a column
contains any of the strings "REAL", "FLOA",
or "DOUB" then the column has REAL affinity.</p>
</li><li><p>Otherwise, the affinity is NUMERIC.</p>
</li></ol>
<p>Note that the order of the rules for determining column affinity
is important. A column whose declared type is "CHARINT" will match
both rules 1 and 2 but the first rule takes precedence and so the
column affinity will be INTEGER.</p>
<h3 id="affinity_name_examples"><span>3.1.1. </span>Affinity Name Examples</h3>
<p>The following table shows how many common datatype names from
more traditional SQL implementations are converted into affinities by the five rules of the
previous section. This table shows only a small subset of the
datatype names that SQLite will accept. Note that numeric arguments
in parentheses that following the type name (ex: "VARCHAR(255)") are
ignored by SQLite - SQLite does not impose any length restrictions
(other than the large global <a href="limits.html#max_length">SQLITE_MAX_LENGTH</a> limit) on the length of
strings, BLOBs or numeric values.</p>
<blockquote>
<table border="1" cellpadding="5">
<tr><th>Example Typenames From The<br>CREATE TABLE Statement<br>
or CAST Expression
</th><th>Resulting Affinity
</th><th>Rule Used To Determine Affinity
</th></tr><tr><td align="center" valign="top">
INT<br>
INTEGER<br>
TINYINT<br>
SMALLINT<br>
MEDIUMINT<br>
BIGINT<br>
UNSIGNED BIG INT<br>
INT2<br>
INT8
</td><td align="center">INTEGER
</td><td align="center">1
</td></tr><tr><td align="center" valign="top">
CHARACTER(20)<br>
VARCHAR(255)<br>
VARYING CHARACTER(255)<br>
NCHAR(55)<br>
NATIVE CHARACTER(70)<br>
NVARCHAR(100)<br>
TEXT<br>
CLOB
</td><td align="center">TEXT
</td><td align="center">2
</td></tr><tr><td align="center" valign="top">
BLOB<br>
<i>no datatype specified</i>
</td><td align="center">BLOB
</td><td align="center">3
</td></tr><tr><td align="center" valign="top">
REAL<br>
DOUBLE<br>
DOUBLE PRECISION<br>
FLOAT
</td><td align="center">REAL
</td><td align="center">4
</td></tr><tr><td align="center" valign="top">
NUMERIC<br>
DECIMAL(10,5)<br>
BOOLEAN<br>
DATE<br>
DATETIME
</td><td align="center">NUMERIC
</td><td align="center">5
</td></tr></table>
</blockquote>
<p>Note that a declared type of "FLOATING POINT" would give INTEGER
affinity, not REAL affinity, due to the "INT" at the end of "POINT".
And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.
<a name="expraff"></a>
</p><h2 id="affinity_of_expressions"><span>3.2. </span>Affinity Of Expressions</h2>
<p>Every table column has a type affinity (one of BLOB, TEXT, INTEGER,
REAL, or NUMERIC) but expressions do not necessarily have an affinity.
</p><p>Expression affinity is determined by the following rules:
</p><ul>
<li><p>The right-hand operand of an IN or NOT IN
operator has no affinity if the operand is a list, or has the same
affinity as the affinity of the result set expression if the operand
is a SELECT.
</p></li><li><p>When an expression is a simple reference to a column of a
real table (not a <a href="lang_createview.html">VIEW</a> or subquery) then the expression
has the same affinity as the table column.
</p><ul>
<li><p>Parentheses around the column name are ignored. Hence if
X and Y.Z are column names, then (X) and (Y.Z) are also considered
column names and have the affinity of the corresponding columns.
</p></li><li><p>
Any operators applied to column names, including the no-op
unary "+" operator, convert the column name into an expression which
always has no affinity. Hence even if X and Y.Z are column names, the
expressions +X and +Y.Z are not column names and have no affinity.
</p></li></ul>
</li><li><p>An expression of the form "CAST(<i>expr</i> AS <i>type</i>)"
has an affinity that is the same as a column with a declared
type of "<i>type</i>".
</p></li><li><p>A COLLATE operator has the same affinity as its left-hand side operand.
</p></li><li><p>Otherwise, an expression has no affinity.
</p></li></ul>
<a name="affview"></a>
<h2 id="column_affinity_for_views_and_subqueries"><span>3.3. </span>Column Affinity For Views And Subqueries</h2>
<p>The "columns" of a <a href="lang_createview.html">VIEW</a> or FROM-clause subquery are really
the expressions
in the result set of the <a href="lang_select.html">SELECT</a> statement that implements the VIEW
or subquery. Thus, the affinity for columns of a VIEW or subquery
are determined by the expression affinity rules above.
Consider an example:
</p><blockquote><pre>
CREATE TABLE t1(a INT, b TEXT, c REAL);
CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;
</pre></blockquote>
<p>The affinity of the v1.x column will be the same as the affinity
of t1.b (TEXT), since v1.x maps directly into t1.b. But
columns v1.y and v1.z both have no affinity, since those columns
map into expression a+c and 42, and expressions always have no
affinity.
<a name="affcompoundview"></a>
</p><h3 id="column_affinity_for_compound_views"><span>3.3.1. </span>Column Affinity For Compound Views</h3>
<p>When the <a href="lang_select.html">SELECT</a> statement that implements a <a href="lang_createview.html">VIEW</a> or
FROM-clause subquery is a <a href="lang_select.html#compound">compound SELECT</a> then the affinity of
each column of the VIEW or subquery will
be the affinity of the corresponding result column for
one of the individual SELECT statements that make up
the compound.
However, it is indeterminate which of the SELECT statements will
be used to determine affinity.
Different constituent SELECT statements might be used to determine
affinity at different times during query evaluation. The choice
might vary across different versions of SQLite. The choice might
change between one query and the next in the same version of SQLite.
The choice might be different at different times within the same
query. Hence, you can never be sure what affinity will be used
for columns of a compound SELECT that have different affinities in
the constituent subqueries.
</p><p>Best practice is to avoid mixing affinities in a compound SELECT
if you care about the datatype of the result. Mixing affinities in
a compound SELECT can lead to surprising and unintuitive results.
See, for example, <a href="https://sqlite.org/forum/forumpost/02d7be94d7">forum post 02d7be94d7</a>.
</p><h2 id="column_affinity_behavior_example"><span>3.4. </span>Column Affinity Behavior Example</h2>
<p>The following SQL demonstrates how SQLite uses column affinity
to do type conversions when values are inserted into a table.</p>
<blockquote>
<pre>
CREATE TABLE t1(
t TEXT, -- text affinity by rule 2
nu NUMERIC, -- numeric affinity by rule 5
i INTEGER, -- integer affinity by rule 1
r REAL, -- real affinity by rule 4
no BLOB -- no affinity by rule 3
);
-- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|text
-- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL.
DELETE FROM t1;
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|real
-- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER.
DELETE FROM t1;
INSERT INTO t1 VALUES(500, 500, 500, 500, 500);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|integer
-- BLOBs are always stored as BLOBs regardless of column affinity.
DELETE FROM t1;
INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
blob|blob|blob|blob|blob
-- NULLs are also unaffected by affinity
DELETE FROM t1;
INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
null|null|null|null|null
</pre>
</blockquote>
<a name="comparisons"></a>
<h1 id="comparison_expressions"><span>4. </span>Comparison Expressions</h1>
<p>SQLite version 3 has the usual set of SQL comparison operators
including "=", "==", "<", "<=", ">", ">=", "!=", "",
"IN", "NOT IN", "BETWEEN", "IS", and "IS NOT", .
<a name="sortorder"></a>
</p><h2 id="sort_order"><span>4.1. </span>Sort Order</h2>
<p>The results of a comparison depend on the storage classes of the
operands, according to the following rules:</p>
<ul>
<li><p>A value with storage class NULL is considered less than any
other value (including another value with storage class NULL).</p>
</li><li><p>An INTEGER or REAL value is less than any TEXT or BLOB value.
When an INTEGER or REAL is compared to another INTEGER or REAL, a
numerical comparison is performed.</p>
</li><li><p>A TEXT value is less than a BLOB value. When two TEXT values
are compared an appropriate collating sequence is used to determine
the result. </p>
</li><li><p>When two BLOB values are compared, the result is
determined using memcmp().</p>
</li></ul>
<a name="compaff"></a>
<h2 id="type_conversions_prior_to_comparison"><span>4.2. </span>Type Conversions Prior To Comparison</h2>
<p>SQLite may attempt to convert values between the storage classes
INTEGER, REAL, and/or TEXT before performing a comparison.
Whether or not any conversions are attempted before the comparison takes
place depends on the type affinity of the operands.
</p><p>To "apply affinity" means to convert an operand to a particular storage
class if and only if the conversion does not lose essential information.
Numeric values can always be converted into TEXT. TEXT values
can be converted into numeric values if the text content is a well-formed
integer or real literal, but not a hexadecimal integer literal.
BLOB values are converted into TEXT values by simply interpreting
the binary BLOB content as a text string in the current database
encoding.
</p><p>Affinity is applied to operands of a comparison operator prior to
the comparison according to the following rules in the order shown:</p>
<ul>
<li><p>If one operand has INTEGER, REAL or NUMERIC affinity
and the other operand has TEXT or BLOB or no affinity
then NUMERIC affinity is applied to other operand.
</p></li><li><p>If one operand has TEXT affinity and the other has no affinity,
then TEXT affinity is applied to the other operand.
</p></li><li><p>Otherwise, no affinity is applied and both operands are compared
as is.</p>
</li></ul>
<p>The expression "a BETWEEN b AND c" is treated as two separate
binary comparisons "a >= b AND a <= c", even if that means
different affinities are applied to 'a' in each of the comparisons.
Datatype conversions in comparisons of the
form "x IN (SELECT y ...)" are handled as if
the comparison were really "x=y".
The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR
a = +y OR a = +z OR ...".
In other words, the values to the right of the IN operator (the "x", "y",
and "z" values in this example) are considered to have no affinity,
even if they happen to be column values or CAST expressions.
</p>
<h2 id="comparison_example"><span>4.3. </span>Comparison Example</h2>
<blockquote>
<pre>
CREATE TABLE t1(
a TEXT, -- text affinity
b NUMERIC, -- numeric affinity
c BLOB, -- no affinity
d -- no affinity
);
-- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectively
INSERT INTO t1 VALUES('500', '500', '500', 500);
SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;
text|integer|text|integer
-- Because column "a" has text affinity, numeric values on the
-- right-hand side of the comparisons are converted to text before
-- the comparison occurs.
SELECT a < 40, a < 60, a < 600 FROM t1;
0|1|1
-- Text affinity is applied to the right-hand operands but since
-- they are already TEXT this is a no-op; no conversions occur.
SELECT a < '40', a < '60', a < '600' FROM t1;
0|1|1
-- Column "b" has numeric affinity and so numeric affinity is applied
-- to the operands on the right. Since the operands are already numeric,
-- the application of affinity is a no-op; no conversions occur. All
-- values are compared numerically.
SELECT b < 40, b < 60, b < 600 FROM t1;
0|0|1
-- Numeric affinity is applied to operands on the right, converting them
-- from text to integers. Then a numeric comparison occurs.
SELECT b < '40', b < '60', b < '600' FROM t1;
0|0|1
-- No affinity conversions occur. Right-hand side values all have
-- storage class INTEGER which are always less than the TEXT values
-- on the left.
SELECT c < 40, c < 60, c < 600 FROM t1;
0|0|0
-- No affinity conversions occur. Values are compared as TEXT.
SELECT c < '40', c < '60', c < '600' FROM t1;
0|1|1
-- No affinity conversions occur. Right-hand side values all have
-- storage class INTEGER which compare numerically with the INTEGER
-- values on the left.
SELECT d < 40, d < 60, d < 600 FROM t1;
0|0|1
-- No affinity conversions occur. INTEGER values on the left are
-- always less than TEXT values on the right.
SELECT d < '40', d < '60', d < '600' FROM t1;
1|1|1
</pre>
</blockquote>
<p>All of the results in the example are the same if the comparisons are
commuted - if expressions of the form "a<40" are rewritten
as "40>a".
</p><h1 id="operators"><span>5. </span>Operators</h1>
<p>Mathematical operators (+, -, *, /, %, <<, >>,
&, and |) interpret both operands as if they were numbers.
STRING or BLOB operands automatically convert into REAL or INTEGER values.
If the STRING or BLOB looks like a real number (if it has a decimal point
or an exponent) or if the value is outside the range that can be represented
as a 64-bit signed integer, then it converts to REAL. Otherwise the operand
converts to INTEGER.
The implied type conversion of mathematical operands is slightly different
from <a href="lang_expr.html#castexpr">CAST to NUMERIC</a> in that string and BLOB values that
look like real numbers but have no fractional part are kept as REAL
instead of being converted into INTEGER as they would be for <a href="lang_expr.html#castexpr">CAST to NUMERIC</a>.
The conversion from STRING or BLOB into REAL or INTEGER is performed
even if it is lossy and irreversible.
Some mathematical operators (%, <<, >>, &, and |) expect
INTEGER operands. For those operators, REAL operands are converted into INTEGER
in the same way as a <a href="lang_expr.html#castexpr">CAST to INTEGER</a>.
The <<, >>, &, and | operators always return an INTEGER (or NULL)
result, but the % operator returns either INTEGER or REAL (or NULL)
depending on the type of its operands.
A NULL operand on a mathematical operator yields a NULL result.
An operand on a mathematical operator that does not look in any way
numeric and is not NULL is converted to 0 or 0.0.
Division by zero gives a result of NULL.
</p>
<h1 id="sorting_grouping_and_compound_selects"><span>6. </span>Sorting, Grouping and Compound SELECTs</h1>
<p>When query results are sorted by an ORDER BY clause, values with storage
class NULL come first, followed by INTEGER and REAL values
interspersed in numeric order, followed by TEXT values in collating
sequence order, and finally BLOB values in memcmp() order. No storage
class conversions occur before the sort.</p>
<p>When grouping values with the GROUP BY clause values with
different storage classes are considered distinct, except for INTEGER
and REAL values which are considered equal if they are numerically
equal. No affinities are applied to any values as the result of a
GROUP by clause.</p>
<p>The compound SELECT operators UNION,
INTERSECT and EXCEPT perform implicit comparisons between values.
No affinity is applied to comparison operands for the implicit
comparisons associated with UNION, INTERSECT, or EXCEPT - the values
are compared as is.</p>
<a name="collation"></a>
<h1 id="collating_sequences"><span>7. </span>Collating Sequences</h1>
<p>When SQLite compares two strings, it uses a collating sequence or
collating function (two terms for the same thing) to determine which
string is greater or if the two strings are equal.
SQLite has three built-in collating functions: BINARY, NOCASE, and
RTRIM.</p>
<ul>
<li><b>BINARY</b> - Compares string data using memcmp(), regardless
of text encoding.</li>
<li><b>NOCASE</b> - Similar to binary, except that it uses
<a href="c3ref/stricmp.html">sqlite3_strnicmp()</a> for the comparison. Hence the 26 upper case
characters of ASCII are folded to their lower case equivalents before
the comparison is performed. Note that only ASCII characters
are case folded. SQLite does not attempt to do full
UTF case folding due to the size of the tables required.
Also note that any U+0000 characters in the string are considered
string terminators for comparison purposes.
</li><li><b>RTRIM</b> - The same as binary, except that trailing space
characters are ignored.</li>
</ul>
<p>An application can register additional collating functions using
the <a href="c3ref/create_collation.html">sqlite3_create_collation()</a> interface.</p>
<p>Collating functions only matter when comparing string values.
Numeric values are always compared numerically, and BLOBs are always
compared byte-by-byte using memcmp().</p>
<h2 id="assigning_collating_sequences_from_sql"><span>7.1. </span>Assigning Collating Sequences from SQL</h2>
<p>
Every column of every
table has an associated collating function. If no collating function
is explicitly defined, then the collating function defaults to BINARY.
The COLLATE clause of the <a href="lang_createtable.html#tablecoldef">column definition</a> is used
to define alternative collating functions for a column.
</p>
<a name="colrules"></a>
<p>
The rules for determining which collating function to use for a
binary comparison operator (=, <, >, <=, >=, !=, IS, and
IS NOT) are as follows:
</p><ol>
<li><p>If either operand has an explicit collating function assignment
using the postfix <a href="lang_expr.html#collateop">COLLATE operator</a>, then the explicit collating function
is used for comparison, with precedence to the collating function of the
left operand.</p></li>
<li><p>If either operand is a column, then the collating function of
that column is used with precedence to the left operand.
For the purposes of the previous sentence, a column name
preceded by one or more unary "+" operators and/or CAST operators
is still considered a column name.
</p></li>
<li><p>Otherwise, the BINARY collating function is used for comparison.
</p></li>
</ol>
<p>
An operand of a comparison is considered to have an explicit
collating function assignment (rule 1 above)
if any subexpression of the operand uses
the postfix <a href="lang_expr.html#collateop">COLLATE operator</a>. Thus, if a <a href="lang_expr.html#collateop">COLLATE operator</a> is used
anywhere in a comparison expression, the collating function defined
by that operator is used for string comparison regardless of what
table columns might be a part of that expression. If two or more
<a href="lang_expr.html#collateop">COLLATE operator</a> subexpressions appear anywhere in a comparison, the
left most explicit collating function is used regardless of how deeply the
COLLATE operators are nested in the expression and regardless of
how the expression is parenthesized.
</p>
<p>
The expression "x BETWEEN y and z" is logically
equivalent to two comparisons "x >= y AND x <= z" and works with
respect to collating functions as if it were two separate comparisons.
The expression "x IN (SELECT y ...)" is handled in the same way as the
expression "x = y" for the purposes of determining the collating sequence.
The collating sequence used for expressions of the form
"x IN (y, z, ...)" is the collating sequence of x.
If an explicit collating sequence is required on an IN operator
it should be applied to the left operand, like this:
"x COLLATE nocase IN (y,z, ...)".
</p>
<p>
Terms of the ORDER BY clause that is part of a <a href="lang_select.html">SELECT</a>
statement may be assigned a collating sequence using the
<a href="lang_expr.html#collateop">COLLATE operator</a>, in which case the specified collating function is
used for sorting.
Otherwise, if the expression sorted by an ORDER BY clause is
a column, then the collating sequence of the column is used to
determine sort order. If the expression is not a column and has no
COLLATE clause, then the BINARY collating sequence is used.
</p>
<h2 id="collation_sequence_examples"><span>7.2. </span>Collation Sequence Examples</h2>
<p>
The examples below identify the collating sequences that would be used to
determine the results of text comparisons that may be performed by various
SQL statements. Note that a text comparison may not be required, and no
collating sequence used, in the case of numeric, blob or NULL values.
</p>
<blockquote>
<pre>
CREATE TABLE t1(
x INTEGER PRIMARY KEY,
a, /* collating sequence BINARY */
b COLLATE BINARY, /* collating sequence BINARY */
c COLLATE RTRIM, /* collating sequence RTRIM */
d COLLATE NOCASE /* collating sequence NOCASE */
);
/* x a b c d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc', 'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC', 'abc');
/* Text comparison a=b is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = b ORDER BY x;
--result 1 2 3
/* Text comparison a=b is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
--result 1 2 3 4
/* Text comparison d=a is performed using the NOCASE collating sequence. */
SELECT x FROM t1 WHERE d = a ORDER BY x;
--result 1 2 3 4
/* Text comparison a=d is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = d ORDER BY x;
--result 1 4
/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--result 1 2 3
/* Text comparison c='abc' is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--result 1 2 3
/* Grouping is performed using the NOCASE collating sequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4
/* Grouping is performed using the BINARY collating sequence. 'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2
/* Sorting or column c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 ORDER BY c, x;
--result 4 1 2 3
/* Sorting of (c||'') is performed using the BINARY collating sequence. */
SELECT x FROM t1 ORDER BY (c||''), x;
--result 4 2 3 1
/* Sorting of column c is performed using the NOCASE collating sequence. */
SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;
--result 2 4 3 1
</pre>
</blockquote>
|
