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