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;