Strange results - Part 2
This is a follow up message to a earlier threat this week (which is included in the message below) Ok, here's the model table: Table,Create Table model,CREATE TABLE `model` ( `PID` tinyint(3) NOT NULL auto_increment, `VendorID` tinyint(4) NOT NULL default '0', `Model` varchar(15) NOT NULL default '', PRIMARY KEY (`PID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 The vendor table: Table,Create Table vendor,CREATE TABLE `vendor` ( `PID` int(11) NOT NULL auto_increment, `Vendor` varchar(25) NOT NULL default '', PRIMARY KEY (`PID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 And the specs table: Table,Create Table specs,CREATE TABLE `specs` ( `SpecID` int(11) NOT NULL auto_increment, `ProdModel` varchar(15) NOT NULL default '', `ProdImage` text NOT NULL, `ProdPrice` varchar(15) NOT NULL default '', `ProdStroke` varchar(8) NOT NULL default '', `ProdCC` varchar(7) NOT NULL default '', `ProdFuel` varchar(15) NOT NULL default '', `ProdCooling` varchar(40) NOT NULL default '', `ProdStarter` varchar(20) NOT NULL default '', `ProdIgnition` char(3) NOT NULL default '', `ProdTrans` varchar(35) NOT NULL default '', `ProdFSusp` varchar(45) NOT NULL default '', `ProdRSusp` varchar(35) NOT NULL default '', `ProdFBrake` varchar(45) NOT NULL default '', `ProdRBrake` varchar(25) NOT NULL default '', `ProdTireSize` varchar(25) NOT NULL default '', `ProdSpeed` varchar(15) NOT NULL default '', `ProdDimension` varchar(30) NOT NULL default '', `ProdWheelBase` char(3) NOT NULL default '', `ProdSeatHeight` char(3) NOT NULL default '', `ProdGauges` mediumtext NOT NULL, `ProdSafety` mediumtext NOT NULL, `ProdAntiTheft` mediumtext NOT NULL, `ProdWeight` varchar(10) NOT NULL default '', `ProdOption` mediumtext NOT NULL, `ProdPower` varchar(30) NOT NULL default '', `ProdColor` mediumtext NOT NULL, `ProdConsumption` varchar(11) NOT NULL default '', `ProdOilInject` char(3) NOT NULL default '', `ProdLoad` varchar(7) NOT NULL default '', `ProdCargo` varchar(8) NOT NULL default '', `ProdWarranty` varchar(35) NOT NULL default '', `ProdCarb` varchar(35) NOT NULL default '', `ProdCarbManu` varchar(6) NOT NULL default '', `ProdStorage` varchar(30) NOT NULL default '', PRIMARY KEY (`SpecID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Kevin A. Burton [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Steve Grosz wrote: If you can tell me the command to dump the table format, I'm more than happy to list it here. SHOW CREATE TABLE FOO; -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 I am still looking for assistance with the table problem. Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange results - Part 2
Roger Baklund wrote: [...] You are joining the model table on vendor.PID=model.VendorID, and model.VendorID is not a primary or unique key, it could contain duplicates. ... probably the four rows you want. This is ok. It's probably the other join that causes the problem. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange results - Part 2
Steve Grosz wrote: This is a follow up message to a earlier threat this week (which is included in the message below) model,CREATE TABLE `model` ( PRIMARY KEY (`PID`) vendor,CREATE TABLE `vendor` ( PRIMARY KEY (`PID`) specs,CREATE TABLE `specs` ( PRIMARY KEY (`SpecID`) Like Rhino suggested, you are joining on something other than your primary keys. Your query, slightly re-arranged for readability: select * from vendor left outer join model on vendor.PID=model.VendorID left outer join specs on model.Model=specs.ProdModel where vendor.Vendor='#URL.Vendor#' You are joining the model table on vendor.PID=model.VendorID, and model.VendorID is not a primary or unique key, it could contain duplicates. You are joining the specs table on model.Model=specs.ProdModel, neither is a primary key or unique, both could containt duplicates. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange results
I'm curious, why if I enter this code: select * from vendor left outer join model on vendor.PID=model.VendorID left outer join specs on model.Model=specs.ProdModel where vendor.Vendor='#URL.Vendor#' do I get the results like: http://www.scootervilleusa.com/test1.cfm?Vendor=Tank I simply want 1 row for each product to be displayed, not 4 sets of 4 rows. Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange results
If you can tell me the command to dump the table format, I'm more than happy to list it here. Steve Rhino wrote: - Original Message - From: Steve Grosz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, December 11, 2004 3:57 PM Subject: Strange results I'm curious, why if I enter this code: select * from vendor left outer join model on vendor.PID=model.VendorID left outer join specs on model.Model=specs.ProdModel where vendor.Vendor='#URL.Vendor#' do I get the results like: http://www.scootervilleusa.com/test1.cfm?Vendor=Tank I simply want 1 row for each product to be displayed, not 4 sets of 4 rows. It's hard to say for sure without seeing your table definitions and a sampling of the data in each table but I think there's a very good chance that either one of your tables doesn't have a primary key defined or that you are not joining on the primary key in one of your joins. Joins typically take place between a primary key on one table and a corresponding foreign key on the other table. The syntax of your joins seems fine so I can only suspect the two things I said above. For instance, if Vendor.PID is intended to be unique and you think of it as the primary key but it hasn't actually been defined that way in the table, it's very possible that this is causing your duplicate rows. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange results
Steve Grosz wrote: If you can tell me the command to dump the table format, I'm more than happy to list it here. SHOW CREATE TABLE FOO; -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange results
Ok, here's the model table: Table,Create Table model,CREATE TABLE `model` ( `PID` tinyint(3) NOT NULL auto_increment, `VendorID` tinyint(4) NOT NULL default '0', `Model` varchar(15) NOT NULL default '', PRIMARY KEY (`PID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 The vendor table: Table,Create Table vendor,CREATE TABLE `vendor` ( `PID` int(11) NOT NULL auto_increment, `Vendor` varchar(25) NOT NULL default '', PRIMARY KEY (`PID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 And the specs table: Table,Create Table specs,CREATE TABLE `specs` ( `SpecID` int(11) NOT NULL auto_increment, `ProdModel` varchar(15) NOT NULL default '', `ProdImage` text NOT NULL, `ProdPrice` varchar(15) NOT NULL default '', `ProdStroke` varchar(8) NOT NULL default '', `ProdCC` varchar(7) NOT NULL default '', `ProdFuel` varchar(15) NOT NULL default '', `ProdCooling` varchar(40) NOT NULL default '', `ProdStarter` varchar(20) NOT NULL default '', `ProdIgnition` char(3) NOT NULL default '', `ProdTrans` varchar(35) NOT NULL default '', `ProdFSusp` varchar(45) NOT NULL default '', `ProdRSusp` varchar(35) NOT NULL default '', `ProdFBrake` varchar(45) NOT NULL default '', `ProdRBrake` varchar(25) NOT NULL default '', `ProdTireSize` varchar(25) NOT NULL default '', `ProdSpeed` varchar(15) NOT NULL default '', `ProdDimension` varchar(30) NOT NULL default '', `ProdWheelBase` char(3) NOT NULL default '', `ProdSeatHeight` char(3) NOT NULL default '', `ProdGauges` mediumtext NOT NULL, `ProdSafety` mediumtext NOT NULL, `ProdAntiTheft` mediumtext NOT NULL, `ProdWeight` varchar(10) NOT NULL default '', `ProdOption` mediumtext NOT NULL, `ProdPower` varchar(30) NOT NULL default '', `ProdColor` mediumtext NOT NULL, `ProdConsumption` varchar(11) NOT NULL default '', `ProdOilInject` char(3) NOT NULL default '', `ProdLoad` varchar(7) NOT NULL default '', `ProdCargo` varchar(8) NOT NULL default '', `ProdWarranty` varchar(35) NOT NULL default '', `ProdCarb` varchar(35) NOT NULL default '', `ProdCarbManu` varchar(6) NOT NULL default '', `ProdStorage` varchar(30) NOT NULL default '', PRIMARY KEY (`SpecID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Kevin A. Burton [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Steve Grosz wrote: If you can tell me the command to dump the table format, I'm more than happy to list it here. SHOW CREATE TABLE FOO; -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange results
- Original Message - From: Steve Grosz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, December 11, 2004 3:57 PM Subject: Strange results I'm curious, why if I enter this code: select * from vendor left outer join model on vendor.PID=model.VendorID left outer join specs on model.Model=specs.ProdModel where vendor.Vendor='#URL.Vendor#' do I get the results like: http://www.scootervilleusa.com/test1.cfm?Vendor=Tank I simply want 1 row for each product to be displayed, not 4 sets of 4 rows. It's hard to say for sure without seeing your table definitions and a sampling of the data in each table but I think there's a very good chance that either one of your tables doesn't have a primary key defined or that you are not joining on the primary key in one of your joins. Joins typically take place between a primary key on one table and a corresponding foreign key on the other table. The syntax of your joins seems fine so I can only suspect the two things I said above. For instance, if Vendor.PID is intended to be unique and you think of it as the primary key but it hasn't actually been defined that way in the table, it's very possible that this is causing your duplicate rows. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange results from a query
In both mysql 4.0.20 and 4.1.4a-gamma, I get mysql CREATE TABLE master (col1 INT NOT NULL); Query OK, 0 rows affected (0.09 sec) mysql CREATE TABLE sub (col1 INT NOT NULL, a CHAR(1) NULL, b CHAR(1) NULL, c SMALLINT); Query OK, 0 rows affected (0.01 sec) mysql INSERT INTO MASTER (col1) VALUES (1), (2); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql INSERT INTO sub (col1, a, b, c) VALUES - (1, 'a', null, '2'), - (1, 'a', null, '2'), - (2, null, 'b', '3'), - (2, null, 'b', '3'); Query OK, 4 rows affected (0.07 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql SELECT m.col1, - SUM(s1.c) AS 'A-count', - SUM(s2.c) AS 'B-count' - FROM master m - LEFT JOIN sub s1 ON (m.col1 = s1.col1 AND s1.a is not null) - LEFT JOIN sub s2 ON (m.col1 = s2.col1 AND s2.b is not null) - GROUP BY m.col1; +--+-+-+ | col1 | A-count | B-count | +--+-+-+ |1 | 4 |NULL | |2 |NULL | 6 | +--+-+-+ 2 rows in set (0.07 sec) which is the correct result, just as Stephen did with 3.23.58. If I then INSERT INTO MASTER (col1) VALUES (1), (2); a second time, I then get +--+-+-+ | col1 | A-count | B-count | +--+-+-+ |1 | 8 |NULL | |2 |NULL | 12 | +--+-+-+ 2 rows in set (0.07 sec) I think you should double-check your data. In your original email, you listed two inserts to table master, then said you'd removed some data to make it clearer in your follow-up. I suspect your master table has 2 copies of each row. Michael David Griffiths wrote: Thanks, Stephen - that's interesting to know. David Stephen E. Bacher wrote: FYI, I ran the same scenario under MySQL 3.23.58 and it produced the results you wanted/expected: +--+-+-+ | col1 | A-count | B-count | +--+-+-+ |1 | 4 | 0 | |2 | 0 | 6 | +--+-+-+ So could it be a bug in 4.0.18? - seb --- David Griffiths [EMAIL PROTECTED] wrote: One of our developers came to me yesterday with strange results from a query. I've created a simple version of the example. I've pasted the table definitions at the bottom if someone really needs to see them. This is on mysql 4.0.18. insert into master (col1) values (1), (2); insert into sub (col1, a, b, c) values (1, 'a', null, '2'), (1, 'a', null, '2'), (2, null, 'b', '3'), (2, null, 'b', '3'); mysql select m.col1, - sum(s1.c) as 'A-count', - sum(s2.c) as 'B-count' - FROM master m - left join sub s1 on (m.col1 = s1.col1 AND s1.a is not null) - left join sub s2 on (m.col1 = s2.col1 and s2.b is not null) - group by m.col1; +--+-+-+ | col1 | A-count | B-count | +--+-+-+ |1 | 8 |NULL | |2 |NULL | 12 | +--+-+-+ 2 rows in set (0.00 sec) In case it's not obvious, the count for the column marked A should be 4, not 8. And for B, it should be 6, not 12. The database seems to be iterating through the table twice. If one of the outer-joins is removed, the results are correct. I would hazard a guess that if a third column existed in master/sub, and a third left-join was added, A would go to 12, and B would go to 16. Each outer join seems to spawn a new iteration through the data. My question is why, and what would be the strategy to avoid this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange results from a query
FYI, I ran the same scenario under MySQL 3.23.58 and it produced the results you wanted/expected: +--+-+-+ | col1 | A-count | B-count | +--+-+-+ |1 | 4 | 0 | |2 | 0 | 6 | +--+-+-+ So could it be a bug in 4.0.18? - seb --- David Griffiths [EMAIL PROTECTED] wrote: One of our developers came to me yesterday with strange results from a query. I've created a simple version of the example. I've pasted the table definitions at the bottom if someone really needs to see them. This is on mysql 4.0.18. insert into master (col1) values (1), (2); insert into sub (col1, a, b, c) values (1, 'a', null, '2'), (1, 'a', null, '2'), (2, null, 'b', '3'), (2, null, 'b', '3'); mysql select m.col1, - sum(s1.c) as 'A-count', - sum(s2.c) as 'B-count' - FROM master m - left join sub s1 on (m.col1 = s1.col1 AND s1.a is not null) - left join sub s2 on (m.col1 = s2.col1 and s2.b is not null) - group by m.col1; +--+-+-+ | col1 | A-count | B-count | +--+-+-+ |1 | 8 |NULL | |2 |NULL | 12 | +--+-+-+ 2 rows in set (0.00 sec) In case it's not obvious, the count for the column marked A should be 4, not 8. And for B, it should be 6, not 12. The database seems to be iterating through the table twice. If one of the outer-joins is removed, the results are correct. I would hazard a guess that if a third column existed in master/sub, and a third left-join was added, A would go to 12, and B would go to 16. Each outer join seems to spawn a new iteration through the data. My question is why, and what would be the strategy to avoid this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange results from a query
Thanks, Stephen - that's interesting to know. David Stephen E. Bacher wrote: FYI, I ran the same scenario under MySQL 3.23.58 and it produced the results you wanted/expected: +--+-+-+ | col1 | A-count | B-count | +--+-+-+ |1 | 4 | 0 | |2 | 0 | 6 | +--+-+-+ So could it be a bug in 4.0.18? - seb --- David Griffiths [EMAIL PROTECTED] wrote: One of our developers came to me yesterday with strange results from a query. I've created a simple version of the example. I've pasted the table definitions at the bottom if someone really needs to see them. This is on mysql 4.0.18. insert into master (col1) values (1), (2); insert into sub (col1, a, b, c) values (1, 'a', null, '2'), (1, 'a', null, '2'), (2, null, 'b', '3'), (2, null, 'b', '3'); mysql select m.col1, - sum(s1.c) as 'A-count', - sum(s2.c) as 'B-count' - FROM master m - left join sub s1 on (m.col1 = s1.col1 AND s1.a is not null) - left join sub s2 on (m.col1 = s2.col1 and s2.b is not null) - group by m.col1; +--+-+-+ | col1 | A-count | B-count | +--+-+-+ |1 | 8 |NULL | |2 |NULL | 12 | +--+-+-+ 2 rows in set (0.00 sec) In case it's not obvious, the count for the column marked A should be 4, not 8. And for B, it should be 6, not 12. The database seems to be iterating through the table twice. If one of the outer-joins is removed, the results are correct. I would hazard a guess that if a third column existed in master/sub, and a third left-join was added, A would go to 12, and B would go to 16. Each outer join seems to spawn a new iteration through the data. My question is why, and what would be the strategy to avoid this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange results from a query
One of our developers came to me yesterday with strange results from a query. I've created a simple version of the example. I've pasted the table definitions at the bottom if someone really needs to see them. This is on mysql 4.0.18. insert into master (col1) values (1), (2); insert into sub (col1, a, b, c) values (1, 'a', null, '2'), (1, 'a', null, '2'), (2, null, 'b', '3'), (2, null, 'b', '3'); mysql select m.col1, - sum(s1.c) as 'A-count', - sum(s2.c) as 'B-count' - FROM master m - left join sub s1 on (m.col1 = s1.col1 AND s1.a is not null) - left join sub s2 on (m.col1 = s2.col1 and s2.b is not null) - group by m.col1; +--+-+-+ | col1 | A-count | B-count | +--+-+-+ |1 | 8 |NULL | |2 |NULL | 12 | +--+-+-+ 2 rows in set (0.00 sec) In case it's not obvious, the count for the column marked A should be 4, not 8. And for B, it should be 6, not 12. The database seems to be iterating through the table twice. If one of the outer-joins is removed, the results are correct. I would hazard a guess that if a third column existed in master/sub, and a third left-join was added, A would go to 12, and B would go to 16. Each outer join seems to spawn a new iteration through the data. My question is why, and what would be the strategy to avoid this? Here are the table defs: create table master (col1 int not null); create table sub (col1 int not null, a char(1) null, b char(1) null, c smallint); insert into master (col1) values (1), (2), (3); Thanks, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange results from a query
Before I think about this, which is it? insert into master (col1) values (1), (2); or insert into master (col1) values (1), (2), (3); Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange results from a query
Sorry - removed some data to make it clearer. insert into master (col1) values (1), (2); is correct. David Michael Stassen wrote: Before I think about this, which is it? insert into master (col1) values (1), (2); or insert into master (col1) values (1), (2), (3); Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very simple query but strange results
- Original Message -
From: Kapoor, Nishikant [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, August 30, 2004 2:41 PM
Subject: very simple query but strange results
This little sql has me puzzled. Would appreciate your help.
mysql drop table if exists T;
Query OK, 0 rows affected (0.00 sec)
mysql create table T (title text, fulltext(title)) type=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql insert into T values ('01 test'), ('test resource');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql -- not working
mysql SELECT title FROM T WHERE MATCH(title) AGAINST ('test');
Empty set (0.00 sec)
Why 'Empty set'? I expect to see two rows. What am I missing?
Thanks
Nishi
mysqladmin Ver 8.40 Distrib 4.0.15, for mandrake-linux-gnu on i586
Server version 4.0.15
Protocol version10
I'm not sure.
I haven't worked with fulltext searches myself but your question intrigued
me. I tried creating the same table you did and then ran the same query: I
had the same result you did.
I went to this page of the MySQL manual
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html, but couldn't see any
obvious error in your query based on the rules I saw there. I tried setting
up the table that they used in their examples and it gave the same result
that they predicted.
The big difference between your example and theirs was that their example
had the fulltext() function applied to TWO columns, title and body, while
yours applied only to title.
I wonder if there is an unstated rule that the match() function must always
be used against at least two columns? That strikes me as a very odd design
for a function so it doesn't seem likely. However, I am at a loss to suggest
another explanation.
Perhaps someone with more experience with MATCH() or one of the MySQL
developers could enlighten us on the correct technique for searching a
single fulltext column.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very simple query but strange results
Rhino wrote:
- Original Message -
From: Kapoor, Nishikant [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, August 30, 2004 2:41 PM
Subject: very simple query but strange results
This little sql has me puzzled. Would appreciate your help.
mysql drop table if exists T;
Query OK, 0 rows affected (0.00 sec)
mysql create table T (title text, fulltext(title)) type=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql insert into T values ('01 test'), ('test resource');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql -- not working
mysql SELECT title FROM T WHERE MATCH(title) AGAINST ('test');
Empty set (0.00 sec)
Why 'Empty set'? I expect to see two rows. What am I missing?
Thanks
Nishi
mysqladmin Ver 8.40 Distrib 4.0.15, for mandrake-linux-gnu on i586
Server version 4.0.15
Protocol version10
I'm not sure.
I haven't worked with fulltext searches myself but your question intrigued
me. I tried creating the same table you did and then ran the same query: I
had the same result you did.
I went to this page of the MySQL manual
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html, but couldn't see any
obvious error in your query based on the rules I saw there. I tried setting
up the table that they used in their examples and it gave the same result
that they predicted.
The big difference between your example and theirs was that their example
had the fulltext() function applied to TWO columns, title and body, while
yours applied only to title.
I wonder if there is an unstated rule that the match() function must always
be used against at least two columns? That strikes me as a very odd design
for a function so it doesn't seem likely. However, I am at a loss to suggest
another explanation.
Perhaps someone with more experience with MATCH() or one of the MySQL
developers could enlighten us on the correct technique for searching a
single fulltext column.
Rhino
The answer is in the middle of that document. Words which appear in 50% or
more of your rows become stopwords, because they aren't very useful for
narrowing your search. One consequence is that you must have at least 3
rows in a table before full-text indexing will find anything. Full-text
indexing is designed for large collections, not tiny test tables.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very simple query but strange results
I skimmed the whole article twice, including the user comments, and still
missed that :-(
Thanks for catching that Michael! That explanation makes a lot of sense, a
lot more sense than forcing there to be at least two columns in the
fulltext() column.
Rhino
- Original Message -
From: Michael Stassen [EMAIL PROTECTED]
To: Rhino [EMAIL PROTECTED]
Cc: Kapoor, Nishikant [EMAIL PROTECTED];
[EMAIL PROTECTED]
Sent: Monday, August 30, 2004 7:12 PM
Subject: Re: very simple query but strange results
Rhino wrote:
- Original Message -
From: Kapoor, Nishikant [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, August 30, 2004 2:41 PM
Subject: very simple query but strange results
This little sql has me puzzled. Would appreciate your help.
mysql drop table if exists T;
Query OK, 0 rows affected (0.00 sec)
mysql create table T (title text, fulltext(title)) type=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql insert into T values ('01 test'), ('test resource');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql -- not working
mysql SELECT title FROM T WHERE MATCH(title) AGAINST ('test');
Empty set (0.00 sec)
Why 'Empty set'? I expect to see two rows. What am I missing?
Thanks
Nishi
mysqladmin Ver 8.40 Distrib 4.0.15, for mandrake-linux-gnu on i586
Server version 4.0.15
Protocol version10
--
--
I'm not sure.
I haven't worked with fulltext searches myself but your question
intrigued
me. I tried creating the same table you did and then ran the same query:
I
had the same result you did.
I went to this page of the MySQL manual
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html, but couldn't see
any
obvious error in your query based on the rules I saw there. I tried
setting
up the table that they used in their examples and it gave the same
result
that they predicted.
The big difference between your example and theirs was that their
example
had the fulltext() function applied to TWO columns, title and body,
while
yours applied only to title.
I wonder if there is an unstated rule that the match() function must
always
be used against at least two columns? That strikes me as a very odd
design
for a function so it doesn't seem likely. However, I am at a loss to
suggest
another explanation.
Perhaps someone with more experience with MATCH() or one of the MySQL
developers could enlighten us on the correct technique for searching a
single fulltext column.
Rhino
The answer is in the middle of that document. Words which appear in 50%
or
more of your rows become stopwords, because they aren't very useful for
narrowing your search. One consequence is that you must have at least 3
rows in a table before full-text indexing will find anything. Full-text
indexing is designed for large collections, not tiny test tables.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange results
Hey folks - can anyone explain this behaviour: mysql select token, spam_hits, innocent_hits from dspam_token_data where uid=500 and token=14243385100413148122; Returns this result, which is a completely different record. +---+-++ | token | spam_hits | innocent_hits | +---+-++ | 14243385100413147136 | 0 | 1 | +---+-++ ?? --Jo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange results
Is that a character field? If it is, you forgot the quotes, and the string was converted to a number. 16 significant digits match. Joakim Ryden wrote: Hey folks - can anyone explain this behaviour: mysql select token, spam_hits, innocent_hits from dspam_token_data where uid=500 and token=14243385100413148122; Returns this result, which is a completely different record. +---+-++ | token | spam_hits | innocent_hits | +---+-++ | 14243385100413147136 | 0 | 1 | +---+-++ ?? --Jo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange results
It's a BIGINT(25). On Tuesday 21 October 2003 11:58 am, gerald_clark wrote: Is that a character field? If it is, you forgot the quotes, and the string was converted to a number. 16 significant digits match. Joakim Ryden wrote: Hey folks - can anyone explain this behaviour: mysql select token, spam_hits, innocent_hits from dspam_token_data where uid=500 and token=14243385100413148122; Returns this result, which is a completely different record. +---+-++ | token | spam_hits | innocent_hits | +---+-++ | 14243385100413147136 | 0 | 1 | +---+-++ ?? --Jo !DSPAM:3f9581bb310893224619726! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange results
On Tue, 21 Oct 2003 11:58:57 -0700, Joakim Ryden [EMAIL PROTECTED] wrote: It's a BIGINT(25). Well there you go. Your number is too big for the field. BIGINT(25) can (theoretically) DISPLAY up to 25 digits, but it is still bounded by MySQL's internal limits, as documented here: http://www.mysql.com/doc/en/Numeric_types.html TypeBytes MinimumMaximum BIGINT8-9223372036854775808 9223372036854775807 Your number is bigger: 14243385100413147136 9223372036854775807 Behavior beyond the maximum is undefined, and it looks like you are hitting that undefined behavior. Unless you are actually doing numeric things with this token, you might want to store it as a string. -Bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SOLVED Re: Strange results
This was indeed the problem. See below. It turns out MySQL's BIGINT doesn't handle all that big of an INT. :-/ Many thanks to bluejack. --Jo On Tuesday 21 October 2003 12:51 pm, bluejack wrote: On Tue, 21 Oct 2003 11:58:57 -0700, Joakim Ryden [EMAIL PROTECTED] wrote: It's a BIGINT(25). Well there you go. Your number is too big for the field. BIGINT(25) can (theoretically) DISPLAY up to 25 digits, but it is still bounded by MySQL's internal limits, as documented here: http://www.mysql.com/doc/en/Numeric_types.html TypeBytes MinimumMaximum BIGINT8-9223372036854775808 9223372036854775807 Your number is bigger: 14243385100413147136 9223372036854775807 Behavior beyond the maximum is undefined, and it looks like you are hitting that undefined behavior. Unless you are actually doing numeric things with this token, you might want to store it as a string. -Bluejack !DSPAM:3f958e4f36899526667875! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Union and Order By give strange results in Mysql 4.0.13
Neculai Macarie wrote: [] select 'gallery' as table_name, d_image_small, d_image_big from gallery UNION select 'gallery_categ' as table_name, d_image, NULL from gallery_categ order by table_name; [] select 'gallery' as table_name, d_image_small, d_image_big from gallery union select 'categ' as table_name, d_image, NULL from gallery_categ order by table_name; Making a twist to the samples provided select 'categ' as table_name, d_image, '.' as big from gallery_categ union select 'gallery' as table_name, d_image_small , d_image_big from gallery order by table_name; the results are amazing 'table_name','d_image','big' 'categ','img22.jpg','.' 'categ','img23.jpg','.' 'categ','img21.jpg','.' 'galle','img3.jpg','i' 'galle','img6.jpg','i' 'galle','img1.jpg','i' To my observations constants in a column declaration limit the column width to just fit the initial constant. Your choice of values 'gallary' and 'gallery-categ' just masked that out ;-) Yes, you are right. Based on your observation I was able to trick him with this query: select Trim( 'gallery' ) as table_name, d_image_small, d_image_big from gallery UNION select 'gallery_categ' as table_name, d_image, NULL from gallery_categ order by table_name; mack / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Union and Order By give strange results in Mysql 4.0.13
Neculai Macarie wrote: To my observations constants in a column declaration limit the column width to just fit the initial constant. Your choice of values 'gallary' and 'gallery-categ' just masked that out ;-) Yes, you are right. Based on your observation I was able to trick him with this query: select Trim( 'gallery' ) as table_name, d_image_small, d_image_big from gallery UNION select 'gallery_categ' as table_name, d_image, NULL from gallery_categ order by table_name; Well, it works for you, but preserving the original length don't feel logical. I fear this might change without notice. Come to think of select 'gallery ' as table_name, .. Yes, MySQL silently trims the trailing spaces for you!! HansH -- MySQL 4.1.0-alpha-max-nt on Windows XP using MySLQcc 0.9.2.B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Union and Order By give strange results in Mysql 4.0.13
Hi!
Using Union and Order By gives strange behaviour in the following test-case:
drop table if exists gallery;
drop table if exists gallery_categ;
# create test tables
create table gallery (d_image_small char(100), d_image_big char(100));
create table gallery_categ (d_image char(100));
# insert test data
insert into gallery(d_image_small, d_image_big) values('img1.jpg',
'img2.jpg');
insert into gallery(d_image_small, d_image_big) values(img3.jpg,
img4.jpg);
insert into gallery(d_image_small, d_image_big) values(img6.jpg,
img5.jpg);
insert into gallery_categ(d_image) values(img21.jpg);
insert into gallery_categ(d_image) values(img22.jpg);
insert into gallery_categ(d_image) values(img23.jpg);
This query
select 'gallery' as table_name, d_image_small, d_image_big
from gallery
UNION
select 'gallery_categ' as table_name, d_image, NULL
from gallery_categ
order by table_name;
returns this:
++---+-+
| table_name | d_image_small | d_image_big |
++---+-+
| gallery| img21.jpg | NULL |
| gallery| img6.jpg | img5.jpg|
| gallery| img3.jpg | img4.jpg|
| gallery| img1.jpg | img2.jpg|
| gallery| img23.jpg | NULL|
| gallery| img22.jpg | NULL|
++---+-+
Which is wrong, because the table_name field has the same value for both
tables.
But the following query works:
select 'gallery' as table_name, d_image_small, d_image_big
from gallery
union
select 'categ' as table_name, d_image, NULL
from gallery_categ
order by table_name;
++---+-+
| table_name | d_image_small | d_image_big |
++---+-+
| categ | img21.jpg | NULL|
| categ | img23.jpg | NULL|
| categ | img22.jpg | NULL|
| gallery| img6.jpg | img5.jpg|
| gallery| img3.jpg | img4.jpg|
| gallery| img1.jpg | img2.jpg|
++---+-+
mack /
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Union and Order By give strange results in Mysql 4.0.13
Neculai Macarie wrote: [] select 'gallery' as table_name, d_image_small, d_image_big from gallery UNION select 'gallery_categ' as table_name, d_image, NULL from gallery_categ order by table_name; [] select 'gallery' as table_name, d_image_small, d_image_big from gallery union select 'categ' as table_name, d_image, NULL from gallery_categ order by table_name; Making a twist to the samples provided select 'categ' as table_name, d_image, '.' as big from gallery_categ union select 'gallery' as table_name, d_image_small , d_image_big from gallery order by table_name; the results are amazing 'table_name','d_image','big' 'categ','img22.jpg','.' 'categ','img23.jpg','.' 'categ','img21.jpg','.' 'galle','img3.jpg','i' 'galle','img6.jpg','i' 'galle','img1.jpg','i' To my observations constants in a column declaration limit the column width to just fit the initial constant. Your choice of values 'gallary' and 'gallery-categ' just masked that out ;-) A workaround can be found in creating an additional table -never to hold data- create table dummy ( _name char(25), _small char(100), _big char(100)); and selecting it upfront of the unions to the filled tables select * from dummy union select 'categ' , d_image, null from gallery_categ union select 'gallery' , d_image_small , d_image_big from gallery order by _name; BTW I use this kind of a dummy table just to easy (re)name collumns in a union-ed resultset: not a simngle 'as' in any select. HansH -- Running 4.1.0-alpha-max-nt On WindowsXP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange results from query
I'm using the following query SELECT DISTINCT a.addrdsp,a.listdate,a.solddate,a.lpricea,a.sprice FROM archive a, archive b WHERE a.status='s' AND a.addrdsp IS NOT NULL AND a.addrdsp = b.addrdsp AND a.solddate b.solddate AND date_add(a.solddate, interval 1 year) b.solddate ORDER BY a.addrdsp, a.solddate LIMIT 200; I'm trying to look at about 300,000 rows of property data. I'm interested in knowing which properties have been sold 2 or more times within a 1 year period. However, when I run the query, I get 100 Brazeau Cresc SW and 100 Bridlewood Road SW in my list of properties--even though they have not had 2 or more 'sales' within a period of one year. I need to limit those properties which only occur once in the table. Also, even though all of the fields are indexed, the query takes up to 12 minutes to complete! Thanks David - 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 results from query
I'm using the following query SELECT DISTINCT a.addrdsp,a.listdate,a.solddate,a.lpricea,a.sprice FROM archive a, archive b WHERE a.status='s' AND a.addrdsp IS NOT NULL AND a.addrdsp = b.addrdsp AND a.solddate b.solddate AND date_add(a.solddate, interval 1 year) b.solddate ORDER BY a.addrdsp, a.solddate LIMIT 200; I'm trying to look at about 300,000 rows of property data. I'm interested in knowing which properties have been sold 2 or more times within a 1 year period. However, when I run the query, I get 100 Brazeau Cresc SW and 100 Bridlewood Road SW in my list of properties--even though they have not had 2 or more 'sales' within a period of one year. I need to limit those properties which only occur once in the table. Shouldn't you require b.status to be 's' as well? Also, even though all of the fields are indexed, the query takes up to 12 minutes to complete! I'm not surprized -- you're forcing MySQL to calculate date_add on every single one of the 300,000 rows. Indexing doesn't help you there. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - 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: Fulltext Strange Results...
Do you know roughly when this will be done.
*
* Visit http://www.computerstaff.net - Computer Jobs at all LEVELS *
*
-Original Message-
From: Sergei Golubchik [mailto:[EMAIL PROTECTED]]
Sent: 20 May 2001 22:44
To: technical Support
Subject: Re: Fulltext Strange Results...
Hi!
On May 20, technical Support wrote:
I have just done this:
select * from indexed_table where match(indexed_col)
against ('visual basic')
This returns rows with either visual or basic. I read
phrase searching was allowed in mySQL v4.0?
It's still in todo.
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
Fulltext Strange Results...
Hello,
I did the following query against my indexed table:
select * from indexed_table where match(indexed_col)
against ('oracle +sybase +london')
It returned 16 rows of which only 4 rows included all words.
I then did:
select * from indexed_table where match(indexed_col)
against ('+oracle +sybase +london')
It returned 4 rows which is the desired or expected results.
I then did:
select * from indexed_table where match(indexed_col)
against ('+oracle +sybase +london -oracle')
Nothing was returned.
I then went on to do this:
select * from indexed_table where match(indexed_col)
against ('oracle and sybase and london')
This returned over 200 rows rather than the expected 4.
This was done against mySQL v4.0.
Can someone please explain the ambiguities.
Joe B.
*
* Visit http://www.computerstaff.net - Computer Jobs at all LEVELS *
*
-
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
