Debian Codegraph @

mysql-test/suite/ndb/t/ndb_types.test

raw
1 --source include/have_ndb.inc
2
3 --disable_warnings
4 DROP TABLE IF EXISTS t1;
5 --enable_warnings
6
7 #
8 # Test creation of different column types in NDB
9 #
10 CREATE TABLE t1 (
11 auto int(5) unsigned NOT NULL auto_increment,
12 string char(10) default "hello",
13 vstring varchar(10) default "hello",
14 bin binary(2),
15 vbin varbinary(7),
16 tiny tinyint(4) DEFAULT '0' NOT NULL ,
17 short smallint(6) DEFAULT '1' NOT NULL ,
18 medium mediumint(8) DEFAULT '0' NOT NULL,
19 long_int int(11) DEFAULT '0' NOT NULL,
20 longlong bigint(13) DEFAULT '0' NOT NULL,
21 real_float float(13,1) DEFAULT 0.0 NOT NULL,
22 real_double double(16,4),
23 real_decimal decimal(16,4),
24 utiny tinyint(3) unsigned DEFAULT '0' NOT NULL,
25 ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL,
26 umedium mediumint(8) unsigned DEFAULT '0' NOT NULL,
27 ulong int(11) unsigned DEFAULT '0' NOT NULL,
28 ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL,
29 bits bit(3),
30 options enum('one','two','tree') not null,
31 flags set('one','two','tree') not null,
32 date_field date,
33 year_field year,
34 time_field time,
35 date_time datetime,
36 time_stamp timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
37 PRIMARY KEY (auto),
38 KEY (utiny),
39 KEY (tiny),
40 KEY (short),
41 KEY any_name (medium),
42 KEY (longlong),
43 KEY (real_float),
44 KEY (ushort),
45 KEY (umedium),
46 KEY (ulong),
47 KEY (ulonglong,ulong),
48 KEY (options,flags)
49 );
50
51 set @now = now();
52 --sleep 1.5
53 insert into t1
54 (string,vstring,bin,vbin,tiny,short,medium,long_int,longlong,
55 real_float,real_double, real_decimal,utiny, ushort, umedium,ulong,ulonglong,
56 bits,options,flags,date_field,year_field,time_field,date_time)
57 values
58 ("aaaa","aaaa",0xAAAA,0xAAAA,-1,-1,-1,-1,-1,1.1,1.1,1.1,1,1,1,1,1,
59 b'001','one','one', '1901-01-01','1901','01:01:01','1901-01-01 01:01:01');
60
61 select auto,string,vstring,hex(bin),hex(vbin),tiny,short,medium,long_int,longlong,
62 real_float,real_double,real_decimal,utiny,ushort,umedium,ulong,ulonglong,
63 bin(bits),options,flags,date_field,year_field,time_field,date_time
64 from t1;
65 select time_stamp>@now from t1;
66
67 set @now = now();
68 --sleep 1.5
69 update t1 set string="bbbb",vstring="bbbb",bin=0xBBBB,vbin=0xBBBB,
70 tiny=-2,short=-2,medium=-2,long_int=-2,longlong=-2,real_float=2.2,
71 real_double=2.2,real_decimal=2.2,utiny=2,ushort=2,umedium=2,ulong=2,
72 ulonglong=2, bits=b'010',
73 options='one',flags='one', date_field='1902-02-02',year_field='1902',
74 time_field='02:02:02',date_time='1902-02-02 02:02:02' where auto=1;
75
76 select auto,string,vstring,hex(bin),hex(vbin),tiny,short,medium,long_int,longlong,
77 real_float,real_double,real_decimal,utiny,ushort,umedium,ulong,ulonglong,
78 bin(bits),options,flags,date_field,year_field,time_field,date_time
79 from t1;
80 select time_stamp>@now from t1;
81
82 drop table t1;
83
84 --echo End of 4.1 tests
85
86 # Bug#14798022 : "CHAR (0)" DATATYPE CAUSING ERROR 1296 (HY000): GOT ERROR 4547
87
88 create table t1 (
89 a int primary key,
90 b char(0),
91 c char(1)
92 )engine NDB;
93
94 --echo #test for insertion in char(0) column
95 insert into t1 values(1, '', 'a'), (2, NULL, 'b');
96
97 --echo #testing values stored in the table t1
98 select * from t1 order by a;
99 select count(*) from t1 where b = '';
100 select count(*) from t1 where b is null;
101
102 drop table t1;
103
104 --echo #testing bit(1) with char(0)
105 create table t1 (
106 a int primary key,
107 b char(0),
108 c bit(1)
109 )engine NDB;
110
111 --echo #testing insertion
112 insert into t1 values(1, '', 0),
113 (2, '', 1),
114 (3, '', NULL),
115 (4, NULL, 0),
116 (5, NULL, 1),
117 (6, NULL, NULL);
118
119 --echo #verify data in table
120 select a,b,bin(c) from t1 where b = '' and c is null order by a;
121 select a,b,bin(c) from t1 where b is null and c is null order by a;
122 select a,b,bin(c) from t1 where b = '' and c = 0 order by a;
123 select a,b,bin(c) from t1 where b is null and c = 0 order by a;
124 select a,b,bin(c) from t1 where b = '' and c = 1 order by a;
125 select a,b,bin(c) from t1 where b is null and c = 1 order by a;
126
127 drop table t1;
128
129 --echo #testing char(0) not null
130 create table t1 (
131 a int primary key,
132 b char(0) not null,
133 c char(1)
134 )engine NDB;
135
136 insert into t1 values(1, '', 'a'), (2, '', 'b');
137
138 --echo #testing values stored in the table t1
139 select * from t1 order by a;
140 select count(*) from t1 where b = '';
141 select count(*) from t1 where b is null;
142
143 drop table t1;
144
145 #
146 # Check that the default "dummy" timestamp is still allowed
147 # when you turn off flag in sql_mode which prevent it
148 #
149 --disable_query_log
150 set @save_sql_mode=@@sql_mode;
151 set sql_mode=(select replace(@@sql_mode,'NO_ZERO_DATE',''));
152 --enable_query_log
153 create table t1 (
154 a int primary key,
155 b timestamp not null default '0000-00-00 00:00:00',
156 c timestamp not null,
157 d timestamp NULL
158 ) engine = NDB;
159 insert into t1 (a,c) values (37, '0000-00-00 00:00:00');
160 select b,c,d from t1;
161 drop table t1;
162 --disable_query_log
163 set sql_mode=@save_sql_mode;
164 --enable_query_log
165
166