1############## mysql-test\t\ft_boolean_syntax_func.test  ######################
2#                                                                             #
3# Variable Name: ft_boolean_syntax                                            #
4# Scope: GLOBAL                                                               #
5# Access Type: Dynamic                                                        #
6# Data Type: string                                                           #
7# Default Value: + -><()~*:""&                                                #
8# Range: NA                                                                   #
9#                                                                             #
10#                                                                             #
11# Creation Date: 2008-03-08                                                   #
12# Author:  Rizwan                                                             #
13#                                                                             #
14# Description: Test Cases of Dynamic System Variable ft_boolean_syntax        #
15#              that checks the behavior of this variable                      #
16#                                                                             #
17# Reference: http://dev.mysql.com/doc/refman/5.1/en/                          #
18#  server-system-variables.html                                               #
19#                                                                             #
20###############################################################################
21--echo '#--------------------FN_DYNVARS_033_01-------------------------#'
22###################################################################
23# Check if setting ft_boolean_syntax is changed in new connection #
24###################################################################
25
26SET @@global.ft_boolean_syntax = ' -+()<>~*:``&|';
27# con1 will be default connection from now on
28--echo 'connect (con1,localhost,root,,,,)'
29connect (con1,localhost,root,,,,);
30--echo 'connection con1'
31connection con1;
32SELECT @@global.ft_boolean_syntax;
33SET @@global.ft_boolean_syntax = '+ -><()~*:""&|';
34--echo 'connect (con2,localhost,root,,,,)'
35connect (con2,localhost,root,,,,);
36--echo 'connection con2'
37connection con2;
38SELECT @@global.ft_boolean_syntax;
39disconnect con2;
40disconnect con1;
41
42--echo '#--------------------FN_DYNVARS_033_02-------------------------#'
43#########################################################
44# Begin the functionality Testing of ft_boolean_syntax  #
45#########################################################
46
47--echo 'connection default'
48connection default;
49
50--disable_warnings
51DROP TABLE IF EXISTS t1;
52--enable_warnings
53
54CREATE TABLE articles (
55   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
56   title VARCHAR(200),
57   body TEXT,
58   FULLTEXT (title,body)
59);
60
61INSERT INTO articles (title,body) VALUES
62('MySQL Tutorial','DBMS stands for DataBase ...'),
63('How To',''),
64('How To Use MySQL Well','After you went through a ...'),
65('Optimizing MySQL','In this tutorial we will show .... Run command line ...'),
66('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
67('100 Tips for Myisam','1. Myisam is faster than innodb 2. Tricks and Tips for Myisam...'),
68('MySQL vs. YourSQL','In the following database comparison ...'),
69('MySQL Security','When configured properly, MySQL ...'),
70('Database Security','Configuring MySQL for ...');
71
72SET @@global.ft_boolean_syntax = DEFAULT;
73
74SELECT * FROM articles WHERE MATCH (title,body)
75AGAINST ('+mySQL -yourSQL' IN BOOLEAN MODE);
76
77SELECT * FROM articles WHERE MATCH (title,body)
78AGAINST ('+MySQL +YourSQL' IN BOOLEAN MODE);
79
80SELECT * FROM articles WHERE MATCH (title,body)
81AGAINST ('MySQL' IN BOOLEAN MODE);
82
83SELECT * FROM articles WHERE MATCH (title,body)
84AGAINST ('mysql tutorial dbms' IN BOOLEAN MODE);
85
86SELECT id,title,body, (MATCH (title,body)
87AGAINST ('+security configuring' IN BOOLEAN MODE)) AS relevance
88FROM articles WHERE MATCH (title,body)
89AGAINST ('+security configuring' IN BOOLEAN MODE);
90
91SELECT * FROM articles WHERE MATCH (title,body)
92AGAINST ('"faster than"' IN BOOLEAN MODE);
93
94SELECT * FROM articles WHERE MATCH (title,body)
95AGAINST ('+tutorial ~line' IN BOOLEAN MODE);
96
97SELECT * FROM articles WHERE MATCH (title,body)
98AGAINST ('10*' IN BOOLEAN MODE);
99
100SELECT id,title,body, (MATCH (title,body)
101AGAINST ('+MySQL +(>show <dbms)' IN BOOLEAN MODE)) AS relevance
102FROM articles WHERE MATCH (title,body)
103AGAINST ('+MySQL +(>show <dbms)' IN BOOLEAN MODE)
104ORDER BY relevance DESC;
105
106#==============================================================================
107--echo '---try setting different operators. Default '+ -><()~*:""&|'--'
108#==============================================================================
109SET @@global.ft_boolean_syntax='~ /!@#$%^&*()-';
110SELECT * FROM articles WHERE MATCH (title,body)
111AGAINST ('~mySQL /yourSQL' IN BOOLEAN MODE);
112
113#restore default
114SET @@global.ft_boolean_syntax=DEFAULT;
115
116#########################################################
117# End of functionality Testing for ft_boolean_syntax    #
118#########################################################
119DROP TABLE articles;
120
121