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
|
ij> --
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
--
-- Test the builtin type 'bit'
-- Specifically the base-16, hex bit literal
-- stupid test of literals
values(X'aAff');
1
----
aaff
ij> -- casting to a for bit data type
values (cast (x'ee' as char(2) for bit data));
1
----
ee20
ij> values x'aAff' || (cast (x'ee' as char(2) for bit data));
1
--------
aaffee20
ij> -- are the search conditions true?
create table tab1 (c1 char(25));
0 rows inserted/updated/deleted
ij> insert into tab1 values 'search condition is true';
1 row inserted/updated/deleted
ij> select * from tab1 where ((X'1010' || X'0011' || X'0100') = X'101000110100');
C1
-------------------------
search condition is true
ij> select * From tab1 where ((X'1010' || X'0011' || X'0100') = X'101000110100');
C1
-------------------------
search condition is true
ij> select * from tab1 where (X'1100' > X'0011');
C1
-------------------------
search condition is true
ij> drop table tab1;
0 rows inserted/updated/deleted
ij> -- simple negative test
values(X'gg');
ERROR 42X01: Syntax error: Encountered "\'gg\'" at line 2, column 9.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values(X'z');
ERROR 42X01: Syntax error: Encountered "\'z\'" at line 1, column 9.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> values(X'zz');
ERROR 42X01: Syntax error: Encountered "\'zz\'" at line 1, column 9.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> -- fails after bug 5742 is fixed
values(X'9');
ERROR 42606: An invalid hexadecimal constant starting with 'X'9'' has been detected.
ij> -- some quick tests of the length function
-- # bits in a string expression
values({fn length(X'ab')} * 8);
ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.
ij> values({fn length(X'11')} * 8);
ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.
ij> -- # characters in a string expression
values({fn length(X'ab')});
ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.
ij> values({fn length(X'11')});
ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.
ij> -- # octets in a string expression
values({fn length(X'ab')});
ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.
ij> values({fn length(X'11')});
ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.
ij> -- stupid test for syntax
create table t1 (b1 char for bit data, b2 char(2) for bit data, b3 varchar(2) for bit data, b4 LONG VARCHAR FOR BIT DATA,
b5 LONG VARCHAR FOR BIT DATA, b6 LONG VARCHAR FOR BIT DATA);
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> create table t1 (b1 char for bit data, b2 char(1) for bit data not null, b3 varchar(1) for bit data not null,
b4 LONG VARCHAR FOR BIT DATA not null, b5 LONG VARCHAR FOR BIT DATA not null,
b6 LONG VARCHAR FOR BIT DATA not null);
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> create table t (i int, s smallint, c char(10), v varchar(50),
d double precision, r real, b char (2) for bit data, bv varchar(8) for bit data,
lbv LONG VARCHAR FOR BIT DATA);
0 rows inserted/updated/deleted
ij> -- explicit null
insert into t values (null, null, null, null, null, null, null, null, null);
1 row inserted/updated/deleted
ij> -- implicit null
insert into t (i) values (null);
1 row inserted/updated/deleted
ij> select b, bv, lbv from t;
B |BV |LBV
------------------------------------------------------------------------------------------------------------------------------------------------------
NULL|NULL |NULL
NULL|NULL |NULL
ij> -- sample data
insert into t values (0, 100, 'hello', 'everyone is here', 200.0e0, 200.0e0,
X'12af', X'0000111100001111', X'abc123');
1 row inserted/updated/deleted
ij> insert into t values (-1, -100, 'goodbye', 'everyone is there', -200.0e0, -200.0e0,
X'0000', X'', X'10101010');
1 row inserted/updated/deleted
ij> -- truncation -- should get an error
insert into t (b, bv) values (X'ffffffff', X'ffffffff');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA 'ffffffff' to length 2.
ij> select b, bv, lbv from t;
B |BV |LBV
------------------------------------------------------------------------------------------------------------------------------------------------------
NULL|NULL |NULL
NULL|NULL |NULL
12af|0000111100001111|abc123
0000| |10101010
ij> -- padding -- will be warning, some day (not now)
insert into t (b, bv) values (X'01', X'01');
1 row inserted/updated/deleted
ij> insert into t (b, bv) values (X'', X'');
1 row inserted/updated/deleted
ij> select b, bv from t;
B |BV
---------------------
NULL|NULL
NULL|NULL
12af|0000111100001111
0000|
0120|01
2020|
ij> drop table t;
0 rows inserted/updated/deleted
ij> --
-- simple comparisons
-- returns 1 if the search conditions are true
--
create table nulltab (b char(1) for bit data);
0 rows inserted/updated/deleted
ij> insert into nulltab values (null);
1 row inserted/updated/deleted
ij> select 1 from nulltab where X'0001' > X'0000';
1
-----------
1
ij> select 1 from nulltab where X'0100' > X'0001';
1
-----------
1
ij> select 1 from nulltab where X'ff00' > X'00ff';
1
-----------
1
ij> select 1 from nulltab where X'0100' > X'0100';
1
-----------
ij> select 1 from nulltab where X'0100' > b;
1
-----------
ij> select 1 from nulltab where X'0001' >= X'0000';
1
-----------
1
ij> select 1 from nulltab where X'0100' >= X'0001';
1
-----------
1
ij> select 1 from nulltab where X'ff00' >= X'00ff';
1
-----------
1
ij> select 1 from nulltab where X'0100' >= b;
1
-----------
ij> select 1 from nulltab where X'0001' < X'0000';
1
-----------
ij> select 1 from nulltab where X'0100' < X'0001';
1
-----------
ij> select 1 from nulltab where X'ff00' < X'00ff';
1
-----------
ij> select 1 from nulltab where X'0100' < b;
1
-----------
ij> select 1 from nulltab where X'0001' <= X'0000';
1
-----------
ij> select 1 from nulltab where X'0100' <= X'0001';
1
-----------
ij> select 1 from nulltab where X'ff00' <= X'00ff';
1
-----------
ij> select 1 from nulltab where X'0100' <= b;
1
-----------
ij> drop table nulltab;
0 rows inserted/updated/deleted
ij> --
-- select comparisons
--
create table t (b10 char(20) for bit data, vb10 varchar(20) for bit data, b16 char(2) for bit data, vb16 varchar(2) for bit data, lbv LONG VARCHAR FOR BIT DATA, c20 char(20), cv20 varchar(20));
0 rows inserted/updated/deleted
ij> insert into t values (null, null, null, null, null, 'null', 'null columns');
1 row inserted/updated/deleted
ij> insert into t values (X'', X'', X'', X'', X'', '0', 'zero length column');
1 row inserted/updated/deleted
ij> insert into t values (X'0000000001', X'0000000001', X'01', X'01', X'0000000001', '1', '1');
1 row inserted/updated/deleted
ij> insert into t values (X'0000000011', X'0000000011', X'03', X'03', X'03', '3', '3');
1 row inserted/updated/deleted
ij> insert into t values (X'1111111111', X'1111111111', X'ff', X'ff', X'1111111111', 'ff', 'ff');
1 row inserted/updated/deleted
ij> insert into t values (X'11', X'11', X'aa', X'aa', X'aa', 'aa', 'aa');
1 row inserted/updated/deleted
ij> -- make sure built-in functions work ok on binary types,
-- it is a little special since it maps to an
-- array. use length to make sure it wont
-- diff from run to run
select {fn length(cast(b10 as char(10)))} from t where b10 is not null;
ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'CHAR'.
ij> select {fn length(cast(vb10 as char(10)))} from t where vb10 is not null;
ERROR 42846: Cannot convert types 'VARCHAR () FOR BIT DATA' to 'CHAR'.
ij> select {fn length(cast(lbv as char(10)))} from t where vb10 is not null;
ERROR 42846: Cannot convert types 'LONG VARCHAR FOR BIT DATA' to 'CHAR'.
ij> select b10, c20, cv20 from t order by b10 asc;
B10 |C20 |CV20
----------------------------------------------------------------------------------
0000000001202020202020202020202020202020|1 |1
0000000011202020202020202020202020202020|3 |3
1111111111202020202020202020202020202020|ff |ff
1120202020202020202020202020202020202020|aa |aa
2020202020202020202020202020202020202020|0 |zero length column
NULL |null |null columns
ij> select b10, c20, cv20 from t order by b10 desc;
B10 |C20 |CV20
----------------------------------------------------------------------------------
NULL |null |null columns
2020202020202020202020202020202020202020|0 |zero length column
1120202020202020202020202020202020202020|aa |aa
1111111111202020202020202020202020202020|ff |ff
0000000011202020202020202020202020202020|3 |3
0000000001202020202020202020202020202020|1 |1
ij> select vb10, c20, cv20 from t order by vb10;
VB10 |C20 |CV20
----------------------------------------------------------------------------------
|0 |zero length column
0000000001 |1 |1
0000000011 |3 |3
11 |aa |aa
1111111111 |ff |ff
NULL |null |null columns
ij> select b16, c20, cv20 from t order by b16;
B16 |C20 |CV20
----------------------------------------------
0120|1 |1
0320|3 |3
2020|0 |zero length column
aa20|aa |aa
ff20|ff |ff
NULL|null |null columns
ij> select vb16, c20, cv20 from t order by vb16;
VB16|C20 |CV20
----------------------------------------------
|0 |zero length column
01 |1 |1
03 |3 |3
aa |aa |aa
ff |ff |ff
NULL|null |null columns
ij> select vb16, c20, cv20, lbv from t order by lbv;
ERROR X0X67: Columns of type 'LONG VARCHAR FOR BIT DATA' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> select b10 from t where b10 > X'0000000010';
B10
----------------------------------------
2020202020202020202020202020202020202020
0000000011202020202020202020202020202020
1111111111202020202020202020202020202020
1120202020202020202020202020202020202020
ij> select b10 from t where b10 < X'0000000010';
B10
----------------------------------------
0000000001202020202020202020202020202020
ij> select b10 from t where b10 <= X'0000000011';
B10
----------------------------------------
0000000001202020202020202020202020202020
0000000011202020202020202020202020202020
ij> select b10 from t where b10 >= X'0000000011';
B10
----------------------------------------
2020202020202020202020202020202020202020
0000000011202020202020202020202020202020
1111111111202020202020202020202020202020
1120202020202020202020202020202020202020
ij> select b10 from t where b10 <> X'0000000011';
B10
----------------------------------------
2020202020202020202020202020202020202020
0000000001202020202020202020202020202020
1111111111202020202020202020202020202020
1120202020202020202020202020202020202020
ij> select vb10 from t where vb10 > X'0000000010';
VB10
----------------------------------------
0000000011
1111111111
11
ij> select vb10 from t where vb10 < X'0000000010';
VB10
----------------------------------------
0000000001
ij> select vb10 from t where vb10 <= X'0000000011';
VB10
----------------------------------------
0000000001
0000000011
ij> select vb10 from t where vb10 >= X'0000000011';
VB10
----------------------------------------
0000000011
1111111111
11
ij> select vb10 from t where vb10 <> X'0000000011';
VB10
----------------------------------------
0000000001
1111111111
11
ij> select b16 from t where b16 > X'0000000010';
B16
----
2020
0120
0320
ff20
aa20
ij> select b16 from t where b16 < X'0000000010';
B16
----
ij> select b16 from t where b16 <= X'0000000011';
B16
----
ij> select b16 from t where b16 >= X'0000000011';
B16
----
2020
0120
0320
ff20
aa20
ij> select b16 from t where b16 <> X'0000000011';
B16
----
2020
0120
0320
ff20
aa20
ij> select vb16 from t where vb16 > X'0000000010';
VB16
----
01
03
ff
aa
ij> select vb16 from t where vb16 < X'0000000010';
VB16
----
ij> select vb16 from t where vb16 <= X'0000000011';
VB16
----
ij> select vb16 from t where vb16 >= X'0000000011';
VB16
----
01
03
ff
aa
ij> select vb16 from t where vb16 <> X'0000000011';
VB16
----
01
03
ff
aa
ij> select lbv from t where lbv > X'0000000010';
ERROR 42818: Comparisons between 'LONG VARCHAR FOR BIT DATA' and 'CHAR () FOR BIT DATA' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select lbv from t where lbv < X'0000000010';
ERROR 42818: Comparisons between 'LONG VARCHAR FOR BIT DATA' and 'CHAR () FOR BIT DATA' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select lbv from t where lbv <= X'0000000011';
ERROR 42818: Comparisons between 'LONG VARCHAR FOR BIT DATA' and 'CHAR () FOR BIT DATA' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select lbv from t where lbv >= X'0000000011';
ERROR 42818: Comparisons between 'LONG VARCHAR FOR BIT DATA' and 'CHAR () FOR BIT DATA' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select lbv from t where lbv <> X'0000000011';
ERROR 42818: Comparisons between 'LONG VARCHAR FOR BIT DATA' and 'CHAR () FOR BIT DATA' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
ij> select b10, vb10||X'11' from t where vb10||X'11' > b10;
B10 |2
-----------------------------------------------------------------------------------
ij> select b10, X'11'||vb10 from t where X'11'||vb10 > b10;
B10 |2
-----------------------------------------------------------------------------------
0000000001202020202020202020202020202020|110000000001
0000000011202020202020202020202020202020|110000000011
ij> select b16, vb16||X'11' from t where vb16||X'11' > b16;
B16 |2
-----------
ij> select b10 || vb10 from t;
1
--------------------------------------------------------------------------------
NULL
2020202020202020202020202020202020202020
00000000012020202020202020202020202020200000000001
00000000112020202020202020202020202020200000000011
11111111112020202020202020202020202020201111111111
112020202020202020202020202020202020202011
ij> select lbv || b10 from t;
1
--------------------------------------------------------------------------------------------------------------------------------
NULL
2020202020202020202020202020202020202020
00000000010000000001202020202020202020202020202020
030000000011202020202020202020202020202020
11111111111111111111202020202020202020202020202020
aa1120202020202020202020202020202020202020
ij> select b10 || lbv from t;
1
--------------------------------------------------------------------------------------------------------------------------------
NULL
2020202020202020202020202020202020202020
00000000012020202020202020202020202020200000000001
000000001120202020202020202020202020202003
11111111112020202020202020202020202020201111111111
1120202020202020202020202020202020202020aa
ij> select lbv || vb10 from t;
1
--------------------------------------------------------------------------------------------------------------------------------
NULL
00000000010000000001
030000000011
11111111111111111111
aa11
ij> select vb10 || lbv from t;
1
--------------------------------------------------------------------------------------------------------------------------------
NULL
00000000010000000001
000000001103
11111111111111111111
11aa
ij> select t1.b10 from t t1, t t2 where t1.b10 > t2.b10;
B10
----------------------------------------
2020202020202020202020202020202020202020
2020202020202020202020202020202020202020
2020202020202020202020202020202020202020
2020202020202020202020202020202020202020
0000000011202020202020202020202020202020
1111111111202020202020202020202020202020
1111111111202020202020202020202020202020
1120202020202020202020202020202020202020
1120202020202020202020202020202020202020
1120202020202020202020202020202020202020
ij> -- FUNCTIONS
-- some length functions
select {fn length(b10)} from t;
ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.
ij> select {fn length(vb10)} from t;
ERROR 42846: Cannot convert types 'VARCHAR () FOR BIT DATA' to 'VARCHAR'.
ij> select {fn length(lbv)} from t;
ERROR 42846: Cannot convert types 'LONG VARCHAR FOR BIT DATA' to 'VARCHAR'.
ij> select {fn length(c20)} from t;
1
-----------
4
1
1
1
2
2
ij> select {fn length(cv20)} from t;
1
-----------
12
18
1
1
2
2
ij> drop table t;
0 rows inserted/updated/deleted
ij> -----------------------
-- test normalization
-----------------------
create table t1 (c1 char(2) for bit data);
0 rows inserted/updated/deleted
ij> insert into t1 values (X'0001');
1 row inserted/updated/deleted
ij> insert into t1 values (X'0010');
1 row inserted/updated/deleted
ij> insert into t1 values (X'0011');
1 row inserted/updated/deleted
ij> select * from t1;
C1
----
0001
0010
0011
ij> -- now insert something that needs to be expanded
insert into t1 values (X'11');
1 row inserted/updated/deleted
ij> select * from t1;
C1
----
0001
0010
0011
1120
ij> -- insert select, expand 1 byte
create table t2 (c1 char(3) for bit data);
0 rows inserted/updated/deleted
ij> insert into t2 select c1 from t1;
4 rows inserted/updated/deleted
ij> select * from t2;
C1
------
000120
001020
001120
112020
ij> drop table t2;
0 rows inserted/updated/deleted
ij> -- insert select, expand many bytes
create table t2 (c1 char(20) for bit data);
0 rows inserted/updated/deleted
ij> insert into t2 select c1 from t1;
4 rows inserted/updated/deleted
ij> select * from t2;
C1
----------------------------------------
0001202020202020202020202020202020202020
0010202020202020202020202020202020202020
0011202020202020202020202020202020202020
1120202020202020202020202020202020202020
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> --
-- some extra tests for truncation. in 2.0
create table t1 (b1 char(1) for bit data);
0 rows inserted/updated/deleted
ij> -- ok
insert into t1 values (X'11');
1 row inserted/updated/deleted
ij> -- valid length
insert into t1 values (X'10');
1 row inserted/updated/deleted
ij> insert into t1 values (X'11');
1 row inserted/updated/deleted
ij> -- truncation errors
insert into t1 values (X'1000');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '1000' to length 1.
ij> insert into t1 values (X'100000');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '100000' to length 1.
ij> insert into t1 values (X'10000000');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '10000000' to length 1.
ij> insert into t1 values (X'1000000000');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '1000000000' to length 1.
ij> insert into t1 values (X'100001');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '100001' to length 1.
ij> insert into t1 values (X'0001');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '0001' to length 1.
ij> insert into t1 values (X'8001');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '8001' to length 1.
ij> insert into t1 values (X'8000');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '8000' to length 1.
ij> drop table t1;
0 rows inserted/updated/deleted
ij> create table t1 (b9 char(2) for bit data);
0 rows inserted/updated/deleted
ij> -- ok
insert into t1 values (X'1111');
1 row inserted/updated/deleted
ij> -- truncation errors
insert into t1 values (X'111100');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '111100' to length 2.
ij> insert into t1 values (X'11110000');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '11110000' to length 2.
ij> insert into t1 values (X'1111000000');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '1111000000' to length 2.
ij> insert into t1 values (X'1111111100000000');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '1111111100000000' to length 2.
ij> insert into t1 values (X'1111111111');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '1111111111' to length 2.
ij> insert into t1 values (X'11111111100001');
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '11111111100001' to length 2.
ij> insert into t1 values (X'0001');
1 row inserted/updated/deleted
ij> insert into t1 values (X'8001');
1 row inserted/updated/deleted
ij> insert into t1 values (X'8000');
1 row inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> -- a few other conditions
create table t1 (b3 char(2) for bit data, b7 char(4) for bit data, b8 char (5) for bit data, b15 char(8) for bit data, b16 char(9) for bit data);
0 rows inserted/updated/deleted
ij> -- ok
insert into t1 values
(
X'1111',
X'11111111',
X'1111111111',
X'1111111111111111',
X'111111111111111111'
);
1 row inserted/updated/deleted
ij> -- ok
insert into t1 values
(
X'1110',
X'11111110',
X'11111111',
X'1111111111111110',
X'1111111111111111'
);
1 row inserted/updated/deleted
ij> -- bad
-- truncation error for column b8
insert into t1 values
(
null,
null,
X'111111111110',
null,
null
);
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '111111111110' to length 5.
ij> -- truncation error for column b7
insert into t1 values
(
null,
X'1111111100',
null,
null,
null
);
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '1111111100' to length 4.
ij> -- truncation error for column b7
insert into t1 values
(
null,
X'1111111111',
null,
null,
null
);
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '1111111111' to length 4.
ij> -- truncation error for column b15
insert into t1 values
(
null,
null,
null,
X'111111111111111100',
null
);
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '111111111111111100' to length 8.
ij> -- truncation error for column b15
insert into t1 values
(
null,
null,
null,
X'111111111111111111',
null
);
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '111111111111111111' to length 8.
ij> -- truncation error for column b16
insert into t1 values
(
null,
null,
null,
null,
X'11111111111111111110'
);
ERROR 22001: A truncation error was encountered trying to shrink CHAR () FOR BIT DATA '11111111111111111110' to length 9.
ij> AUTOCOMMIT OFF;
ij> -- bug 5160 - incorrect typing of VALUES table constructor on an insert;
create table iv (id int, vc varchar(12));
0 rows inserted/updated/deleted
ij> insert into iv values (1, 'abc'), (2, 'defghijk'), (3, 'lmnopqrstcc');
3 rows inserted/updated/deleted
ij> insert into iv values (4, null), (5, 'null ok?'), (6, '2blanks ');
3 rows inserted/updated/deleted
ij> insert into iv values (7, 'dddd'), (8, '0123456789123'), (9, 'too long');
ERROR 22001: A truncation error was encountered trying to shrink VARCHAR '0123456789123' to length 12.
ij> select id, vc, {fn length(vc)} AS LEN from iv order by 1;
ID |VC |LEN
------------------------------------
1 |abc |3
2 |defghijk |8
3 |lmnopqrstcc |11
4 |NULL |NULL
5 |null ok? |8
6 |2blanks |7
ij> -- the inner values must not be changed to VARCHAR as it is not the table constructor
insert into iv select * from (values (10, 'pad'), (11, 'pad me'), (12, 'anakin jedi')) as t(i, c);
3 rows inserted/updated/deleted
ij> select id, vc, {fn length(vc)} AS LEN from iv order by 1;
ID |VC |LEN
------------------------------------
1 |abc |3
2 |defghijk |8
3 |lmnopqrstcc |11
4 |NULL |NULL
5 |null ok? |8
6 |2blanks |7
10 |pad |3
11 |pad me |6
12 |anakin jedi |11
ij> -- check values outside of table constructors retain their CHARness
select c, {fn length(c)} AS LEN from (values (1, 'abc'), (2, 'defghijk'), (3, 'lmnopqrstcc')) as t(i, c);
C |LEN
-----------------------
abc |3
defghijk |8
lmnopqrstcc|11
ij> drop table iv;
0 rows inserted/updated/deleted
ij> create table bv (id int, vb varchar(16) for bit data);
0 rows inserted/updated/deleted
ij> insert into bv values (1, X'1a'), (2, X'cafebabe'), (3, null);
3 rows inserted/updated/deleted
ij> select id, vb, {fn length(vb)} AS LEN from bv order by 1;
ERROR 42846: Cannot convert types 'VARCHAR () FOR BIT DATA' to 'VARCHAR'.
ij> drop table bv;
0 rows inserted/updated/deleted
ij> create table dv (id int, vc varchar(12));
0 rows inserted/updated/deleted
ij> -- beetle 5568
-- should fail because DB2 doesn't allow this implicit casting to string
insert into dv values (1, 1.2), (2, 34.5639), (3, null);
ERROR 42X61: Types 'DECIMAL' and 'VARCHAR' are not UNION compatible.
ij> -- should pass
insert into dv values (1, '1.2'), (2, '34.5639'), (3, null);
3 rows inserted/updated/deleted
ij> select id, vc from dv order by 1;
ID |VC
------------------------
1 |1.2
2 |34.5639
3 |NULL
ij> drop table dv;
0 rows inserted/updated/deleted
ij> -- bug 5306 -- incorrect padding of VALUES table constructor on an insert,
-- when implicit casting (bit->char or char->bit) is used.
-- 5306: Char -> For Bit Data Types
create table bitTable (id int, bv LONG VARCHAR FOR BIT DATA);
0 rows inserted/updated/deleted
ij> insert into bitTable values (1, X'031'), (2, X'032'), (3, X'');
ERROR 42606: An invalid hexadecimal constant starting with 'X'031'' has been detected.
ij> insert into bitTable values (4, null), (5, X'033'), (6, X'2020');
ERROR 42606: An invalid hexadecimal constant starting with 'X'033'' has been detected.
ij> select id, bv, {fn length(bv)} as LEN from bitTable order by 1;
ERROR 42846: Cannot convert types 'LONG VARCHAR FOR BIT DATA' to 'VARCHAR'.
ij> -- the inner values must not be changed to varying, as it is not the table constructor
insert into bitTable select * from (values (10, 'pad'), (11, 'pad me'), (12, 'anakin jedi')) as t(i, c);
ERROR 42821: Columns of type 'LONG VARCHAR FOR BIT DATA' cannot hold values of type 'CHAR'.
ij> select id, bv, {fn length(bv)} AS LEN from bitTable order by 1;
ERROR 42846: Cannot convert types 'LONG VARCHAR FOR BIT DATA' to 'VARCHAR'.
ij> drop table bitTable;
0 rows inserted/updated/deleted
ij> -- 5306: Bit -> Char
create table charTable (id int, cv long varchar);
0 rows inserted/updated/deleted
ij> insert into charTable values (1, x'0101'), (2, x'00101100101001'), (3, x'');
ERROR 42821: Columns of type 'LONG VARCHAR' cannot hold values of type 'LONG VARCHAR FOR BIT DATA'.
ij> insert into charTable values (4, null), (5, x'1010101111'), (6, x'1000');
ERROR 42X61: Types 'LONG VARCHAR' and 'LONG VARCHAR FOR BIT DATA' are not UNION compatible.
ij> select id, cv, {fn length(cv)} as LEN from charTable order by 1;
ID |CV |LEN
--------------------------------------------------------------------------------------------------------------------------------------------------------
ij> -- the inner values must not be changed to varying, as it is not the table constructor
insert into charTable select * from (values (10, x'001010'), (11, x'01011010101111'), (12, x'0101010101000010100101110101')) as t(i, c);
ERROR 42821: Columns of type 'LONG VARCHAR' cannot hold values of type 'CHAR () FOR BIT DATA'.
ij> select id, cv, {fn length(cv)} AS LEN from charTable order by 1;
ID |CV |LEN
--------------------------------------------------------------------------------------------------------------------------------------------------------
ij> drop table charTable;
0 rows inserted/updated/deleted
ij> -- Verify that 5306 still works with Union.
create table pt5 (b5 char(2) for bit data);
0 rows inserted/updated/deleted
ij> create table pt10 (b10 char (4) for bit data);
0 rows inserted/updated/deleted
ij> insert into pt10 values (x'01000110');
1 row inserted/updated/deleted
ij> insert into pt5 values (x'1010');
1 row inserted/updated/deleted
ij> select {fn length(CM)} from (select b5 from pt5 union all select b10 from pt10) as t(CM);
ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.
ij> drop table pt5;
0 rows inserted/updated/deleted
ij> drop table pt10;
0 rows inserted/updated/deleted
ij> -- beetle 5612
create table t5612 (c1 char(10), c2 varchar(10), c3 long varchar);
0 rows inserted/updated/deleted
ij> insert into t5612 values (X'00680069', X'00680069', X'00680069');
ERROR 42821: Columns of type 'CHAR' cannot hold values of type 'CHAR () FOR BIT DATA'.
ij> select * from t5612;
C1 |C2 |C3
------------------------------------------------------------------------------------------------------------------------------------------------------
ij> values cast(X'00680069' as char(30)), cast(X'00680069' as varchar(30)), cast(X'00680069' as long varchar);
ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'CHAR'.
ij> -- DERBY-1085
create table npetest1 (col1 varchar(36) for bit data not null, constraint pknpe1 primary key (col1));
0 rows inserted/updated/deleted
ij> create table npetest2 (col2 varchar(36) for bit data, constraint fknpe1 foreign key (col2) references npetest1(col1) on delete cascade);
0 rows inserted/updated/deleted
ij> insert into npetest1 (col1) values (X'0000000001');
1 row inserted/updated/deleted
ij> insert into npetest1 (col1) values (X'0000000002');
1 row inserted/updated/deleted
ij> insert into npetest1 (col1) values (X'0000000003');
1 row inserted/updated/deleted
ij> insert into npetest2 (col2) values (X'0000000001');
1 row inserted/updated/deleted
ij> insert into npetest2 (col2) values (NULL);
1 row inserted/updated/deleted
ij> insert into npetest2 (col2) values (X'0000000002');
1 row inserted/updated/deleted
ij> select col1 from npetest1 where col1 not in (select col2 from npetest2);
COL1
------------------------------------------------------------------------
ij> select col1 from npetest1 where col1 not in (select col2 from npetest2 where col2 is not null);
COL1
------------------------------------------------------------------------
0000000003
ij> drop table npetest2;
0 rows inserted/updated/deleted
ij> drop table npetest1;
0 rows inserted/updated/deleted
ij>
|
