Debian Codegraph @

mysql-test/suite/funcs_1/views/func_view.inc

raw
1 ###################################################
2 # #
3 # Functions within VIEWs #
4 # #
5 ###################################################
6 # 2007-11-09 HHunger enabled all disabled parts belonging to fixed bugs.
7 # 2006-12-08 mleich Maintenance + refinements
8 # 2005-09-14 mleich Create this test
9 #
10 # 0. Some notes about this test:
11 # #################################################################
12 #
13 # 0.1 This test is unfinished and incomplete, but already useful.
14 # -----------------------------------------------------------------
15 # 0.1.1 There will be architectural changes in future.
16 # The long sequences with
17 # let $col_type= <column to use>;
18 # --source suite/funcs_1/views/<file containing the
19 # select with function>
20 # per every column type do not look very smart.
21 #
22 # Ugly combinations of functions and data types must be also checked,
23 # because an accidental typo like assigning a string column to an
24 # numeric parameter could happen and should not result in a server crash.
25 #
26 # Maybe it is better to change the architecture of this test in such
27 # a way:
28 # 1. A generator script (this one or written in Perl or SP language)
29 # generates an prototype of the the final testscript.
30 # 2. Some manual adjustments because of open bugs (depending on
31 # storage engine or function) might be needed (I hope not :)
32 # 3. The final testscript is pushed to the other regression testscripts.
33 # Advantage: The analysis of bugs, extension and maintenance of this
34 # test will be much easier.
35 # Disadvantage: Much redundant code within the final testscript,
36 # but the maintenance of the redundant code will be done
37 # by the script generator.
38 #
39 # 0.1.2 The behaviour of SELECTs on VIEWs could be affected by the SQL mode
40 # which was valid during VIEW creation time. This means some variations
41 # of the SQL mode are needed.
42 # 0.1.3 There are much more functions to be tested.
43 # 0.1.4 The result sets of some CAST sub testcases with ugly function parameter
44 # column data type combinations must be discussed.
45 #
46 #
47 # 0.2 How to valuate the test results:
48 # ---------------------------------------------------------------------------
49 # Due to the extreme "greedy bug hunting" architecture (combinatorics
50 # + heavy use of sourced scripts) of the following tests, there will be
51 # - no abort of the test execution, when one statements gets an return
52 # code != 0 (The sub testcases are independend.)
53 # But statements, which do not make sense like SELECT on non existent
54 # VIEW will be omitted. This decreases the amount of useless output.
55 # - a file with expected results, which might contain incorrect server
56 # responses
57 # There are open bugs and I cannot omit statements which reveal these
58 # bugs.
59 # But there will be a special messages within the protocol files.
60 # Example:
61 # "Attention: CAST --> SIGNED INTEGER
62 # The file with expected results suffers from Bug 5913";
63 # means, the file with expected results contains result sets which
64 # are known to be wrong.
65 # "Attention: The last <whatever> failed"
66 # means, a statement which should be successful (bugfree MySQL)
67 # failed.
68 #
69 # "Passed" : The behaviour of your MySQL version does not differ from the
70 # version used to generate the files with expected results.
71 # Known bugs affecting these tests could be retrieved by
72 # grep "Attention" r/<testcase>.result .
73 #
74 # "Failed" : The behaviour of your MySQL version differs from the version
75 # used to generate the files with expected results.
76 # These differences could be result of bug fixes or new bugs.
77 # Please compare r/<testcase>.reject and r/<testcase>.result .
78 #
79 # The test will abort if one of the basic preparation statement fails
80 # (except ALTER TABLE ADD ...).
81 #
82 #
83 # 0.3 How to debug sub testcases with "unexpected" results:
84 # ---------------------------------------------------------------------------
85 # 1. Please execute this test and copy the "reject" file to a save place.
86 # Search within the "reject" file for the sub testcase (the SELECT)
87 # with the suspicious result set or server response.
88 # Now all t1_values records are preloaded.
89 # 2. Start the server without the initial cleanup of databases etc.
90 # This preserves the content of the table t1_values, which
91 # might be needed for replaying the situation.
92 # Example:
93 # ./mysql-test-run.pl --socket=var/tmp/master.sock --start-dirty
94 # 3. Issue the statements needed by using "mysql" or "mysqltest".
95 #
96 # Maybe an internal routine of this test fails. Please ask me (mleich) or
97 # enable the logging of auxiliary queries and try to analyze the
98 # problem.
99 #
100 #
101 # 0.4 How to extend the number of functions to be checked:
102 # ---------------------------------------------------------------------------
103 # Please jump to the paragraphs of the basic preparations
104 # 1. Extend t1_values with the columns you need
105 # 2. Insert some predefinded rows
106 # 3. Add the SELECTs with function which should be used within VIEWs
107 # and
108 # records which should be used dedicated to the SELECT above
109 #
110 #
111 # 0.5 How to alter the internal routines of this test:
112 # ---------------------------------------------------------------------------
113 # Please try to achieve a state where the protocol
114 # - contains ALL statements, which are needed to replay a problem within
115 # the field of functions within VIEWs
116 # - does not contain too much auxiliary statements, which are not needed
117 # to replay a problem (--> "--disable_query_log")
118 # Example:
119 # Needed for replay:
120 # - DROP/CREATE TABLE t1_values
121 # - INSERT of records into t1_values
122 # - DROP/CREATE/SELECT/SHOW VIEW v1
123 # - SELECT direct on base table
124 # Not needed for replay:
125 # - SET @<uservariable> = <value>
126 # - DROP/CREATE/INSERT/SELECT TABLE t1_selects, t1_modes
127 #
128 #
129 # 0.6 A trick for checking results
130 # ---------------------------------------------------------------------------
131 # Standard setting for common execution of this test:
132 let $simple_select_result= 1;
133 let $view_select_result= 1;
134 # The implementation of some additional function tests may lead to
135 # masses of result sets, which have to be checked. The result sets of
136 # the simple selects on the base table must equal the result sets of the
137 # queries on the VIEWs. This step could be made more comfortable by
138 # 1. Edit this file to
139 # let $simple_select_result= 1;
140 # let $view_select_result= 0;
141 # Please execute this test.
142 # The script will omit CREATE/DROP/SHOW/SELECT on VIEW.
143 # The "reject" file contains only the simple select result sets.
144 # 2. Edit this file to
145 # let $simple_select_result= 0;
146 # let $view_select_result= 1;
147 # Please execute this test.
148 # The script will work with the VIEWs, but omit the simple selects.
149 # The "reject" file contains the view select result sets.
150 # 3. Compare the "reject" files of 1. and 2. within a graphical diff tool.
151 #
152 #
153
154 SET timestamp=unix_timestamp('2001-02-03 10:20:30');
155
156 --disable_warnings
157 DROP TABLE IF EXISTS t1_selects, t1_modes, t1_values;
158 DROP VIEW IF EXISTS v1;
159 --enable_warnings
160
161 --disable_query_log
162 # Storage for the SELECTs to be used for the VIEW definition
163 # Attention: my_select must be no too small because a statement like
164 # SELECT LOAD_FILE(< file in MYSQLTEST_VARDIR >)
165 # AS my_col,
166 # id FROM t1_values';
167 # might be a very long
168 # Bug#38427 "Data too long" ... tests "<ENGINE>_func_view" fail
169 CREATE TABLE t1_selects
170 (
171 id BIGINT AUTO_INCREMENT,
172 my_select VARCHAR(1000) NOT NULL,
173 disable_result ENUM('Yes','No') NOT NULL default 'No',
174 PRIMARY KEY(id),
175 UNIQUE (my_select)
176 ) ENGINE=MyISAM;
177
178 # MODES to be checked
179 CREATE TABLE t1_modes
180 (
181 id BIGINT AUTO_INCREMENT,
182 my_mode VARCHAR(200) NOT NULL,
183 PRIMARY KEY(id),
184 UNIQUE (my_mode)
185 ) ENGINE=MyISAM;
186 --enable_query_log
187
188 # The table to be used in the FROM parts of the SELECTs
189 --replace_result $type <engine_to_be_tested>
190 eval CREATE TABLE t1_values
191 (
192 id BIGINT AUTO_INCREMENT,
193 select_id BIGINT,
194 PRIMARY KEY(id)
195 ) ENGINE = $type;
196
197 ##### BEGIN Basic preparations #######################################
198 #
199 # 1. Extend t1_values with the columns you need
200 # - the column name must show the data type
201 # - do not add NOT NULL columns
202 # - do not worry if the intended column data type is not
203 # available for some storage engines
204 # Please do not forget to assign values for the new columns (paragraph 2.).
205 --disable_abort_on_error
206 ALTER TABLE t1_values ADD my_char_30 CHAR(30);
207 ALTER TABLE t1_values ADD my_varchar_1000 VARCHAR(1000);
208 ALTER TABLE t1_values ADD my_binary_30 BINARY(30);
209 ALTER TABLE t1_values ADD my_varbinary_1000 VARBINARY(1000);
210 ALTER TABLE t1_values ADD my_datetime DATETIME;
211 ALTER TABLE t1_values ADD my_date DATE;
212 ALTER TABLE t1_values ADD ts_dummy TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
213 ALTER TABLE t1_values ADD my_timestamp TIMESTAMP NOT NULL DEFAULT '2001-01-01 10:20:30';
214 ALTER TABLE t1_values ADD my_time TIME;
215 ALTER TABLE t1_values ADD my_year YEAR;
216 ALTER TABLE t1_values ADD my_bigint BIGINT;
217 ALTER TABLE t1_values ADD my_double DOUBLE;
218 ALTER TABLE t1_values ADD my_decimal DECIMAL(64,30);
219 --enable_abort_on_error
220
221 #-------------------------------------------------------------------------------
222
223 #
224 # 2. Insert some predefinded rows
225 # Predefined rows
226 # - t1_values.select_id IS NULL
227 # - will be selected by every SELECT with function to be tested
228 # - have to be inserted when sql_mode = 'traditional' is valid, because
229 # we do not want to start with "illegal/unexpected/..." values.
230 # Such experiments should be done in other testcases.
231 # Please be careful
232 # - modifying column values of predefined rows they might change many
233 # result sets
234 # - additional predefined rows should be really useful for the majority of
235 # all sub testcases, since they blow up all result sets.
236 SET sql_mode = 'traditional';
237 #
238 # 2.1 record -- everything to NULL
239 INSERT INTO t1_values SET id = 0;
240 #
241 # 2.2 record -- everything to "minimum"
242 # numbers, date/time types -> minimum of range
243 # strings, blobs, binaries -> ''
244 # FIXME enum, set ??
245 INSERT INTO t1_values SET
246 my_char_30 = '',
247 my_varchar_1000 = '',
248 my_binary_30 = '',
249 my_varbinary_1000 = '',
250 my_datetime = '0001-01-01 00:00:00',
251 my_date = '0001-01-01',
252 my_timestamp = '1970-01-01 14:00:01',
253 my_time = '-838:59:59',
254 my_year = '1901',
255 my_bigint = -9223372036854775808,
256 my_decimal = -9999999999999999999999999999999999.999999999999999999999999999999 ,
257 my_double = -1.7976931348623E+308;
258 # shortened due to bug#32285
259 # my_double = -1.7976931348623157E+308;
260 #
261 # 2.3 record -- everything to "maximum"
262 # numbers, date/time types -> maximum of range
263 # strings, blobs, binaries -> '<- full length of used data type>'
264 # FIXME enum, set ??
265 INSERT INTO t1_values SET
266 my_char_30 = '<--------30 characters------->',
267 my_varchar_1000 = CONCAT('<---------1000 characters',
268 RPAD('',965,'-'),'--------->'),
269 my_binary_30 = '<--------30 characters------->',
270 my_varbinary_1000 = CONCAT('<---------1000 characters',
271 RPAD('',965,'-'),'--------->'),
272 my_datetime = '9999-12-31 23:59:59',
273 my_date = '9999-12-31',
274 my_timestamp = '2038-01-01 02:59:59',
275 my_time = '838:59:59',
276 my_year = 2155,
277 my_bigint = 9223372036854775807,
278 my_decimal = +9999999999999999999999999999999999.999999999999999999999999999999 ,
279 my_double = 1.7976931348623E+308;
280 # shortened due to bug#32285
281 # my_double = -1.7976931348623157E+308;
282 #
283 # 2.4 record -- everything to "magic" value if available or
284 # other interesting value
285 # numbers -> 0
286 # strings, blobs, binaries -> not full length of used data type, "exotic"
287 # characters and preceding and trailing spaces
288 # FIXME enum, set ??
289 INSERT INTO t1_values SET
290 my_char_30 = ' ---äÖüß@µ*$-- ',
291 my_varchar_1000 = ' ---äÖüß@µ*$-- ',
292 my_binary_30 = ' ---äÖüß@µ*$-- ',
293 my_varbinary_1000 = ' ---äÖüß@µ*$-- ',
294 my_datetime = '2004-02-29 23:59:59',
295 my_date = '2004-02-29',
296 my_timestamp = '2004-02-29 23:59:59',
297 my_time = '13:00:00',
298 my_year = 2000,
299 my_bigint = 0,
300 my_decimal = 0.0,
301 my_double = 0;
302 #
303 # 2.5 record -- everything to "harmless" value if available
304 # numbers -> -1 (logical)
305 # strings, blobs, binaries -> '-1' useful for numeric functions
306 # FIXME enum, set ??
307 INSERT INTO t1_values SET
308 my_char_30 = '-1',
309 my_varchar_1000 = '-1',
310 my_binary_30 = '-1',
311 my_varbinary_1000 = '-1',
312 my_datetime = '2005-06-28 10:00:00',
313 my_date = '2005-06-28',
314 my_timestamp = '2005-06-28 10:00:00',
315 my_time = '10:00:00',
316 my_year = 2005,
317 my_bigint = -1,
318 my_decimal = -1.000000000000000000000000000000,
319 my_double = -0.1E+1;
320
321 #-------------------------------------------------------------------------------
322
323 #
324 # 3. Add the SELECTs with function which should be used within VIEWs
325 # and
326 # records which should be used dedicated to the SELECT above
327 # - Please avoid WHERE clauses
328 # - Include the PRIMARY KEY ("id") of the base table t1_values into the
329 # select column list
330 # - Include the base table column used as function parameter into the
331 # select column list, because it is much easier to check the results
332 # - Do not forget to escape single quotes
333 # Example:
334 # SET @my_select = 'SELECT sqrt(my_bigint), my_bigint, id FROM t1_values'
335 # SET @my_select = 'SELECT CONCAT(\'A\',my_char_30), id FROM t1_values';
336 # - Statements, which reveal open crashing bugs MUST be disabled.
337 # - Result sets must not contain data, which might differ between boxes
338 # executing this test.
339 # Example: current time, absolute path to some files ...
340 # - Please derive the functions to be checked from the MySQL manual
341 # and use the same order. This means copy the the function names as
342 # comment into this test and start to implement a testcase for your
343 # most preferred function.
344 # This method avoids that we forget a function and gives a better
345 # overview.
346 #
347 # If you have the time to check the result sets do the insert of the
348 # SELECT with function via:
349 # eval SET @my_select =
350 # '<your SELECT>';
351 # --source suite/funcs_1/views/fv1.inc
352 # fv1.inc sets t1_selects.disable_result to 'No' and the effect will be,
353 # that the result set will be logged.
354 #
355 # If you do not have the time to check the result sets do the insert of the
356 # SELECT with function via:
357 # eval SET @my_select =
358 # '<your SELECT>';
359 # --source suite/funcs_1/views/fv2.inc
360 # fv2.inc sets t1_selects.disable_result to 'Yes' and the effect will be,
361 # that the result set will be not logged.
362 # This should be only a temporary solution and it does not remove the
363 # need to check the server return codes.
364 # That means even when we do not have the time to check the correctness of
365 # the result sets, we check if
366 # - SELECT <function> or
367 # - SELECT * FROM <VIEW with function>
368 # crash the server or get suspicious server responses.
369 #
370 # - the SELECTs will be applied to the rows defined here (3.) +
371 # all predefined rows (2.)
372 # - the rows dedicated to the SELECT should contain especially interesting
373 # column values or combinations of column values, which are not covered
374 # by the predefined records
375 # - The records have to be inserted when sql_mode = 'traditional' is valid.
376 # - Please do not insert records with column values where the allowed
377 # range is exceeded. The SQL mode 'traditional' will prevent such
378 # inserts. Such experiments should be done in other tests, because
379 # they inflate the current test without giving an appropriate value.
380 #
381 # Example:
382 # The function to be tested is "sqrt".
383 # The minimum, maximum, default and NULL value are covered by the
384 # predefined rows.
385 # A value where sqrt(<value>) = <integer value> in strict mathematics
386 # would be of interest.
387 # --> Add a record with my_bigint = 4
388 # --> Just for fun my_bigint = -25 .
389 #
390 # Some internal stuff
391 PREPARE ins_sel_with_result FROM "INSERT INTO t1_selects SET my_select = @my_select,
392 disable_result = 'No'" ;
393 PREPARE ins_sel_no_result FROM "INSERT INTO t1_selects SET my_select = @my_select,
394 disable_result = 'Yes'" ;
395 SET sql_mode = 'traditional';
396 # --disable_query_log
397
398 let $col_type= my_bigint;
399 # Example:
400 # eval SET @my_select = 'SELECT CONCAT(''A'', $col_type), $col_type, id';
401 eval SET @my_select = 'SELECT sqrt($col_type), $col_type, id FROM t1_values';
402 --source suite/funcs_1/views/fv1.inc
403 # Content of suite/funcs_1/views/fv1.inc :
404 # --disable_query_log
405 # EXECUTE ins_sel_with_result;
406 # SET @select_id = LAST_INSERT_ID();
407 # --enable_query_log
408
409 eval INSERT INTO t1_values SET select_id = @select_id,
410 $col_type = 4;
411 eval INSERT INTO t1_values SET select_id = @select_id,
412 $col_type = -25;
413 # SELECT * FROM t1_values;
414
415 # 1. Cast Functions and Operators
416 # 1.1 CAST
417 #
418 # Note(mleich): I guess the CAST routines are used in many other functions.
419 # Therefore check also nearly all "ugly" variants like
420 # CAST(<string composed of non digits> AS DECIMAL) here.
421 #
422 # suite/funcs_1/views/fv_cast.inc contains
423 # SELECT CAST($col_type AS $target_type), ...
424 #
425 #
426 # 1.1.1. CAST --> BINARY
427 --echo ##### 1.1.1. CAST --> BINARY
428 let $target_type= BINARY;
429 #
430 let $col_type= my_char_30;
431 --source suite/funcs_1/views/fv_cast.inc
432 let $col_type= my_varchar_1000;
433 --source suite/funcs_1/views/fv_cast.inc
434 let $col_type= my_binary_30;
435 --source suite/funcs_1/views/fv_cast.inc
436 let $col_type= my_varbinary_1000;
437 --source suite/funcs_1/views/fv_cast.inc
438 let $col_type= my_bigint;
439 --source suite/funcs_1/views/fv_cast.inc
440 let $col_type= my_decimal;
441 --source suite/funcs_1/views/fv_cast.inc
442 let $col_type= my_double;
443 --source suite/funcs_1/views/fv_cast.inc
444 let $col_type= my_datetime;
445 --source suite/funcs_1/views/fv_cast.inc
446 let $col_type= my_date;
447 --source suite/funcs_1/views/fv_cast.inc
448 let $col_type= my_timestamp;
449 --source suite/funcs_1/views/fv_cast.inc
450 let $col_type= my_time;
451 --source suite/funcs_1/views/fv_cast.inc
452 let $col_type= my_year;
453 --source suite/funcs_1/views/fv_cast.inc
454
455
456 # 1.1.2. CAST --> CHAR
457 --echo ##### 1.1.2. CAST --> CHAR
458 let $target_type= CHAR;
459 #
460 let $col_type= my_char_30;
461 --source suite/funcs_1/views/fv_cast.inc
462 let $col_type= my_varchar_1000;
463 --source suite/funcs_1/views/fv_cast.inc
464 let $col_type= my_binary_30;
465 --source suite/funcs_1/views/fv_cast.inc
466 let $col_type= my_varbinary_1000;
467 --source suite/funcs_1/views/fv_cast.inc
468 let $col_type= my_bigint;
469 --source suite/funcs_1/views/fv_cast.inc
470 let $col_type= my_decimal;
471 --source suite/funcs_1/views/fv_cast.inc
472 let $col_type= my_double;
473 --source suite/funcs_1/views/fv_cast.inc
474 let $col_type= my_datetime;
475 --source suite/funcs_1/views/fv_cast.inc
476 let $col_type= my_date;
477 --source suite/funcs_1/views/fv_cast.inc
478 let $col_type= my_timestamp;
479 --source suite/funcs_1/views/fv_cast.inc
480 let $col_type= my_time;
481 --source suite/funcs_1/views/fv_cast.inc
482 let $col_type= my_year;
483 --source suite/funcs_1/views/fv_cast.inc
484
485
486 # 1.1.3. CAST --> DATE
487 --echo ##### 1.1.3. CAST --> DATE
488 let $target_type= DATE;
489 #
490 let $col_type= my_char_30;
491 --source suite/funcs_1/views/fv_cast.inc
492 eval INSERT INTO t1_values SET select_id = @select_id,
493 $col_type = '2005-06-27';
494 let $col_type= my_varchar_1000;
495 --source suite/funcs_1/views/fv_cast.inc
496 eval INSERT INTO t1_values SET select_id = @select_id,
497 $col_type = '2005-06-27';
498 let $col_type= my_binary_30;
499 --source suite/funcs_1/views/fv_cast.inc
500 eval INSERT INTO t1_values SET select_id = @select_id,
501 $col_type = '2005-06-27';
502 let $col_type= my_varbinary_1000;
503 --source suite/funcs_1/views/fv_cast.inc
504 eval INSERT INTO t1_values SET select_id = @select_id,
505 $col_type = '2005-06-27';
506 let $col_type= my_bigint;
507 --source suite/funcs_1/views/fv_cast.inc
508 eval INSERT INTO t1_values SET select_id = @select_id,
509 $col_type = 20050627;
510 let $col_type= my_double;
511 --source suite/funcs_1/views/fv_cast.inc
512 eval INSERT INTO t1_values SET select_id = @select_id,
513 $col_type = +20.050627E+6;
514 let $col_type= my_datetime;
515 --source suite/funcs_1/views/fv_cast.inc
516 let $col_type= my_date;
517 --source suite/funcs_1/views/fv_cast.inc
518 let $col_type= my_timestamp;
519 --source suite/funcs_1/views/fv_cast.inc
520 let $col_type= my_time;
521 --source suite/funcs_1/views/fv_cast.inc
522 let $col_type= my_year;
523 --source suite/funcs_1/views/fv_cast.inc
524
525
526 # 1.1.4. CAST --> DATETIME
527 --echo ##### 1.1.4. CAST --> DATETIME
528 let $target_type= DATETIME;
529 #
530 let $col_type= my_char_30;
531 --source suite/funcs_1/views/fv_cast.inc
532 eval INSERT INTO t1_values SET select_id = @select_id,
533 $col_type = '2005-06-27 17:58';
534 let $col_type= my_varchar_1000;
535 --source suite/funcs_1/views/fv_cast.inc
536 eval INSERT INTO t1_values SET select_id = @select_id,
537 $col_type = '2005-06-27 17:58';
538 let $col_type= my_binary_30;
539 --source suite/funcs_1/views/fv_cast.inc
540 eval INSERT INTO t1_values SET select_id = @select_id,
541 $col_type = '2005-06-27 17:58';
542 let $col_type= my_varbinary_1000;
543 --source suite/funcs_1/views/fv_cast.inc
544 eval INSERT INTO t1_values SET select_id = @select_id,
545 $col_type = '2005-06-27 17:58';
546 let $col_type= my_bigint;
547 --source suite/funcs_1/views/fv_cast.inc
548 eval INSERT INTO t1_values SET select_id = @select_id,
549 $col_type = 200506271758;
550 let $col_type= my_double;
551 --source suite/funcs_1/views/fv_cast.inc
552 eval INSERT INTO t1_values SET select_id = @select_id,
553 $col_type = +0.0200506271758E+13;
554 let $col_type= my_datetime;
555 --source suite/funcs_1/views/fv_cast.inc
556 let $col_type= my_date;
557 --source suite/funcs_1/views/fv_cast.inc
558 let $col_type= my_timestamp;
559 --source suite/funcs_1/views/fv_cast.inc
560 let $col_type= my_time;
561 --source suite/funcs_1/views/fv_cast.inc
562 let $col_type= my_year;
563 --source suite/funcs_1/views/fv_cast.inc
564
565
566 # 1.1.5. CAST --> TIME
567 --echo ##### 1.1.5. CAST --> TIME
568 let $target_type= TIME;
569 #
570 let $col_type= my_char_30;
571 --source suite/funcs_1/views/fv_cast.inc
572 eval INSERT INTO t1_values SET select_id = @select_id,
573 $col_type = '1 17:58';
574 let $col_type= my_varchar_1000;
575 --source suite/funcs_1/views/fv_cast.inc
576 eval INSERT INTO t1_values SET select_id = @select_id,
577 $col_type = '1 17:58';
578 let $col_type= my_binary_30;
579 --source suite/funcs_1/views/fv_cast.inc
580 eval INSERT INTO t1_values SET select_id = @select_id,
581 $col_type = '1 17:58';
582 let $col_type= my_varbinary_1000;
583 --source suite/funcs_1/views/fv_cast.inc
584 eval INSERT INTO t1_values SET select_id = @select_id,
585 $col_type = '1 17:58';
586 let $col_type= my_bigint;
587 --source suite/funcs_1/views/fv_cast.inc
588 eval INSERT INTO t1_values SET select_id = @select_id,
589 $col_type = 1758;
590 let $col_type= my_double;
591 # Bug#12440: CAST(data type DOUBLE AS TIME) strange results;
592 --source suite/funcs_1/views/fv_cast.inc
593 eval INSERT INTO t1_values SET select_id = @select_id,
594 $col_type = +1.758E+3;
595 let $col_type= my_datetime;
596 --source suite/funcs_1/views/fv_cast.inc
597 let $col_type= my_date;
598 --source suite/funcs_1/views/fv_cast.inc
599 let $col_type= my_timestamp;
600 --source suite/funcs_1/views/fv_cast.inc
601 let $col_type= my_time;
602 --source suite/funcs_1/views/fv_cast.inc
603 let $col_type= my_year;
604 --source suite/funcs_1/views/fv_cast.inc
605
606
607 # 1.1.6. CAST --> DECIMAL
608 --echo ##### 1.1.6. CAST --> DECIMAL
609 # Set the following to (37,2) since the default was changed to (10,0) - OBN
610 let $target_type= DECIMAL(37,2);
611 #
612 let $col_type= my_char_30;
613 --source suite/funcs_1/views/fv_cast.inc
614 eval INSERT INTO t1_values SET select_id = @select_id,
615 $col_type = '-3333.3333';
616 let $col_type= my_varchar_1000;
617 --source suite/funcs_1/views/fv_cast.inc
618 eval INSERT INTO t1_values SET select_id = @select_id,
619 $col_type = '-3333.3333';
620 let $col_type= my_binary_30;
621 --source suite/funcs_1/views/fv_cast.inc
622 eval INSERT INTO t1_values SET select_id = @select_id,
623 $col_type = '-3333.3333';
624 let $col_type= my_varbinary_1000;
625 --source suite/funcs_1/views/fv_cast.inc
626 eval INSERT INTO t1_values SET select_id = @select_id,
627 $col_type = '-3333.3333';
628 let $col_type= my_bigint;
629 --source suite/funcs_1/views/fv_cast.inc
630 let $col_type= my_decimal;
631 --source suite/funcs_1/views/fv_cast.inc
632 # Bug#13349: CAST(1.0E+300 TO DECIMAL) returns wrong result + diff little/big endian;
633 let $col_type= my_double;
634 --source suite/funcs_1/views/fv_cast.inc
635 eval INSERT INTO t1_values SET select_id = @select_id,
636 $col_type = -0.33333333E+4;
637 let $col_type= my_datetime;
638 --source suite/funcs_1/views/fv_cast.inc
639 let $col_type= my_date;
640 --source suite/funcs_1/views/fv_cast.inc
641 let $col_type= my_timestamp;
642 --source suite/funcs_1/views/fv_cast.inc
643 let $col_type= my_time;
644 --source suite/funcs_1/views/fv_cast.inc
645 let $col_type= my_year;
646 --source suite/funcs_1/views/fv_cast.inc
647
648
649 # 1.1.7. CAST --> SIGNED INTEGER
650 --echo ##### 1.1.7. CAST --> SIGNED INTEGER
651 let $target_type= SIGNED INTEGER;
652 #
653 let $message=
654 "Attention: CAST --> SIGNED INTEGER
655 Bug#5913 Traditional mode: BIGINT range not correctly delimited
656 Status: To be fixed later";
657 --source include/show_msg80.inc
658 let $col_type= my_char_30;
659 --source suite/funcs_1/views/fv_cast.inc
660 let $col_type= my_varchar_1000;
661 --source suite/funcs_1/views/fv_cast.inc
662 let $col_type= my_binary_30;
663 --source suite/funcs_1/views/fv_cast.inc
664 let $col_type= my_varbinary_1000;
665 --source suite/funcs_1/views/fv_cast.inc
666 let $col_type= my_bigint;
667 --source suite/funcs_1/views/fv_cast.inc
668 let $col_type= my_decimal;
669 --source suite/funcs_1/views/fv_cast.inc
670 # Bug #13344: CAST(1E+300 TO signed int) on little endian CPU, wrong result;
671 let $col_type= my_double;
672 --source suite/funcs_1/views/fv_cast.inc
673 let $col_type= my_datetime;
674 --source suite/funcs_1/views/fv_cast.inc
675 let $col_type= my_date;
676 --source suite/funcs_1/views/fv_cast.inc
677 let $col_type= my_timestamp;
678 --source suite/funcs_1/views/fv_cast.inc
679 let $col_type= my_time;
680 --source suite/funcs_1/views/fv_cast.inc
681 let $col_type= my_year;
682 --source suite/funcs_1/views/fv_cast.inc
683
684
685 # 1.1.8. CAST --> UNSIGNED INTEGER
686 --echo ##### 1.1.8. CAST --> UNSIGNED INTEGER
687 let $target_type= UNSIGNED INTEGER;
688 #
689 let $message=
690 "Attention: CAST --> UNSIGNED INTEGER
691 The file with expected results suffers from Bug 5913";
692 --source include/show_msg80.inc
693 let $col_type= my_char_30;
694 --source suite/funcs_1/views/fv_cast.inc
695 let $col_type= my_varchar_1000;
696 --source suite/funcs_1/views/fv_cast.inc
697 let $col_type= my_binary_30;
698 --source suite/funcs_1/views/fv_cast.inc
699 let $col_type= my_varbinary_1000;
700 --source suite/funcs_1/views/fv_cast.inc
701 let $col_type= my_bigint;
702 --source suite/funcs_1/views/fv_cast.inc
703 let $col_type= my_decimal;
704 --source suite/funcs_1/views/fv_cast.inc
705 let $message= some statements disabled because of
706 Bug#5913 Traditional mode: BIGINT range not correctly delimited;
707 --source include/show_msg80.inc
708 # Bug#8663 cant use bgint unsigned as input to cast
709 let $col_type= my_double;
710 --source suite/funcs_1/views/fv_cast.inc
711 let $col_type= my_datetime;
712 --source suite/funcs_1/views/fv_cast.inc
713 let $col_type= my_date;
714 --source suite/funcs_1/views/fv_cast.inc
715 let $col_type= my_timestamp;
716 --source suite/funcs_1/views/fv_cast.inc
717 let $col_type= my_time;
718 --source suite/funcs_1/views/fv_cast.inc
719 let $col_type= my_year;
720 --source suite/funcs_1/views/fv_cast.inc
721
722
723 # 1.2. BINARY
724 # Manual: BINARY str is a shorthand for CAST(str AS BINARY).
725 # Therefore we do not test it here in the moment.
726 # FIXME: Add testcases for str in CHAR and VARCHAR only.
727
728
729 # 1.3 CONVERT(expr USING transcoding_name)
730 #
731 # 1.3.1 CONVERT(expr USING utf8)
732 let $target_charset= utf8;
733 #
734 let $col_type= my_char_30;
735 eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
736 $col_type, id FROM t1_values';
737 --source suite/funcs_1/views/fv1.inc
738 let $col_type= my_varchar_1000;
739 eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
740 $col_type, id FROM t1_values';
741 --source suite/funcs_1/views/fv1.inc
742 let $col_type= my_binary_30;
743 eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
744 $col_type, id FROM t1_values';
745 --source suite/funcs_1/views/fv1.inc
746 let $col_type= my_varbinary_1000;
747 eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
748 $col_type, id FROM t1_values';
749 --source suite/funcs_1/views/fv1.inc
750 #
751 # 1.3.2 CONVERT(expr USING koi8r)
752 let $target_charset= koi8r;
753 let $col_type= my_char_30;
754 eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
755 $col_type, id FROM t1_values';
756 --source suite/funcs_1/views/fv1.inc
757 let $col_type= my_varchar_1000;
758 eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
759 $col_type, id FROM t1_values';
760 --source suite/funcs_1/views/fv1.inc
761 let $col_type= my_binary_30;
762 eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
763 $col_type, id FROM t1_values';
764 --source suite/funcs_1/views/fv1.inc
765 let $col_type= my_varbinary_1000;
766 eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
767 $col_type, id FROM t1_values';
768 --source suite/funcs_1/views/fv1.inc
769
770
771 # 2. Control Flow Functions
772 # 2.1. CASE value WHEN [compare-value] THEN result [WHEN ...] [ELSE result]
773 # END or
774 # CASE WHEN [condition] THEN result [WHEN ...] [ELSE result] END
775 #
776 # FIXME: to be implemented
777 #
778 # 2.2. IF(expr1,expr2,expr3)
779 # expr1 is TRUE when (expr1 <> 0 and expr1 <> NULL) is fulfilled
780 #
781 # 2.2.1 IF(expr1,expr2,expr3) with expr1 = <column>
782 #
783 # Note(mleich): Strings, which do not contain a number -> FALSE
784 #
785 # suite/funcs_1/views/fv_if1.inc contains
786 # SELECT IF($col_type, 'IS TRUE', 'IS NOT TRUE'), ...
787 #
788 let $col_type= my_char_30;
789 --source suite/funcs_1/views/fv_if1.inc
790 #
791 let $col_type= my_varchar_1000;
792 --source suite/funcs_1/views/fv_if1.inc
793 #
794 let $col_type= my_binary_30;
795 --source suite/funcs_1/views/fv_if1.inc
796 #
797 let $col_type= my_varbinary_1000;
798 --source suite/funcs_1/views/fv_if1.inc
799 #
800 let $col_type= my_bigint;
801 --source suite/funcs_1/views/fv_if1.inc
802 #
803 let $col_type= my_decimal;
804 --source suite/funcs_1/views/fv_if1.inc
805 #
806 let $col_type= my_double;
807 --source suite/funcs_1/views/fv_if1.inc
808 #
809 let $col_type= my_datetime;
810 --source suite/funcs_1/views/fv_if1.inc
811 #
812 let $col_type= my_date;
813 --source suite/funcs_1/views/fv_if1.inc
814 #
815 let $col_type= my_timestamp;
816 --source suite/funcs_1/views/fv_if1.inc
817 #
818 let $col_type= my_time;
819 --source suite/funcs_1/views/fv_if1.inc
820 #
821 let $col_type= my_year;
822 --source suite/funcs_1/views/fv_if1.inc
823
824
825 # 2.2.2 IF(expr1,expr2,expr3) with expr1 != <column>
826 #
827 # suite/funcs_1/views/fv_if2.inc contains
828 # SELECT IF($col_type IS NULL, 'IS NULL', 'IS NOT NULL'), ...
829 #
830 # Note(mleich): July 2005
831 # IF($col_type IS NULL, ...) is mapped to a VIEW definition
832 # create ... view ... as
833 # select if(isnull(`test`.`t1`.`f1`),_latin1'IS NULL',
834 # _latin1'IS NOT NULL'),...
835 #
836 # Bug#11689 success on Create view .. IF(col1 IS NULL,...), col2 ; but SELECT fails
837 let $col_type= my_char_30;
838 --source suite/funcs_1/views/fv_if2.inc
839 #
840 let $col_type= my_varchar_1000;
841 --source suite/funcs_1/views/fv_if2.inc
842 #
843 let $col_type= my_binary_30;
844 --source suite/funcs_1/views/fv_if2.inc
845 #
846 let $col_type= my_varbinary_1000;
847 --source suite/funcs_1/views/fv_if2.inc
848 #
849 let $col_type= my_bigint;
850 --source suite/funcs_1/views/fv_if2.inc
851 #
852 let $col_type= my_decimal;
853 --source suite/funcs_1/views/fv_if2.inc
854 #
855 let $col_type= my_double;
856 --source suite/funcs_1/views/fv_if2.inc
857 #
858 let $col_type= my_datetime;
859 --source suite/funcs_1/views/fv_if2.inc
860 #
861 let $col_type= my_date;
862 --source suite/funcs_1/views/fv_if2.inc
863 #
864 let $col_type= my_timestamp;
865 --source suite/funcs_1/views/fv_if2.inc
866 #
867 let $col_type= my_time;
868 --source suite/funcs_1/views/fv_if2.inc
869 #
870 let $col_type= my_year;
871 --source suite/funcs_1/views/fv_if2.inc
872
873
874 # 2.3. IFNULL(expr1,expr2)
875 # If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2.
876 #
877 # suite/funcs_1/views/fv_ifnull.inc contains
878 # SELECT IFNULL($col_type, 'IS_NULL'), ....
879 # FIXME: The mixup of non string column values
880 # and the string 'IS NULL' within the first column of the
881 # result table is extreme ugly.
882 # CAST(IFNULL($col_type, 'IS_NULL') AS CHAR) looks better, but
883 # it has the disadvantage, that it involves CAST as additional
884 # function.
885 #
886 let $col_type= my_char_30;
887 --source suite/funcs_1/views/fv_ifnull.inc
888 #
889 let $col_type= my_varchar_1000;
890 --source suite/funcs_1/views/fv_ifnull.inc
891 #
892 let $col_type= my_binary_30;
893 --source suite/funcs_1/views/fv_ifnull.inc
894 #
895 let $col_type= my_varbinary_1000;
896 --source suite/funcs_1/views/fv_ifnull.inc
897 #
898 let $col_type= my_bigint;
899 --source suite/funcs_1/views/fv_ifnull.inc
900 #
901 let $col_type= my_decimal;
902 --source suite/funcs_1/views/fv_ifnull.inc
903 #
904 let $col_type= my_double;
905 --source suite/funcs_1/views/fv_ifnull.inc
906 #
907 let $col_type= my_datetime;
908 --source suite/funcs_1/views/fv_ifnull.inc
909 #
910 let $col_type= my_date;
911 --source suite/funcs_1/views/fv_ifnull.inc
912 #
913 let $col_type= my_timestamp;
914 --source suite/funcs_1/views/fv_ifnull.inc
915 #
916 let $col_type= my_time;
917 --source suite/funcs_1/views/fv_ifnull.inc
918 #
919 let $col_type= my_year;
920 --source suite/funcs_1/views/fv_ifnull.inc
921
922
923 # 2.4. NULLIF(expr1,expr2)
924 # Returns NULL if expr1 = expr2 is true, else returns expr1.
925 # This is the same as
926 # CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
927 #
928 # FIXME: to be implemented
929 #
930
931
932 # 3. String Functions
933 # 3.1. ASCII(str)
934 # 3.2. BIN(N)
935 # FIXME: to be implemented
936 #
937 # 3.3. BIT_LENGTH(str)
938 # Returns the length of the string str in bits.
939 #
940 let $col_type= my_char_30;
941 eval SET @my_select = 'SELECT BIT_LENGTH($col_type),
942 $col_type, id FROM t1_values';
943 --source suite/funcs_1/views/fv1.inc
944 let $col_type= my_varchar_1000;
945 eval SET @my_select = 'SELECT BIT_LENGTH($col_type),
946 $col_type, id FROM t1_values';
947 --source suite/funcs_1/views/fv1.inc
948 let $col_type= my_binary_30;
949 eval SET @my_select = 'SELECT BIT_LENGTH($col_type),
950 $col_type, id FROM t1_values';
951 --source suite/funcs_1/views/fv1.inc
952 let $col_type= my_varbinary_1000;
953 eval SET @my_select = 'SELECT BIT_LENGTH($col_type),
954 $col_type, id FROM t1_values';
955 --source suite/funcs_1/views/fv1.inc
956
957
958 # 3.4. CHAR(N,...)
959 # 3.5. CHAR_LENGTH(str)
960 # 3.6 CHARACTER_LENGTH(str)
961 # CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().
962 # 3.7. COMPRESS(string_to_compress)
963 # 3.8. CONCAT(str1,str2,...)
964 # 3.9. CONCAT_WS(separator,str1,str2,...)
965 # 3.10. CONV(N,from_base,to_base)
966 # 3.11. ELT(N,str1,str2,str3,...)
967 # 3.12. EXPORT_SET(bits,on,off[,separator[,number_of_bits]])
968 # 3.13. FIELD(str,str1,str2,str3,...)
969 # 3.14. FIND_IN_SET(str,strlist)
970 # 3.15. HEX(N_or_S
971 # 3.16. INSERT(str,pos,len,newstr)
972 # 3.17. INSTR(str,substr)
973 # This is the same as the two-argument form of LOCATE(),
974 # except that the arguments are swapped.
975 # The majority of the testcases should be made with LOCATE().
976 # Therefore test only one example here.
977 let $col_type= my_char_30;
978 eval SET @my_select = 'SELECT INSTR($col_type, ''char''),
979 $col_type, id FROM t1_values';
980 --source suite/funcs_1/views/fv2.inc
981
982
983 # 3.18. LCASE(str)
984 # LCASE() is a synonym for LOWER().
985 # The majority of the testcases should be made with LOWER().
986 # Therefore test only one example here.
987 let $col_type= my_varchar_1000;
988 eval SET @my_select = 'SELECT LCASE($col_type),
989 $col_type, id FROM t1_values';
990 --source suite/funcs_1/views/fv2.inc
991
992
993 # 3.19. LEFT(str,len)
994 # Returns the leftmost len characters from the string str.
995 let $col_type= my_char_30;
996 eval SET @my_select =
997 'SELECT LEFT($col_type, 2), $col_type, id FROM t1_values';
998 --source suite/funcs_1/views/fv1.inc
999 let $col_type= my_varchar_1000;
1000 eval SET @my_select =
1001 'SELECT LEFT($col_type, 2), $col_type, id FROM t1_values';
1002 --source suite/funcs_1/views/fv1.inc
1003 let $col_type= my_binary_30;
1004 eval SET @my_select =
1005 'SELECT LEFT($col_type, 2), $col_type, id FROM t1_values';
1006 --source suite/funcs_1/views/fv1.inc
1007 let $col_type= my_varbinary_1000;
1008 eval SET @my_select =
1009 'SELECT LEFT($col_type, 2), $col_type, id FROM t1_values';
1010 --source suite/funcs_1/views/fv1.inc
1011 # Bug#11728 string function LEFT, strange undocumented behaviour, strict mode
1012 # Bug#10963 LEFT string function returns wrong result with large length
1013 let $col_type= my_bigint;
1014 eval SET @my_select =
1015 'SELECT LEFT(''AaBbCcDdEeFfGgHhIiJjÄäÜüÖö'', $col_type), $col_type, id FROM t1_values';
1016 --source suite/funcs_1/views/fv1.inc
1017 let $col_type= my_decimal;
1018 eval SET @my_select =
1019 'SELECT LEFT(''AaBbCcDdEeFfGgHhIiJjÄäÜüÖö'', $col_type), $col_type, id FROM t1_values';
1020 --source suite/funcs_1/views/fv1.inc
1021 # Bug#10963 LEFT string function returns wrong result with large length
1022 let $col_type= my_double;
1023 eval SET @my_select =
1024 'SELECT LEFT(''AaBbCcDdEeFfGgHhIiJjÄäÜüÖö'', $col_type), $col_type, id FROM t1_values';
1025 --source suite/funcs_1/views/fv1.inc
1026
1027 # 3.20. LENGTH(str)
1028 let $col_type= my_char_30;
1029 eval SET @my_select = 'SELECT LENGTH($col_type),
1030 $col_type, id FROM t1_values';
1031 --source suite/funcs_1/views/fv2.inc
1032 let $col_type= my_varchar_1000;
1033 eval SET @my_select = 'SELECT LENGTH($col_type),
1034 $col_type, id FROM t1_values';
1035 --source suite/funcs_1/views/fv2.inc
1036 let $col_type= my_binary_30;
1037 eval SET @my_select = 'SELECT LENGTH($col_type),
1038 $col_type, id FROM t1_values';
1039 --source suite/funcs_1/views/fv2.inc
1040 let $col_type= my_varbinary_1000;
1041 eval SET @my_select = 'SELECT LENGTH($col_type),
1042 $col_type, id FROM t1_values';
1043 --source suite/funcs_1/views/fv2.inc
1044
1045
1046 # 3.21. LOAD_FILE(file_name)
1047 # Reads the file and returns the file contents as a string.
1048 # If the file doesn't exist or cannot be read ... ,
1049 # the function returns NULL.
1050 # SELECT LOADFILE
1051 --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
1052 eval SET @my_select =
1053 'SELECT LOAD_FILE(''$MYSQLTEST_VARDIR/std_data/funcs_1/load_file.txt'')
1054 AS my_col,
1055 id FROM t1_values';
1056 --source suite/funcs_1/views/fv1.inc
1057
1058
1059 # 3.22. LOCATE(substr,str) , LOCATE(substr,str,pos)
1060 let $col_type= my_char_30;
1061 eval SET @my_select = 'SELECT LOCATE(''char'', $col_type),
1062 $col_type, id FROM t1_values';
1063 --source suite/funcs_1/views/fv2.inc
1064 let $col_type= my_varchar_1000;
1065 eval SET @my_select = 'SELECT LOCATE(''char'', $col_type),
1066 $col_type, id FROM t1_values';
1067 --source suite/funcs_1/views/fv2.inc
1068 let $col_type= my_binary_30;
1069 eval SET @my_select = 'SELECT LOCATE(''char'', $col_type),
1070 $col_type, id FROM t1_values';
1071 --source suite/funcs_1/views/fv2.inc
1072 let $col_type= my_varbinary_1000;
1073 eval SET @my_select = 'SELECT LOCATE(''char'', $col_type),
1074 $col_type, id FROM t1_values';
1075 --source suite/funcs_1/views/fv2.inc
1076 #------------------------------------------------------
1077 let $col_type1= my_char_30;
1078 # against all other
1079 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type1 ),
1080 $col_type1, id FROM t1_values';
1081 --source suite/funcs_1/views/fv2.inc
1082 let $col_type2= my_varchar_1000;
1083 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1084 $col_type1, $col_type2 id FROM t1_values';
1085 --source suite/funcs_1/views/fv2.inc
1086 let $col_type2= my_binary_30;
1087 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1088 $col_type1, $col_type2 id FROM t1_values';
1089 --source suite/funcs_1/views/fv2.inc
1090 let $col_type2= my_varbinary_1000;
1091 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1092 $col_type1, $col_type2 id FROM t1_values';
1093 --source suite/funcs_1/views/fv2.inc
1094 #------------------------------------------------------
1095 let $col_type1= my_varchar_1000;
1096 # against all other
1097 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type1 ),
1098 $col_type1, id FROM t1_values';
1099 --source suite/funcs_1/views/fv2.inc
1100 let $col_type2= my_char_30;
1101 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1102 $col_type1, $col_type2 id FROM t1_values';
1103 --source suite/funcs_1/views/fv2.inc
1104 let $col_type2= my_binary_30;
1105 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1106 $col_type1, $col_type2 id FROM t1_values';
1107 --source suite/funcs_1/views/fv2.inc
1108 let $col_type2= my_varbinary_1000;
1109 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1110 $col_type1, $col_type2 id FROM t1_values';
1111 --source suite/funcs_1/views/fv2.inc
1112 #------------------------------------------------------
1113 let $col_type1= my_binary_30;
1114 # against all other
1115 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type1 ),
1116 $col_type1, id FROM t1_values';
1117 --source suite/funcs_1/views/fv2.inc
1118 let $col_type2= my_char_30;
1119 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1120 $col_type1, $col_type2 id FROM t1_values';
1121 --source suite/funcs_1/views/fv2.inc
1122 let $col_type2= my_varchar_1000;
1123 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1124 $col_type1, $col_type2 id FROM t1_values';
1125 --source suite/funcs_1/views/fv2.inc
1126 let $col_type2= my_varbinary_1000;
1127 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1128 $col_type1, $col_type2 id FROM t1_values';
1129 --source suite/funcs_1/views/fv2.inc
1130 #------------------------------------------------------
1131 let $col_type1= my_varbinary_1000;
1132 # against all other
1133 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type1 ),
1134 $col_type1, id FROM t1_values';
1135 --source suite/funcs_1/views/fv2.inc
1136 let $col_type2= my_char_30;
1137 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1138 $col_type1, $col_type2 id FROM t1_values';
1139 --source suite/funcs_1/views/fv2.inc
1140 let $col_type2= my_varchar_1000;
1141 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1142 $col_type1, $col_type2 id FROM t1_values';
1143 --source suite/funcs_1/views/fv2.inc
1144 let $col_type2= my_binary_30;
1145 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1146 $col_type1, $col_type2 id FROM t1_values';
1147 --source suite/funcs_1/views/fv2.inc
1148
1149 # FIXME How to test exotic or interesting substr values like NULL, '', ' '
1150 # without getting too much result rows
1151 # FIXME Testcases with LOCATE(substr,str,pos)
1152 let $col_type= my_char_30;
1153 eval SET @my_select = 'SELECT LOCATE(''-'', $col_type, 3),
1154 $col_type, id FROM t1_values';
1155 --source suite/funcs_1/views/fv2.inc
1156 let $col_type= my_varchar_1000;
1157 eval SET @my_select = 'SELECT LOCATE(''-'', $col_type, 3),
1158 $col_type, id FROM t1_values';
1159 --source suite/funcs_1/views/fv2.inc
1160 let $col_type= my_binary_30;
1161 eval SET @my_select = 'SELECT LOCATE(''-'', $col_type, 3),
1162 $col_type, id FROM t1_values';
1163 --source suite/funcs_1/views/fv2.inc
1164 let $col_type= my_varbinary_1000;
1165 eval SET @my_select = 'SELECT LOCATE(''-'', $col_type, 3),
1166 $col_type, id FROM t1_values';
1167 --source suite/funcs_1/views/fv2.inc
1168 #--------------------------------------------------------
1169 let $col_type= my_bigint;
1170 eval SET @my_select = 'SELECT LOCATE(''-'', '' - -ABC'', $col_type),
1171 $col_type, id FROM t1_values';
1172 --source suite/funcs_1/views/fv2.inc
1173 let $col_type= my_double;
1174 eval SET @my_select = 'SELECT LOCATE(''-'', '' - -ABC'', $col_type),
1175 $col_type, id FROM t1_values';
1176 --source suite/funcs_1/views/fv2.inc
1177 let $col_type= my_decimal;
1178 eval SET @my_select = 'SELECT LOCATE(''-'', '' - -ABC'', $col_type),
1179 $col_type, id FROM t1_values';
1180 --source suite/funcs_1/views/fv2.inc
1181
1182
1183 # 3.23. LOWER(str)
1184 let $col_type= my_char_30;
1185 eval SET @my_select = 'SELECT LOWER($col_type),
1186 $col_type, id FROM t1_values';
1187 --source suite/funcs_1/views/fv2.inc
1188 let $col_type= my_varchar_1000;
1189 eval SET @my_select = 'SELECT LOWER($col_type),
1190 $col_type, id FROM t1_values';
1191 --source suite/funcs_1/views/fv2.inc
1192 let $col_type= my_binary_30;
1193 eval SET @my_select = 'SELECT LOWER($col_type),
1194 $col_type, id FROM t1_values';
1195 --source suite/funcs_1/views/fv2.inc
1196 let $col_type= my_varbinary_1000;
1197 eval SET @my_select = 'SELECT LOWER($col_type),
1198 $col_type, id FROM t1_values';
1199 --source suite/funcs_1/views/fv2.inc
1200
1201
1202 # 3.24. LPAD(str,len,padstr)
1203 # 3.25. LTRIM(str)
1204 let $col_type= my_char_30;
1205 eval SET @my_select = 'SELECT LTRIM($col_type),
1206 $col_type, id FROM t1_values';
1207 --source suite/funcs_1/views/fv2.inc
1208 let $col_type= my_varchar_1000;
1209 eval SET @my_select = 'SELECT LTRIM($col_type),
1210 $col_type, id FROM t1_values';
1211 --source suite/funcs_1/views/fv2.inc
1212 let $col_type= my_binary_30;
1213 eval SET @my_select = 'SELECT LTRIM($col_type),
1214 $col_type, id FROM t1_values';
1215 --source suite/funcs_1/views/fv2.inc
1216 let $col_type= my_varbinary_1000;
1217 eval SET @my_select = 'SELECT LTRIM($col_type),
1218 $col_type, id FROM t1_values';
1219 --source suite/funcs_1/views/fv2.inc
1220
1221
1222 # 3.26. MAKE_SET(bits,str1,str2,...)
1223 # .....
1224 # FIXME: to be implemented
1225
1226 ################################################################################
1227 # Please do not add SELECTs and interesting records after this line. #
1228 # These last SELECTs are mostly for checking the testcase code itself. #
1229 ################################################################################
1230 eval SET @my_select =
1231 'SELECT CONCAT(''A'',my_char_30), my_char_30, id FROM t1_values'; #
1232 --source suite/funcs_1/views/fv1.inc
1233 #
1234 eval SET @my_select = 'SELECT my_char_30, id FROM t1_values'; #
1235 --source suite/funcs_1/views/fv2.inc
1236 eval INSERT INTO t1_values SET select_id = @select_id,
1237 my_char_30 = 'Viana do Castelo';
1238 ################################################################################
1239 SET sql_mode = ''; #
1240
1241 ##### END Basic preparations #######################################
1242
1243
1244 let $message= "# The basic preparations end and the main test starts here";
1245 --source include/show_msg80.inc
1246
1247 --disable_ps_protocol
1248
1249 ##### The tests start here #####################################################
1250
1251 # Determine the number of different SELECTs to be checked
1252 --disable_query_log
1253 SELECT COUNT(*) INTO @num_selects FROM t1_selects;
1254 --enable_query_log
1255 # Debug statement
1256 # SELECT @num_selects AS "number of SELECTS:";
1257
1258 --disable_abort_on_error
1259 let $select_id= `SELECT @num_selects`;
1260 while ($select_id)
1261 {
1262 # Determine the SELECT
1263 --disable_query_log
1264 eval SELECT my_select, disable_result INTO @my_select, @disable_result
1265 FROM t1_selects WHERE id = $select_id;
1266 let $run_no_result= `SELECT @disable_result = 'Yes'`;
1267 --enable_query_log
1268 # Debug statement
1269 # SELECT @my_select AS "SELECT:";
1270 let $my_select= `SELECT @my_select`;
1271
1272 let $run0= 0;
1273 if ($view_select_result)
1274 {
1275 # Create the VIEW
1276 --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
1277 eval CREATE VIEW v1 AS $my_select;
1278 --disable_query_log
1279 eval set @got_errno= $mysql_errno ;
1280 let $run0= `SELECT @got_errno = 0`;
1281 --enable_query_log
1282 if (!$run0)
1283 {
1284 --echo
1285 --echo Attention: The last CREATE VIEW failed
1286 --echo
1287 }
1288 }
1289
1290 # FIXME The loop over the modes will start here.
1291
1292 if ($simple_select_result)
1293 {
1294 # Simple SELECT on the base table of the VIEW for comparison
1295
1296 if ($run_no_result)
1297 {
1298 --disable_result_log
1299 }
1300 --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
1301 eval $my_select
1302 WHERE select_id = $select_id OR select_id IS NULL order by id;
1303 if ($run_no_result)
1304 {
1305 --enable_result_log
1306 }
1307 if ($mysql_errno)
1308 {
1309 --echo
1310 --echo Attention: The last SELECT on the base table failed
1311 --echo
1312 }
1313 }
1314
1315 # $run0 is 1, if CREATE VIEW was successful.
1316 # That means SHOW CREATE VIEW/SELECT/DROP should be executed.
1317 if ($run0)
1318 {
1319 # Check the CREATE VIEW statement
1320 --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
1321 SHOW CREATE VIEW v1;
1322 if ($mysql_errno)
1323 {
1324 --echo
1325 --echo Attention: The last SHOW CREATE VIEW failed
1326 --echo
1327 }
1328
1329 # Maybe a Join is faster
1330 if ($run_no_result)
1331 {
1332 --disable_result_log
1333 }
1334 eval SELECT v1.* FROM v1
1335 WHERE v1.id IN (SELECT id FROM t1_values
1336 WHERE select_id = $select_id OR select_id IS NULL) order by id;
1337 if ($run_no_result)
1338 {
1339 --enable_result_log
1340 }
1341 if ($mysql_errno)
1342 {
1343 --echo
1344 --echo Attention: The last SELECT from VIEW failed
1345 --echo
1346 }
1347
1348 DROP VIEW v1;
1349 }
1350
1351 # FIXME The loop over the modes will end here.
1352
1353 # Produce two empty lines as separator between different SELECTS
1354 # to be tested.
1355 --echo
1356 --echo
1357
1358 dec $select_id ;
1359 }
1360
1361 --enable_ps_protocol
1362
1363 DROP TABLE t1_selects, t1_modes, t1_values;
1364
1365 SET timestamp=DEFAULT;