Re: strange fulltext behavior
Hi!
On May 23, Przemyslaw Popielarski wrote:
Sergei Golubchik [EMAIL PROTECTED] wrote:
It looks like a bug. Can you create a test case for this ?
create table tBooks (
isbn char(10) not null primary key,
title varchar(60) not null,
fulltext index (title)
);
insert into tBooks (isbn,title) values
('1876340436','2000 Lonely Planet Calendar');
insert into tBooks (isbn,title) values
('0852297904','Britannica 2002 on CD-ROM Expanded ed');
SELECT isbn,title FROM tBooks
WHERE tBooks.isbn=1876340436
AND MATCH (tBooks.title) AGAINST (britannica)
++-+
| isbn | title |
++-+
| 1876340436 | 2000 Lonely Planet Calendar |
++-+
1 row in set (0.00 sec)
Works fine for me (that is No rows - and no bug).
Probably, it's one of numerous fulltext-related bugs that were fixed
since 4.0.1 release.
Regards,
Sergei
--
MySQL Development Team
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: strange fulltext behavior
Sergei Golubchik [EMAIL PROTECTED] wrote: 1 row in set (0.00 sec) Works fine for me (that is No rows - and no bug). Probably, it's one of numerous fulltext-related bugs that were fixed since 4.0.1 release. Will you fix it in 3.23.x ? There is still lot of time (I think) to release 4.x final. -- ./ premax ./ [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: strange fulltext behavior
Przemyslaw, Wednesday, May 22, 2002, 2:02:52 AM, you wrote: PP SELECT ISBN,TITLE FROM tBooks PP WHERE tBooks.ISBN=1876340436 PP AND MATCH (tBooks.TITLE) AGAINST (britannica) PP ++-+ PP | ISBN | TITLE | PP ++-+ PP | 1876340436 | 2000 Lonely Planet Calendar | PP ++-+ PP 1 row in set (0.00 sec) PP Why is it so? I suspected to get an empty result set. PP MYSQL Ver 11.16 Distrib 3.23.49, for Win95/Win98 (i32) MATCH() returnes you relevance value. So, it is always true in WHERE clause. As a result you get result where ISBN=1876340436. In your case you can re-write you query: SELECT ISBN,TITLE, MATCH (tBooks.TITLE) AGAINST (britannica) as aa FROM tBooks WHERE tBooks.ISBN=1876340436 HAVING aa0; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: strange fulltext behavior
Hi! On May 22, Victoria Reznichenko wrote: Przemyslaw, Wednesday, May 22, 2002, 2:02:52 AM, you wrote: PP SELECT ISBN,TITLE FROM tBooks PP WHERE tBooks.ISBN=1876340436 PP AND MATCH (tBooks.TITLE) AGAINST (britannica) PP ++-+ PP | ISBN | TITLE | PP ++-+ PP | 1876340436 | 2000 Lonely Planet Calendar | PP ++-+ PP 1 row in set (0.00 sec) PP Why is it so? I suspected to get an empty result set. PP MYSQL Ver 11.16 Distrib 3.23.49, for Win95/Win98 (i32) It looks like a bug. Can you create a test case for this ? MATCH() returnes you relevance value. So, it is always true in WHERE clause. As a result you get result where ISBN=1876340436. In your case you can re-write you query: SELECT ISBN,TITLE, MATCH (tBooks.TITLE) AGAINST (britannica) as aa FROM tBooks WHERE tBooks.ISBN=1876340436 HAVING aa0; Relevance value of 0 is treated as false. Adding HAVING should not really help. It even makes the query slower, as MySQL cannot use fulltext index to resolve the query. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: strange fulltext behavior
Sergei Golubchik [EMAIL PROTECTED] wrote:
It looks like a bug. Can you create a test case for this ?
create table tBooks (
isbn char(10) not null primary key,
title varchar(60) not null,
fulltext index (title)
);
insert into tBooks (isbn,title) values
('1876340436','2000 Lonely Planet Calendar');
insert into tBooks (isbn,title) values
('0852297904','Britannica 2002 on CD-ROM Expanded ed');
SELECT isbn,title FROM tBooks
WHERE tBooks.isbn=1876340436
AND MATCH (tBooks.title) AGAINST (britannica)
++-+
| isbn | title |
++-+
| 1876340436 | 2000 Lonely Planet Calendar |
++-+
1 row in set (0.00 sec)
mysql Ver 11.16 Distrib 3.23.49, for Win95/Win98 (i32)
Windows 98 SE
--
./ premax
./ [EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: strange fulltext behavior
This simply can't be correct - if a match produces 0 score (no relevance), the whole condition part after AND in this example could be reduced to 0 (=FALSE), hence the query *should* produce 0 records: SELECT ISBN,TITLE FROM tBooks WHERE tBooks.ISBN=1876340436 AND 0; ...and here is a real-life example of the correct behavior: mysql select indatetime from archivel where addr=FN20 and match (header) against (not_a_word); Empty set (0.00 sec) mysql select indatetime from archivel where addr=FN25 and match (header) against (reagan); ++ | 1988120101 | | 19990610115359 | | 2515123714 | | 1988112201 | | 1988112201 | ++ 5 rows in set (0.08 sec) Regards, Vadim P. Victoria Reznichenko wrote: Przemyslaw, Wednesday, May 22, 2002, 2:02:52 AM, you wrote: PP SELECT ISBN,TITLE FROM tBooks PP WHERE tBooks.ISBN=1876340436 PP AND MATCH (tBooks.TITLE) AGAINST (britannica) PP ++-+ PP | ISBN | TITLE | PP ++-+ PP | 1876340436 | 2000 Lonely Planet Calendar | PP ++-+ PP 1 row in set (0.00 sec) PP Why is it so? I suspected to get an empty result set. PP MYSQL Ver 11.16 Distrib 3.23.49, for Win95/Win98 (i32) MATCH() returnes you relevance value. So, it is always true in WHERE clause. As a result you get result where ISBN=1876340436. In your case you can re-write you query: SELECT ISBN,TITLE, MATCH (tBooks.TITLE) AGAINST (britannica) as aa FROM tBooks WHERE tBooks.ISBN=1876340436 HAVING aa0; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
strange fulltext behavior
SELECT ISBN,TITLE FROM tBooks WHERE tBooks.ISBN=1876340436 AND MATCH (tBooks.TITLE) AGAINST (britannica) ++-+ | ISBN | TITLE | ++-+ | 1876340436 | 2000 Lonely Planet Calendar | ++-+ 1 row in set (0.00 sec) Why is it so? I suspected to get an empty result set. MYSQL Ver 11.16 Distrib 3.23.49, for Win95/Win98 (i32) -- ./ premax ./ [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
