Debian Codegraph @

mysql-test/suite/ndb/r/ndb_types.result

raw
1 DROP TABLE IF EXISTS t1;
2 CREATE TABLE t1 (
3 auto int(5) unsigned NOT NULL auto_increment,
4 string char(10) default "hello",
5 vstring varchar(10) default "hello",
6 bin binary(2),
7 vbin varbinary(7),
8 tiny tinyint(4) DEFAULT '0' NOT NULL ,
9 short smallint(6) DEFAULT '1' NOT NULL ,
10 medium mediumint(8) DEFAULT '0' NOT NULL,
11 long_int int(11) DEFAULT '0' NOT NULL,
12 longlong bigint(13) DEFAULT '0' NOT NULL,
13 real_float float(13,1) DEFAULT 0.0 NOT NULL,
14 real_double double(16,4),
15 real_decimal decimal(16,4),
16 utiny tinyint(3) unsigned DEFAULT '0' NOT NULL,
17 ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL,
18 umedium mediumint(8) unsigned DEFAULT '0' NOT NULL,
19 ulong int(11) unsigned DEFAULT '0' NOT NULL,
20 ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL,
21 bits bit(3),
22 options enum('one','two','tree') not null,
23 flags set('one','two','tree') not null,
24 date_field date,
25 year_field year,
26 time_field time,
27 date_time datetime,
28 time_stamp timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
29 PRIMARY KEY (auto),
30 KEY (utiny),
31 KEY (tiny),
32 KEY (short),
33 KEY any_name (medium),
34 KEY (longlong),
35 KEY (real_float),
36 KEY (ushort),
37 KEY (umedium),
38 KEY (ulong),
39 KEY (ulonglong,ulong),
40 KEY (options,flags)
41 );
42 Warnings:
43 Warning 1681 Integer display width is deprecated and will be removed in a future release.
44 Warning 1681 Integer display width is deprecated and will be removed in a future release.
45 Warning 1681 Integer display width is deprecated and will be removed in a future release.
46 Warning 1681 Integer display width is deprecated and will be removed in a future release.
47 Warning 1681 Integer display width is deprecated and will be removed in a future release.
48 Warning 1681 Integer display width is deprecated and will be removed in a future release.
49 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
50 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
51 Warning 1681 Integer display width is deprecated and will be removed in a future release.
52 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
53 Warning 1681 Integer display width is deprecated and will be removed in a future release.
54 Warning 1681 Integer display width is deprecated and will be removed in a future release.
55 Warning 1681 Integer display width is deprecated and will be removed in a future release.
56 Warning 1681 Integer display width is deprecated and will be removed in a future release.
57 set @now = now();
58 insert into t1
59 (string,vstring,bin,vbin,tiny,short,medium,long_int,longlong,
60 real_float,real_double, real_decimal,utiny, ushort, umedium,ulong,ulonglong,
61 bits,options,flags,date_field,year_field,time_field,date_time)
62 values
63 ("aaaa","aaaa",0xAAAA,0xAAAA,-1,-1,-1,-1,-1,1.1,1.1,1.1,1,1,1,1,1,
64 b'001','one','one', '1901-01-01','1901','01:01:01','1901-01-01 01:01:01');
65 select auto,string,vstring,hex(bin),hex(vbin),tiny,short,medium,long_int,longlong,
66 real_float,real_double,real_decimal,utiny,ushort,umedium,ulong,ulonglong,
67 bin(bits),options,flags,date_field,year_field,time_field,date_time
68 from t1;
69 auto string vstring hex(bin) hex(vbin) tiny short medium long_int longlong real_float real_double real_decimal utiny ushort umedium ulong ulonglong bin(bits) options flags date_field year_field time_field date_time
70 1 aaaa aaaa AAAA AAAA -1 -1 -1 -1 -1 1.1 1.1000 1.1000 1 00001 1 1 1 1 one one 1901-01-01 1901 01:01:01 1901-01-01 01:01:01
71 select time_stamp>@now from t1;
72 time_stamp>@now
73 1
74 set @now = now();
75 update t1 set string="bbbb",vstring="bbbb",bin=0xBBBB,vbin=0xBBBB,
76 tiny=-2,short=-2,medium=-2,long_int=-2,longlong=-2,real_float=2.2,
77 real_double=2.2,real_decimal=2.2,utiny=2,ushort=2,umedium=2,ulong=2,
78 ulonglong=2, bits=b'010',
79 options='one',flags='one', date_field='1902-02-02',year_field='1902',
80 time_field='02:02:02',date_time='1902-02-02 02:02:02' where auto=1;
81 select auto,string,vstring,hex(bin),hex(vbin),tiny,short,medium,long_int,longlong,
82 real_float,real_double,real_decimal,utiny,ushort,umedium,ulong,ulonglong,
83 bin(bits),options,flags,date_field,year_field,time_field,date_time
84 from t1;
85 auto string vstring hex(bin) hex(vbin) tiny short medium long_int longlong real_float real_double real_decimal utiny ushort umedium ulong ulonglong bin(bits) options flags date_field year_field time_field date_time
86 1 bbbb bbbb BBBB BBBB -2 -2 -2 -2 -2 2.2 2.2000 2.2000 2 00002 2 2 2 10 one one 1902-02-02 1902 02:02:02 1902-02-02 02:02:02
87 select time_stamp>@now from t1;
88 time_stamp>@now
89 1
90 drop table t1;
91 End of 4.1 tests
92 create table t1 (
93 a int primary key,
94 b char(0),
95 c char(1)
96 )engine NDB;
97 #test for insertion in char(0) column
98 insert into t1 values(1, '', 'a'), (2, NULL, 'b');
99 #testing values stored in the table t1
100 select * from t1 order by a;
101 a b c
102 1 a
103 2 NULL b
104 select count(*) from t1 where b = '';
105 count(*)
106 1
107 select count(*) from t1 where b is null;
108 count(*)
109 1
110 drop table t1;
111 #testing bit(1) with char(0)
112 create table t1 (
113 a int primary key,
114 b char(0),
115 c bit(1)
116 )engine NDB;
117 #testing insertion
118 insert into t1 values(1, '', 0),
119 (2, '', 1),
120 (3, '', NULL),
121 (4, NULL, 0),
122 (5, NULL, 1),
123 (6, NULL, NULL);
124 #verify data in table
125 select a,b,bin(c) from t1 where b = '' and c is null order by a;
126 a b bin(c)
127 3 NULL
128 select a,b,bin(c) from t1 where b is null and c is null order by a;
129 a b bin(c)
130 6 NULL NULL
131 select a,b,bin(c) from t1 where b = '' and c = 0 order by a;
132 a b bin(c)
133 1 0
134 select a,b,bin(c) from t1 where b is null and c = 0 order by a;
135 a b bin(c)
136 4 NULL 0
137 select a,b,bin(c) from t1 where b = '' and c = 1 order by a;
138 a b bin(c)
139 2 1
140 select a,b,bin(c) from t1 where b is null and c = 1 order by a;
141 a b bin(c)
142 5 NULL 1
143 drop table t1;
144 #testing char(0) not null
145 create table t1 (
146 a int primary key,
147 b char(0) not null,
148 c char(1)
149 )engine NDB;
150 insert into t1 values(1, '', 'a'), (2, '', 'b');
151 #testing values stored in the table t1
152 select * from t1 order by a;
153 a b c
154 1 a
155 2 b
156 select count(*) from t1 where b = '';
157 count(*)
158 2
159 select count(*) from t1 where b is null;
160 count(*)
161 0
162 drop table t1;
163 Warnings:
164 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
165 create table t1 (
166 a int primary key,
167 b timestamp not null default '0000-00-00 00:00:00',
168 c timestamp not null,
169 d timestamp NULL
170 ) engine = NDB;
171 insert into t1 (a,c) values (37, '0000-00-00 00:00:00');
172 select b,c,d from t1;
173 b c d
174 0000-00-00 00:00:00 0000-00-00 00:00:00 NULL
175 drop table t1;