1 ###################################################
3 # Functions within VIEWs #
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
10 # 0. Some notes about this test:
11 # #################################################################
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.
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.
26 # Maybe it is better to change the architecture of this test in such
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.
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.
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
57 # There are open bugs and I cannot omit statements which reveal these
59 # But there will be a special messages within the protocol files.
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)
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 .
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 .
79 # The test will abort if one of the basic preparation statement fails
80 # (except ALTER TABLE ADD ...).
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.
93 # ./mysql-test-run.pl --socket=var/tmp/master.sock --start-dirty
94 # 3. Issue the statements needed by using "mysql" or "mysqltest".
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
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
108 # records which should be used dedicated to the SELECT above
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")
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
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.
155 DROP TABLE IF EXISTS t1_selects, t1_modes, t1_values;
156 DROP VIEW IF EXISTS v1;
160 # Storage for the SELECTs to be used for the VIEW definition
161 # Attention: my_select must be no too small because a statement like
162 # SELECT LOAD_FILE(< file in MYSQLTEST_VARDIR >)
164 # id FROM t1_values';
165 # might be a very long
166 # Bug#38427 "Data too long" ... tests "<ENGINE>_func_view" fail
167 CREATE TABLE t1_selects
169 id BIGINT AUTO_INCREMENT,
170 my_select VARCHAR(1000) NOT NULL,
171 disable_result ENUM('Yes','No') NOT NULL default 'No',
176 # MODES to be checked
177 CREATE TABLE t1_modes
179 id BIGINT AUTO_INCREMENT,
180 my_mode VARCHAR(200) NOT NULL,
186 # The table to be used in the FROM parts of the SELECTs
187 --replace_result $type <engine_to_be_tested>
188 eval CREATE TABLE t1_values
190 id BIGINT AUTO_INCREMENT,
195 ##### BEGIN Basic preparations #######################################
197 # 1. Extend t1_values with the columns you need
198 # - the column name must show the data type
199 # - do not add NOT NULL columns
200 # - do not worry if the intended column data type is not
201 # available for some storage engines
202 # Please do not forget to assign values for the new columns (paragraph 2.).
203 --disable_abort_on_error
204 ALTER TABLE t1_values ADD my_char_30 CHAR(30);
205 ALTER TABLE t1_values ADD my_varchar_1000 VARCHAR(1000);
206 ALTER TABLE t1_values ADD my_binary_30 BINARY(30);
207 ALTER TABLE t1_values ADD my_varbinary_1000 VARBINARY(1000);
208 ALTER TABLE t1_values ADD my_datetime DATETIME;
209 ALTER TABLE t1_values ADD my_date DATE;
210 ALTER TABLE t1_values ADD ts_dummy TIMESTAMP;
211 ALTER TABLE t1_values ADD my_timestamp TIMESTAMP;
212 ALTER TABLE t1_values ADD my_time TIME;
213 ALTER TABLE t1_values ADD my_year YEAR;
214 ALTER TABLE t1_values ADD my_bigint BIGINT;
215 ALTER TABLE t1_values ADD my_double DOUBLE;
216 ALTER TABLE t1_values ADD my_decimal DECIMAL(64,30);
217 --enable_abort_on_error
219 #-------------------------------------------------------------------------------
222 # 2. Insert some predefinded rows
224 # - t1_values.select_id IS NULL
225 # - will be selected by every SELECT with function to be tested
226 # - have to be inserted when sql_mode = 'traditional' is valid, because
227 # we do not want to start with "illegal/unexpected/..." values.
228 # Such experiments should be done in other testcases.
230 # - modifying column values of predefined rows they might change many
232 # - additional predefined rows should be really useful for the majority of
233 # all sub testcases, since they blow up all result sets.
234 SET sql_mode = 'traditional';
236 # 2.1 record -- everything to NULL
237 INSERT INTO t1_values SET id = 0;
239 # 2.2 record -- everything to "minimum"
240 # numbers, date/time types -> minimum of range
241 # strings, blobs, binaries -> ''
243 INSERT INTO t1_values SET
245 my_varchar_1000 = '',
247 my_varbinary_1000 = '',
248 my_datetime = '0001-01-01 00:00:00',
249 my_date = '0001-01-01',
250 my_timestamp = '1970-01-01 03:00:01',
251 my_time = '-838:59:59',
253 my_bigint = -9223372036854775808,
254 my_decimal = -9999999999999999999999999999999999.999999999999999999999999999999 ,
255 my_double = -1.7976931348623E+308;
256 # shortened due to bug#32285
257 # my_double = -1.7976931348623157E+308;
259 # 2.3 record -- everything to "maximum"
260 # numbers, date/time types -> maximum of range
261 # strings, blobs, binaries -> '<- full length of used data type>'
263 INSERT INTO t1_values SET
264 my_char_30 = '<--------30 characters------->',
265 my_varchar_1000 = CONCAT('<---------1000 characters',
266 RPAD('',965,'-'),'--------->'),
267 my_binary_30 = '<--------30 characters------->',
268 my_varbinary_1000 = CONCAT('<---------1000 characters',
269 RPAD('',965,'-'),'--------->'),
270 my_datetime = '9999-12-31 23:59:59',
271 my_date = '9999-12-31',
272 my_timestamp = '2038-01-01 02:59:59',
273 my_time = '838:59:59',
275 my_bigint = 9223372036854775807,
276 my_decimal = +9999999999999999999999999999999999.999999999999999999999999999999 ,
277 my_double = 1.7976931348623E+308;
278 # shortened due to bug#32285
279 # my_double = -1.7976931348623157E+308;
281 # 2.4 record -- everything to "magic" value if available or
282 # other interesting value
284 # strings, blobs, binaries -> not full length of used data type, "exotic"
285 # characters and preceeding and trailing spaces
287 INSERT INTO t1_values SET
288 my_char_30 = ' ---äÖüß@µ*$-- ',
289 my_varchar_1000 = ' ---äÖüß@µ*$-- ',
290 my_binary_30 = ' ---äÖüß@µ*$-- ',
291 my_varbinary_1000 = ' ---äÖüß@µ*$-- ',
292 my_datetime = '2004-02-29 23:59:59',
293 my_date = '2004-02-29',
294 my_timestamp = '2004-02-29 23:59:59',
295 my_time = '13:00:00',
301 # 2.5 record -- everything to "harmless" value if available
302 # numbers -> -1 (logical)
303 # strings, blobs, binaries -> '-1' useful for numeric functions
305 INSERT INTO t1_values SET
307 my_varchar_1000 = '-1',
309 my_varbinary_1000 = '-1',
310 my_datetime = '2005-06-28 10:00:00',
311 my_date = '2005-06-28',
312 my_timestamp = '2005-06-28 10:00:00',
313 my_time = '10:00:00',
316 my_decimal = -1.000000000000000000000000000000,
319 #-------------------------------------------------------------------------------
322 # 3. Add the SELECTs with function which should be used within VIEWs
324 # records which should be used dedicated to the SELECT above
325 # - Please avoid WHERE clauses
326 # - Include the PRIMARY KEY ("id") of the base table t1_values into the
328 # - Include the base table column used as function parameter into the
329 # select column list, because it is much easier to check the results
330 # - Do not forget to escape single quotes
332 # SET @my_select = 'SELECT sqrt(my_bigint), my_bigint, id FROM t1_values'
333 # SET @my_select = 'SELECT CONCAT(\'A\',my_char_30), id FROM t1_values';
334 # - Statements, which reveal open crashing bugs MUST be disabled.
335 # - Result sets must not contain data, which might differ between boxes
336 # executing this test.
337 # Example: current time, absolute path to some files ...
338 # - Please derive the functions to be checked from the MySQL manual
339 # and use the same order. This means copy the the function names as
340 # comment into this test and start to implement a testcase for your
341 # most preferred function.
342 # This method avoids that we forget a function and gives a better
345 # If you have the time to check the result sets do the insert of the
346 # SELECT with function via:
347 # eval SET @my_select =
349 # --source suite/funcs_1/views/fv1.inc
350 # fv1.inc sets t1_selects.disable_result to 'No' and the effect will be,
351 # that the result set will be logged.
353 # If you do not have the time to check the result sets do the insert of the
354 # SELECT with function via:
355 # eval SET @my_select =
357 # --source suite/funcs_1/views/fv2.inc
358 # fv2.inc sets t1_selects.disable_result to 'Yes' and the effect will be,
359 # that the result set will be not logged.
360 # This should be only a temporary solution and it does not remove the
361 # need to check the server return codes.
362 # That means even when we do not have the time to check the correctness of
363 # the result sets, we check if
364 # - SELECT <function> or
365 # - SELECT * FROM <VIEW with function>
366 # crash the server or get suspicious server responses.
368 # - the SELECTs will be applied to the rows defined here (3.) +
369 # all predefined rows (2.)
370 # - the rows dedicated to the SELECT should contain especially interesting
371 # column values or combinations of column values, which are not covered
372 # by the predefined records
373 # - The records have to be inserted when sql_mode = 'traditional' is valid.
374 # - Please do not insert records with column values where the allowed
375 # range is exceeded. The SQL mode 'traditional' will prevent such
376 # inserts. Such experiments should be done in other tests, because
377 # they inflate the current test without giving an appropriate value.
380 # The function to be tested is "sqrt".
381 # The minimum, maximum, default and NULL value are covered by the
383 # A value where sqrt(<value>) = <integer value> in strict mathematics
384 # would be of interest.
385 # --> Add a record with my_bigint = 4
386 # --> Just for fun my_bigint = -25 .
388 # Some internal stuff
389 PREPARE ins_sel_with_result FROM "INSERT INTO t1_selects SET my_select = @my_select,
390 disable_result = 'No'" ;
391 PREPARE ins_sel_no_result FROM "INSERT INTO t1_selects SET my_select = @my_select,
392 disable_result = 'Yes'" ;
393 SET sql_mode = 'traditional';
394 # --disable_query_log
396 let $col_type= my_bigint;
398 # eval SET @my_select = 'SELECT CONCAT(''A'', $col_type), $col_type, id';
399 eval SET @my_select = 'SELECT sqrt($col_type), $col_type, id FROM t1_values';
400 --source suite/funcs_1/views/fv1.inc
401 # Content of suite/funcs_1/views/fv1.inc :
402 # --disable_query_log
403 # EXECUTE ins_sel_with_result;
404 # SET @select_id = LAST_INSERT_ID();
407 eval INSERT INTO t1_values SET select_id = @select_id,
409 eval INSERT INTO t1_values SET select_id = @select_id,
411 # SELECT * FROM t1_values;
413 # 1. Cast Functions and Operators
416 # Note(mleich): I guess the CAST routines are used in many other functions.
417 # Therefore check also nearly all "ugly" variants like
418 # CAST(<string composed of non digits> AS DECIMAL) here.
420 # suite/funcs_1/views/fv_cast.inc contains
421 # SELECT CAST($col_type AS $target_type), ...
424 # 1.1.1. CAST --> BINARY
425 --echo ##### 1.1.1. CAST --> BINARY
426 let $target_type= BINARY;
428 let $col_type= my_char_30;
429 --source suite/funcs_1/views/fv_cast.inc
430 let $col_type= my_varchar_1000;
431 --source suite/funcs_1/views/fv_cast.inc
432 let $col_type= my_binary_30;
433 --source suite/funcs_1/views/fv_cast.inc
434 let $col_type= my_varbinary_1000;
435 --source suite/funcs_1/views/fv_cast.inc
436 let $col_type= my_bigint;
437 --source suite/funcs_1/views/fv_cast.inc
438 let $col_type= my_decimal;
439 --source suite/funcs_1/views/fv_cast.inc
440 let $col_type= my_double;
441 --source suite/funcs_1/views/fv_cast.inc
442 let $col_type= my_datetime;
443 --source suite/funcs_1/views/fv_cast.inc
444 let $col_type= my_date;
445 --source suite/funcs_1/views/fv_cast.inc
446 let $col_type= my_timestamp;
447 --source suite/funcs_1/views/fv_cast.inc
448 let $col_type= my_time;
449 --source suite/funcs_1/views/fv_cast.inc
450 let $col_type= my_year;
451 --source suite/funcs_1/views/fv_cast.inc
454 # 1.1.2. CAST --> CHAR
455 --echo ##### 1.1.2. CAST --> CHAR
456 let $target_type= CHAR;
458 let $col_type= my_char_30;
459 --source suite/funcs_1/views/fv_cast.inc
460 let $col_type= my_varchar_1000;
461 --source suite/funcs_1/views/fv_cast.inc
462 let $col_type= my_binary_30;
463 --source suite/funcs_1/views/fv_cast.inc
464 let $col_type= my_varbinary_1000;
465 --source suite/funcs_1/views/fv_cast.inc
466 let $col_type= my_bigint;
467 --source suite/funcs_1/views/fv_cast.inc
468 let $col_type= my_decimal;
469 --source suite/funcs_1/views/fv_cast.inc
470 let $col_type= my_double;
471 --source suite/funcs_1/views/fv_cast.inc
472 let $col_type= my_datetime;
473 --source suite/funcs_1/views/fv_cast.inc
474 let $col_type= my_date;
475 --source suite/funcs_1/views/fv_cast.inc
476 let $col_type= my_timestamp;
477 --source suite/funcs_1/views/fv_cast.inc
478 let $col_type= my_time;
479 --source suite/funcs_1/views/fv_cast.inc
480 let $col_type= my_year;
481 --source suite/funcs_1/views/fv_cast.inc
484 # 1.1.3. CAST --> DATE
485 --echo ##### 1.1.3. CAST --> DATE
486 let $target_type= DATE;
488 let $col_type= my_char_30;
489 --source suite/funcs_1/views/fv_cast.inc
490 eval INSERT INTO t1_values SET select_id = @select_id,
491 $col_type = '2005-06-27';
492 let $col_type= my_varchar_1000;
493 --source suite/funcs_1/views/fv_cast.inc
494 eval INSERT INTO t1_values SET select_id = @select_id,
495 $col_type = '2005-06-27';
496 let $col_type= my_binary_30;
497 --source suite/funcs_1/views/fv_cast.inc
498 eval INSERT INTO t1_values SET select_id = @select_id,
499 $col_type = '2005-06-27';
500 let $col_type= my_varbinary_1000;
501 --source suite/funcs_1/views/fv_cast.inc
502 eval INSERT INTO t1_values SET select_id = @select_id,
503 $col_type = '2005-06-27';
504 let $col_type= my_bigint;
505 --source suite/funcs_1/views/fv_cast.inc
506 eval INSERT INTO t1_values SET select_id = @select_id,
507 $col_type = 20050627;
508 let $col_type= my_double;
509 --source suite/funcs_1/views/fv_cast.inc
510 eval INSERT INTO t1_values SET select_id = @select_id,
511 $col_type = +20.050627E+6;
512 let $col_type= my_datetime;
513 --source suite/funcs_1/views/fv_cast.inc
514 let $col_type= my_date;
515 --source suite/funcs_1/views/fv_cast.inc
516 let $col_type= my_timestamp;
517 --source suite/funcs_1/views/fv_cast.inc
518 let $col_type= my_time;
519 --source suite/funcs_1/views/fv_cast.inc
520 let $col_type= my_year;
521 --source suite/funcs_1/views/fv_cast.inc
524 # 1.1.4. CAST --> DATETIME
525 --echo ##### 1.1.4. CAST --> DATETIME
526 let $target_type= DATETIME;
528 let $col_type= my_char_30;
529 --source suite/funcs_1/views/fv_cast.inc
530 eval INSERT INTO t1_values SET select_id = @select_id,
531 $col_type = '2005-06-27 17:58';
532 let $col_type= my_varchar_1000;
533 --source suite/funcs_1/views/fv_cast.inc
534 eval INSERT INTO t1_values SET select_id = @select_id,
535 $col_type = '2005-06-27 17:58';
536 let $col_type= my_binary_30;
537 --source suite/funcs_1/views/fv_cast.inc
538 eval INSERT INTO t1_values SET select_id = @select_id,
539 $col_type = '2005-06-27 17:58';
540 let $col_type= my_varbinary_1000;
541 --source suite/funcs_1/views/fv_cast.inc
542 eval INSERT INTO t1_values SET select_id = @select_id,
543 $col_type = '2005-06-27 17:58';
544 let $col_type= my_bigint;
545 --source suite/funcs_1/views/fv_cast.inc
546 eval INSERT INTO t1_values SET select_id = @select_id,
547 $col_type = 200506271758;
548 let $col_type= my_double;
549 --source suite/funcs_1/views/fv_cast.inc
550 eval INSERT INTO t1_values SET select_id = @select_id,
551 $col_type = +0.0200506271758E+13;
552 let $col_type= my_datetime;
553 --source suite/funcs_1/views/fv_cast.inc
554 let $col_type= my_date;
555 --source suite/funcs_1/views/fv_cast.inc
556 let $col_type= my_timestamp;
557 --source suite/funcs_1/views/fv_cast.inc
558 let $col_type= my_time;
559 --source suite/funcs_1/views/fv_cast.inc
560 let $col_type= my_year;
561 --source suite/funcs_1/views/fv_cast.inc
564 # 1.1.5. CAST --> TIME
565 --echo ##### 1.1.5. CAST --> TIME
566 let $target_type= TIME;
568 let $col_type= my_char_30;
569 --source suite/funcs_1/views/fv_cast.inc
570 eval INSERT INTO t1_values SET select_id = @select_id,
571 $col_type = '1 17:58';
572 let $col_type= my_varchar_1000;
573 --source suite/funcs_1/views/fv_cast.inc
574 eval INSERT INTO t1_values SET select_id = @select_id,
575 $col_type = '1 17:58';
576 let $col_type= my_binary_30;
577 --source suite/funcs_1/views/fv_cast.inc
578 eval INSERT INTO t1_values SET select_id = @select_id,
579 $col_type = '1 17:58';
580 let $col_type= my_varbinary_1000;
581 --source suite/funcs_1/views/fv_cast.inc
582 eval INSERT INTO t1_values SET select_id = @select_id,
583 $col_type = '1 17:58';
584 let $col_type= my_bigint;
585 --source suite/funcs_1/views/fv_cast.inc
586 eval INSERT INTO t1_values SET select_id = @select_id,
588 let $col_type= my_double;
589 # Bug#12440: CAST(data type DOUBLE AS TIME) strange results;
590 --source suite/funcs_1/views/fv_cast.inc
591 eval INSERT INTO t1_values SET select_id = @select_id,
592 $col_type = +1.758E+3;
593 let $col_type= my_datetime;
594 --source suite/funcs_1/views/fv_cast.inc
595 let $col_type= my_date;
596 --source suite/funcs_1/views/fv_cast.inc
597 let $col_type= my_timestamp;
598 --source suite/funcs_1/views/fv_cast.inc
599 let $col_type= my_time;
600 --source suite/funcs_1/views/fv_cast.inc
601 let $col_type= my_year;
602 --source suite/funcs_1/views/fv_cast.inc
605 # 1.1.6. CAST --> DECIMAL
606 --echo ##### 1.1.6. CAST --> DECIMAL
607 # Set the following to (37,2) since the default was changed to (10,0) - OBN
608 let $target_type= DECIMAL(37,2);
610 let $col_type= my_char_30;
611 --source suite/funcs_1/views/fv_cast.inc
612 eval INSERT INTO t1_values SET select_id = @select_id,
613 $col_type = '-3333.3333';
614 let $col_type= my_varchar_1000;
615 --source suite/funcs_1/views/fv_cast.inc
616 eval INSERT INTO t1_values SET select_id = @select_id,
617 $col_type = '-3333.3333';
618 let $col_type= my_binary_30;
619 --source suite/funcs_1/views/fv_cast.inc
620 eval INSERT INTO t1_values SET select_id = @select_id,
621 $col_type = '-3333.3333';
622 let $col_type= my_varbinary_1000;
623 --source suite/funcs_1/views/fv_cast.inc
624 eval INSERT INTO t1_values SET select_id = @select_id,
625 $col_type = '-3333.3333';
626 let $col_type= my_bigint;
627 --source suite/funcs_1/views/fv_cast.inc
628 let $col_type= my_decimal;
629 --source suite/funcs_1/views/fv_cast.inc
630 # Bug#13349: CAST(1.0E+300 TO DECIMAL) returns wrong result + diff little/big endian;
631 let $col_type= my_double;
632 --source suite/funcs_1/views/fv_cast.inc
633 eval INSERT INTO t1_values SET select_id = @select_id,
634 $col_type = -0.33333333E+4;
635 let $col_type= my_datetime;
636 --source suite/funcs_1/views/fv_cast.inc
637 let $col_type= my_date;
638 --source suite/funcs_1/views/fv_cast.inc
639 let $col_type= my_timestamp;
640 --source suite/funcs_1/views/fv_cast.inc
641 let $col_type= my_time;
642 --source suite/funcs_1/views/fv_cast.inc
643 let $col_type= my_year;
644 --source suite/funcs_1/views/fv_cast.inc
647 # 1.1.7. CAST --> SIGNED INTEGER
648 --echo ##### 1.1.7. CAST --> SIGNED INTEGER
649 let $target_type= SIGNED INTEGER;
652 "Attention: CAST --> SIGNED INTEGER
653 Bug#5913 Traditional mode: BIGINT range not correctly delimited
654 Status: To be fixed later";
655 --source include/show_msg80.inc
656 let $col_type= my_char_30;
657 --source suite/funcs_1/views/fv_cast.inc
658 let $col_type= my_varchar_1000;
659 --source suite/funcs_1/views/fv_cast.inc
660 let $col_type= my_binary_30;
661 --source suite/funcs_1/views/fv_cast.inc
662 let $col_type= my_varbinary_1000;
663 --source suite/funcs_1/views/fv_cast.inc
664 let $col_type= my_bigint;
665 --source suite/funcs_1/views/fv_cast.inc
666 let $col_type= my_decimal;
667 --source suite/funcs_1/views/fv_cast.inc
668 # Bug #13344: CAST(1E+300 TO signed int) on little endian CPU, wrong result;
669 let $col_type= my_double;
670 --source suite/funcs_1/views/fv_cast.inc
671 let $col_type= my_datetime;
672 --source suite/funcs_1/views/fv_cast.inc
673 let $col_type= my_date;
674 --source suite/funcs_1/views/fv_cast.inc
675 let $col_type= my_timestamp;
676 --source suite/funcs_1/views/fv_cast.inc
677 let $col_type= my_time;
678 --source suite/funcs_1/views/fv_cast.inc
679 let $col_type= my_year;
680 --source suite/funcs_1/views/fv_cast.inc
683 # 1.1.8. CAST --> UNSIGNED INTEGER
684 --echo ##### 1.1.8. CAST --> UNSIGNED INTEGER
685 let $target_type= UNSIGNED INTEGER;
688 "Attention: CAST --> UNSIGNED INTEGER
689 The file with expected results suffers from Bug 5913";
690 --source include/show_msg80.inc
691 let $col_type= my_char_30;
692 --source suite/funcs_1/views/fv_cast.inc
693 let $col_type= my_varchar_1000;
694 --source suite/funcs_1/views/fv_cast.inc
695 let $col_type= my_binary_30;
696 --source suite/funcs_1/views/fv_cast.inc
697 let $col_type= my_varbinary_1000;
698 --source suite/funcs_1/views/fv_cast.inc
699 let $col_type= my_bigint;
700 --source suite/funcs_1/views/fv_cast.inc
701 let $col_type= my_decimal;
702 --source suite/funcs_1/views/fv_cast.inc
703 let $message= some statements disabled because of
704 Bug#5913 Traditional mode: BIGINT range not correctly delimited;
705 --source include/show_msg80.inc
706 # Bug#8663 cant use bgint unsigned as input to cast
707 let $col_type= my_double;
708 --source suite/funcs_1/views/fv_cast.inc
709 let $col_type= my_datetime;
710 --source suite/funcs_1/views/fv_cast.inc
711 let $col_type= my_date;
712 --source suite/funcs_1/views/fv_cast.inc
713 let $col_type= my_timestamp;
714 --source suite/funcs_1/views/fv_cast.inc
715 let $col_type= my_time;
716 --source suite/funcs_1/views/fv_cast.inc
717 let $col_type= my_year;
718 --source suite/funcs_1/views/fv_cast.inc
722 # Manual: BINARY str is a shorthand for CAST(str AS BINARY).
723 # Therefore we do not test it here in the moment.
724 # FIXME: Add testcases for str in CHAR and VARCHAR only.
727 # 1.3 CONVERT(expr USING transcoding_name)
729 # 1.3.1 CONVERT(expr USING utf8)
730 let $target_charset= utf8;
732 let $col_type= my_char_30;
733 eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
734 $col_type, id FROM t1_values';
735 --source suite/funcs_1/views/fv1.inc
736 let $col_type= my_varchar_1000;
737 eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
738 $col_type, id FROM t1_values';
739 --source suite/funcs_1/views/fv1.inc
740 let $col_type= my_binary_30;
741 eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
742 $col_type, id FROM t1_values';
743 --source suite/funcs_1/views/fv1.inc
744 let $col_type= my_varbinary_1000;
745 eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
746 $col_type, id FROM t1_values';
747 --source suite/funcs_1/views/fv1.inc
749 # 1.3.2 CONVERT(expr USING koi8r)
750 let $target_charset= koi8r;
751 let $col_type= my_char_30;
752 eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
753 $col_type, id FROM t1_values';
754 --source suite/funcs_1/views/fv1.inc
755 let $col_type= my_varchar_1000;
756 eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
757 $col_type, id FROM t1_values';
758 --source suite/funcs_1/views/fv1.inc
759 let $col_type= my_binary_30;
760 eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
761 $col_type, id FROM t1_values';
762 --source suite/funcs_1/views/fv1.inc
763 let $col_type= my_varbinary_1000;
764 eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
765 $col_type, id FROM t1_values';
766 --source suite/funcs_1/views/fv1.inc
769 # 2. Control Flow Functions
770 # 2.1. CASE value WHEN [compare-value] THEN result [WHEN ...] [ELSE result]
772 # CASE WHEN [condition] THEN result [WHEN ...] [ELSE result] END
774 # FIXME: to be implemented
776 # 2.2. IF(expr1,expr2,expr3)
777 # expr1 is TRUE when (expr1 <> 0 and expr1 <> NULL) is fulfilled
779 # 2.2.1 IF(expr1,expr2,expr3) with expr1 = <column>
781 # Note(mleich): Strings, which do not contain a number -> FALSE
783 # suite/funcs_1/views/fv_if1.inc contains
784 # SELECT IF($col_type, 'IS TRUE', 'IS NOT TRUE'), ...
786 let $col_type= my_char_30;
787 --source suite/funcs_1/views/fv_if1.inc
789 let $col_type= my_varchar_1000;
790 --source suite/funcs_1/views/fv_if1.inc
792 let $col_type= my_binary_30;
793 --source suite/funcs_1/views/fv_if1.inc
795 let $col_type= my_varbinary_1000;
796 --source suite/funcs_1/views/fv_if1.inc
798 let $col_type= my_bigint;
799 --source suite/funcs_1/views/fv_if1.inc
801 let $col_type= my_decimal;
802 --source suite/funcs_1/views/fv_if1.inc
804 let $col_type= my_double;
805 --source suite/funcs_1/views/fv_if1.inc
807 let $col_type= my_datetime;
808 --source suite/funcs_1/views/fv_if1.inc
810 let $col_type= my_date;
811 --source suite/funcs_1/views/fv_if1.inc
813 let $col_type= my_timestamp;
814 --source suite/funcs_1/views/fv_if1.inc
816 let $col_type= my_time;
817 --source suite/funcs_1/views/fv_if1.inc
819 let $col_type= my_year;
820 --source suite/funcs_1/views/fv_if1.inc
823 # 2.2.2 IF(expr1,expr2,expr3) with expr1 != <column>
825 # suite/funcs_1/views/fv_if2.inc contains
826 # SELECT IF($col_type IS NULL, 'IS NULL', 'IS NOT NULL'), ...
828 # Note(mleich): July 2005
829 # IF($col_type IS NULL, ...) is mapped to a VIEW definition
830 # create ... view ... as
831 # select if(isnull(`test`.`t1`.`f1`),_latin1'IS NULL',
832 # _latin1'IS NOT NULL'),...
834 # Bug#11689 success on Create view .. IF(col1 IS NULL,...), col2 ; but SELECT fails
835 let $col_type= my_char_30;
836 --source suite/funcs_1/views/fv_if2.inc
838 let $col_type= my_varchar_1000;
839 --source suite/funcs_1/views/fv_if2.inc
841 let $col_type= my_binary_30;
842 --source suite/funcs_1/views/fv_if2.inc
844 let $col_type= my_varbinary_1000;
845 --source suite/funcs_1/views/fv_if2.inc
847 let $col_type= my_bigint;
848 --source suite/funcs_1/views/fv_if2.inc
850 let $col_type= my_decimal;
851 --source suite/funcs_1/views/fv_if2.inc
853 let $col_type= my_double;
854 --source suite/funcs_1/views/fv_if2.inc
856 let $col_type= my_datetime;
857 --source suite/funcs_1/views/fv_if2.inc
859 let $col_type= my_date;
860 --source suite/funcs_1/views/fv_if2.inc
862 let $col_type= my_timestamp;
863 --source suite/funcs_1/views/fv_if2.inc
865 let $col_type= my_time;
866 --source suite/funcs_1/views/fv_if2.inc
868 let $col_type= my_year;
869 --source suite/funcs_1/views/fv_if2.inc
872 # 2.3. IFNULL(expr1,expr2)
873 # If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2.
875 # suite/funcs_1/views/fv_ifnull.inc contains
876 # SELECT IFNULL($col_type, 'IS_NULL'), ....
877 # FIXME: The mixup of non string column values
878 # and the string 'IS NULL' within the first column of the
879 # result table is extreme ugly.
880 # CAST(IFNULL($col_type, 'IS_NULL') AS CHAR) looks better, but
881 # it has the disadvantage, that it involves CAST as additional
884 let $col_type= my_char_30;
885 --source suite/funcs_1/views/fv_ifnull.inc
887 let $col_type= my_varchar_1000;
888 --source suite/funcs_1/views/fv_ifnull.inc
890 let $col_type= my_binary_30;
891 --source suite/funcs_1/views/fv_ifnull.inc
893 let $col_type= my_varbinary_1000;
894 --source suite/funcs_1/views/fv_ifnull.inc
896 let $col_type= my_bigint;
897 --source suite/funcs_1/views/fv_ifnull.inc
899 let $col_type= my_decimal;
900 --source suite/funcs_1/views/fv_ifnull.inc
902 let $col_type= my_double;
903 --source suite/funcs_1/views/fv_ifnull.inc
905 let $col_type= my_datetime;
906 --source suite/funcs_1/views/fv_ifnull.inc
908 let $col_type= my_date;
909 --source suite/funcs_1/views/fv_ifnull.inc
911 let $col_type= my_timestamp;
912 --source suite/funcs_1/views/fv_ifnull.inc
914 let $col_type= my_time;
915 --source suite/funcs_1/views/fv_ifnull.inc
917 let $col_type= my_year;
918 --source suite/funcs_1/views/fv_ifnull.inc
921 # 2.4. NULLIF(expr1,expr2)
922 # Returns NULL if expr1 = expr2 is true, else returns expr1.
923 # This is the same as
924 # CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
926 # FIXME: to be implemented
930 # 3. String Functions
933 # FIXME: to be implemented
935 # 3.3. BIT_LENGTH(str)
936 # Returns the length of the string str in bits.
938 let $col_type= my_char_30;
939 eval SET @my_select = 'SELECT BIT_LENGTH($col_type),
940 $col_type, id FROM t1_values';
941 --source suite/funcs_1/views/fv1.inc
942 let $col_type= my_varchar_1000;
943 eval SET @my_select = 'SELECT BIT_LENGTH($col_type),
944 $col_type, id FROM t1_values';
945 --source suite/funcs_1/views/fv1.inc
946 let $col_type= my_binary_30;
947 eval SET @my_select = 'SELECT BIT_LENGTH($col_type),
948 $col_type, id FROM t1_values';
949 --source suite/funcs_1/views/fv1.inc
950 let $col_type= my_varbinary_1000;
951 eval SET @my_select = 'SELECT BIT_LENGTH($col_type),
952 $col_type, id FROM t1_values';
953 --source suite/funcs_1/views/fv1.inc
957 # 3.5. CHAR_LENGTH(str)
958 # 3.6 CHARACTER_LENGTH(str)
959 # CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().
960 # 3.7. COMPRESS(string_to_compress)
961 # 3.8. CONCAT(str1,str2,...)
962 # 3.9. CONCAT_WS(separator,str1,str2,...)
963 # 3.10. CONV(N,from_base,to_base)
964 # 3.11. ELT(N,str1,str2,str3,...)
965 # 3.12. EXPORT_SET(bits,on,off[,separator[,number_of_bits]])
966 # 3.13. FIELD(str,str1,str2,str3,...)
967 # 3.14. FIND_IN_SET(str,strlist)
969 # 3.16. INSERT(str,pos,len,newstr)
970 # 3.17. INSTR(str,substr)
971 # This is the same as the two-argument form of LOCATE(),
972 # except that the arguments are swapped.
973 # The majority of the testcases should be made with LOCATE().
974 # Therefore test only one example here.
975 let $col_type= my_char_30;
976 eval SET @my_select = 'SELECT INSTR($col_type, ''char''),
977 $col_type, id FROM t1_values';
978 --source suite/funcs_1/views/fv2.inc
982 # LCASE() is a synonym for LOWER().
983 # The majority of the testcases should be made with LOWER().
984 # Therefore test only one example here.
985 let $col_type= my_varchar_1000;
986 eval SET @my_select = 'SELECT LCASE($col_type),
987 $col_type, id FROM t1_values';
988 --source suite/funcs_1/views/fv2.inc
991 # 3.19. LEFT(str,len)
992 # Returns the leftmost len characters from the string str.
993 let $col_type= my_char_30;
994 eval SET @my_select =
995 'SELECT LEFT($col_type, 2), $col_type, id FROM t1_values';
996 --source suite/funcs_1/views/fv1.inc
997 let $col_type= my_varchar_1000;
998 eval SET @my_select =
999 'SELECT LEFT($col_type, 2), $col_type, id FROM t1_values';
1000 --source suite/funcs_1/views/fv1.inc
1001 let $col_type= my_binary_30;
1002 eval SET @my_select =
1003 'SELECT LEFT($col_type, 2), $col_type, id FROM t1_values';
1004 --source suite/funcs_1/views/fv1.inc
1005 let $col_type= my_varbinary_1000;
1006 eval SET @my_select =
1007 'SELECT LEFT($col_type, 2), $col_type, id FROM t1_values';
1008 --source suite/funcs_1/views/fv1.inc
1009 # Bug#11728 string function LEFT, strange undocumented behaviour, strict mode
1010 # Bug#10963 LEFT string function returns wrong result with large length
1011 let $col_type= my_bigint;
1012 eval SET @my_select =
1013 'SELECT LEFT(''AaBbCcDdEeFfGgHhIiJjÄäÜüÖö'', $col_type), $col_type, id FROM t1_values';
1014 --source suite/funcs_1/views/fv1.inc
1015 let $col_type= my_decimal;
1016 eval SET @my_select =
1017 'SELECT LEFT(''AaBbCcDdEeFfGgHhIiJjÄäÜüÖö'', $col_type), $col_type, id FROM t1_values';
1018 --source suite/funcs_1/views/fv1.inc
1019 # Bug#10963 LEFT string function returns wrong result with large length
1020 let $col_type= my_double;
1021 eval SET @my_select =
1022 'SELECT LEFT(''AaBbCcDdEeFfGgHhIiJjÄäÜüÖö'', $col_type), $col_type, id FROM t1_values';
1023 --source suite/funcs_1/views/fv1.inc
1026 let $col_type= my_char_30;
1027 eval SET @my_select = 'SELECT LENGTH($col_type),
1028 $col_type, id FROM t1_values';
1029 --source suite/funcs_1/views/fv2.inc
1030 let $col_type= my_varchar_1000;
1031 eval SET @my_select = 'SELECT LENGTH($col_type),
1032 $col_type, id FROM t1_values';
1033 --source suite/funcs_1/views/fv2.inc
1034 let $col_type= my_binary_30;
1035 eval SET @my_select = 'SELECT LENGTH($col_type),
1036 $col_type, id FROM t1_values';
1037 --source suite/funcs_1/views/fv2.inc
1038 let $col_type= my_varbinary_1000;
1039 eval SET @my_select = 'SELECT LENGTH($col_type),
1040 $col_type, id FROM t1_values';
1041 --source suite/funcs_1/views/fv2.inc
1044 # 3.21. LOAD_FILE(file_name)
1045 # Reads the file and returns the file contents as a string.
1046 # If the file doesn't exist or cannot be read ... ,
1047 # the function returns NULL.
1049 --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
1050 eval SET @my_select =
1051 'SELECT LOAD_FILE(''$MYSQLTEST_VARDIR/std_data/funcs_1/load_file.txt'')
1054 --source suite/funcs_1/views/fv1.inc
1057 # 3.22. LOCATE(substr,str) , LOCATE(substr,str,pos)
1058 let $col_type= my_char_30;
1059 eval SET @my_select = 'SELECT LOCATE(''char'', $col_type),
1060 $col_type, id FROM t1_values';
1061 --source suite/funcs_1/views/fv2.inc
1062 let $col_type= my_varchar_1000;
1063 eval SET @my_select = 'SELECT LOCATE(''char'', $col_type),
1064 $col_type, id FROM t1_values';
1065 --source suite/funcs_1/views/fv2.inc
1066 let $col_type= my_binary_30;
1067 eval SET @my_select = 'SELECT LOCATE(''char'', $col_type),
1068 $col_type, id FROM t1_values';
1069 --source suite/funcs_1/views/fv2.inc
1070 let $col_type= my_varbinary_1000;
1071 eval SET @my_select = 'SELECT LOCATE(''char'', $col_type),
1072 $col_type, id FROM t1_values';
1073 --source suite/funcs_1/views/fv2.inc
1074 #------------------------------------------------------
1075 let $col_type1= my_char_30;
1077 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type1 ),
1078 $col_type1, id FROM t1_values';
1079 --source suite/funcs_1/views/fv2.inc
1080 let $col_type2= my_varchar_1000;
1081 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1082 $col_type1, $col_type2 id FROM t1_values';
1083 --source suite/funcs_1/views/fv2.inc
1084 let $col_type2= my_binary_30;
1085 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1086 $col_type1, $col_type2 id FROM t1_values';
1087 --source suite/funcs_1/views/fv2.inc
1088 let $col_type2= my_varbinary_1000;
1089 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1090 $col_type1, $col_type2 id FROM t1_values';
1091 --source suite/funcs_1/views/fv2.inc
1092 #------------------------------------------------------
1093 let $col_type1= my_varchar_1000;
1095 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type1 ),
1096 $col_type1, id FROM t1_values';
1097 --source suite/funcs_1/views/fv2.inc
1098 let $col_type2= my_char_30;
1099 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1100 $col_type1, $col_type2 id FROM t1_values';
1101 --source suite/funcs_1/views/fv2.inc
1102 let $col_type2= my_binary_30;
1103 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1104 $col_type1, $col_type2 id FROM t1_values';
1105 --source suite/funcs_1/views/fv2.inc
1106 let $col_type2= my_varbinary_1000;
1107 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1108 $col_type1, $col_type2 id FROM t1_values';
1109 --source suite/funcs_1/views/fv2.inc
1110 #------------------------------------------------------
1111 let $col_type1= my_binary_30;
1113 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type1 ),
1114 $col_type1, id FROM t1_values';
1115 --source suite/funcs_1/views/fv2.inc
1116 let $col_type2= my_char_30;
1117 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1118 $col_type1, $col_type2 id FROM t1_values';
1119 --source suite/funcs_1/views/fv2.inc
1120 let $col_type2= my_varchar_1000;
1121 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1122 $col_type1, $col_type2 id FROM t1_values';
1123 --source suite/funcs_1/views/fv2.inc
1124 let $col_type2= my_varbinary_1000;
1125 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1126 $col_type1, $col_type2 id FROM t1_values';
1127 --source suite/funcs_1/views/fv2.inc
1128 #------------------------------------------------------
1129 let $col_type1= my_varbinary_1000;
1131 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type1 ),
1132 $col_type1, id FROM t1_values';
1133 --source suite/funcs_1/views/fv2.inc
1134 let $col_type2= my_char_30;
1135 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1136 $col_type1, $col_type2 id FROM t1_values';
1137 --source suite/funcs_1/views/fv2.inc
1138 let $col_type2= my_varchar_1000;
1139 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1140 $col_type1, $col_type2 id FROM t1_values';
1141 --source suite/funcs_1/views/fv2.inc
1142 let $col_type2= my_binary_30;
1143 eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
1144 $col_type1, $col_type2 id FROM t1_values';
1145 --source suite/funcs_1/views/fv2.inc
1147 # FIXME How to test exotic or interesting substr values like NULL, '', ' '
1148 # without getting too much result rows
1149 # FIXME Testcases with LOCATE(substr,str,pos)
1150 let $col_type= my_char_30;
1151 eval SET @my_select = 'SELECT LOCATE(''-'', $col_type, 3),
1152 $col_type, id FROM t1_values';
1153 --source suite/funcs_1/views/fv2.inc
1154 let $col_type= my_varchar_1000;
1155 eval SET @my_select = 'SELECT LOCATE(''-'', $col_type, 3),
1156 $col_type, id FROM t1_values';
1157 --source suite/funcs_1/views/fv2.inc
1158 let $col_type= my_binary_30;
1159 eval SET @my_select = 'SELECT LOCATE(''-'', $col_type, 3),
1160 $col_type, id FROM t1_values';
1161 --source suite/funcs_1/views/fv2.inc
1162 let $col_type= my_varbinary_1000;
1163 eval SET @my_select = 'SELECT LOCATE(''-'', $col_type, 3),
1164 $col_type, id FROM t1_values';
1165 --source suite/funcs_1/views/fv2.inc
1166 #--------------------------------------------------------
1167 let $col_type= my_bigint;
1168 eval SET @my_select = 'SELECT LOCATE(''-'', '' - -ABC'', $col_type),
1169 $col_type, id FROM t1_values';
1170 --source suite/funcs_1/views/fv2.inc
1171 let $col_type= my_double;
1172 eval SET @my_select = 'SELECT LOCATE(''-'', '' - -ABC'', $col_type),
1173 $col_type, id FROM t1_values';
1174 --source suite/funcs_1/views/fv2.inc
1175 let $col_type= my_decimal;
1176 eval SET @my_select = 'SELECT LOCATE(''-'', '' - -ABC'', $col_type),
1177 $col_type, id FROM t1_values';
1178 --source suite/funcs_1/views/fv2.inc
1182 let $col_type= my_char_30;
1183 eval SET @my_select = 'SELECT LOWER($col_type),
1184 $col_type, id FROM t1_values';
1185 --source suite/funcs_1/views/fv2.inc
1186 let $col_type= my_varchar_1000;
1187 eval SET @my_select = 'SELECT LOWER($col_type),
1188 $col_type, id FROM t1_values';
1189 --source suite/funcs_1/views/fv2.inc
1190 let $col_type= my_binary_30;
1191 eval SET @my_select = 'SELECT LOWER($col_type),
1192 $col_type, id FROM t1_values';
1193 --source suite/funcs_1/views/fv2.inc
1194 let $col_type= my_varbinary_1000;
1195 eval SET @my_select = 'SELECT LOWER($col_type),
1196 $col_type, id FROM t1_values';
1197 --source suite/funcs_1/views/fv2.inc
1200 # 3.24. LPAD(str,len,padstr)
1202 let $col_type= my_char_30;
1203 eval SET @my_select = 'SELECT LTRIM($col_type),
1204 $col_type, id FROM t1_values';
1205 --source suite/funcs_1/views/fv2.inc
1206 let $col_type= my_varchar_1000;
1207 eval SET @my_select = 'SELECT LTRIM($col_type),
1208 $col_type, id FROM t1_values';
1209 --source suite/funcs_1/views/fv2.inc
1210 let $col_type= my_binary_30;
1211 eval SET @my_select = 'SELECT LTRIM($col_type),
1212 $col_type, id FROM t1_values';
1213 --source suite/funcs_1/views/fv2.inc
1214 let $col_type= my_varbinary_1000;
1215 eval SET @my_select = 'SELECT LTRIM($col_type),
1216 $col_type, id FROM t1_values';
1217 --source suite/funcs_1/views/fv2.inc
1220 # 3.26. MAKE_SET(bits,str1,str2,...)
1222 # FIXME: to be implemented
1224 ################################################################################
1225 # Please do not add SELECTs and interesting records after this line. #
1226 # These last SELECTs are mostly for checking the testcase code itself. #
1227 ################################################################################
1228 eval SET @my_select =
1229 'SELECT CONCAT(''A'',my_char_30), my_char_30, id FROM t1_values'; #
1230 --source suite/funcs_1/views/fv1.inc
1232 eval SET @my_select = 'SELECT my_char_30, id FROM t1_values'; #
1233 --source suite/funcs_1/views/fv2.inc
1234 eval INSERT INTO t1_values SET select_id = @select_id,
1235 my_char_30 = 'Viana do Castelo';
1236 ################################################################################
1237 SET sql_mode = ''; #
1239 ##### END Basic preparations #######################################
1242 let $message= "# The basic preparations end and the main test starts here";
1243 --source include/show_msg80.inc
1245 --disable_ps_protocol
1247 ##### The tests start here #####################################################
1249 # Determine the number of different SELECTs to be checked
1251 SELECT COUNT(*) INTO @num_selects FROM t1_selects;
1254 # SELECT @num_selects AS "number of SELECTS:";
1256 --disable_abort_on_error
1257 let $select_id= `SELECT @num_selects`;
1260 # Determine the SELECT
1262 eval SELECT my_select, disable_result INTO @my_select, @disable_result
1263 FROM t1_selects WHERE id = $select_id;
1264 let $run_no_result= `SELECT @disable_result = 'Yes'`;
1267 # SELECT @my_select AS "SELECT:";
1268 let $my_select= `SELECT @my_select`;
1271 if ($view_select_result)
1274 --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
1275 eval CREATE VIEW v1 AS $my_select;
1277 eval set @got_errno= $mysql_errno ;
1278 let $run0= `SELECT @got_errno = 0`;
1283 --echo Attention: The last CREATE VIEW failed
1288 # FIXME The loop over the modes will start here.
1290 if ($simple_select_result)
1292 # Simple SELECT on the base table of the VIEW for comparison
1296 --disable_result_log
1298 --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
1300 WHERE select_id = $select_id OR select_id IS NULL order by id;
1308 --echo Attention: The last SELECT on the base table failed
1313 # $run0 is 1, if CREATE VIEW was successful.
1314 # That means SHOW CREATE VIEW/SELECT/DROP should be executed.
1317 # Check the CREATE VIEW statement
1318 --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
1319 SHOW CREATE VIEW v1;
1323 --echo Attention: The last SHOW CREATE VIEW failed
1327 # Maybe a Join is faster
1330 --disable_result_log
1332 eval SELECT v1.* FROM v1
1333 WHERE v1.id IN (SELECT id FROM t1_values
1334 WHERE select_id = $select_id OR select_id IS NULL) order by id;
1342 --echo Attention: The last SELECT from VIEW failed
1349 # FIXME The loop over the modes will end here.
1351 # Produce two empty lines as separator between different SELECTS
1359 --enable_ps_protocol
1361 DROP TABLE t1_selects, t1_modes, t1_values;