Hi Scott, I think you've been lucky so far... As you're only ordering on listdate, which is the same for both homes in your example you might definitely get different results once you put in the limit. A basic thing about rdb's is that you must never make assumptions that it returns resultsets in the same order unless you specify what to order by, of course quite often you'll get it back in the same order but you must never bank on it. I guess what happens in your case is that once you put in an extra LEFT JOIN your query gets optimised differently and tables are read in a different order which gives you a different reslutset. I guess you somehow have to include the mls_num in your second query to ensure that you get same resultset.
/Johan Scott Gifford wrote:
Hello, I have an application that does searches against a database of homes. A summary of initial search results is displayed by showing a few columns of information about each home. When the user clicks on one of the listings, it retrieves additional information (some from other tables) and displays more detailed information about the house. The summary listings are generated using a normal MySQL query. The detailed views are implemented by specifying which result row to display using a LIMIT clause. For example, if the user clicks on the 3rd listing on a page, the query will use this LIMIT clause: LIMIT 2,1 We do this instead of specifying a value for the primary key so we can have a "Next" and "Previous" button that will move between detailed listings. These result rows may pull information in from other tables for display. Sometimes the homes are sorted according to a particular column, and sometimes they aren't. Obviously this whole scheme depends on the homes staying in the same order between the summary queries and the detail queries, even if the ordering is ambiguous. We've had this running for several years, and it's always worked fine. We're now seeing some problems with it, possibly because of a move from a server running MySQL 3.x to one running 4.x. The problem we're seeing is that when additional tables are pulled in for the detailed view, the order is different from the summary view, so the wrong homes are displayed. Here's a simplified example. A summary query might ask: mysql> SELECT lutar_homes.mls_num, lutar_images.num_images, lutar_homes_supplemental.address,lutar_homes.listdate FROM lutar_homes, lutar_homes_supplemental LEFT JOIN lutar_images ON lutar_homes.mls_num = lutar_images.mls_num WHERE ((lutar_homes.listdate >= (NOW() - INTERVAL '14 00:00:00' DAY_SECOND))) AND lutar_homes.mls_num = lutar_homes_supplemental.mls_numORDER BY lutar_homes.listdate DESC LIMIT 1; +---------+------------+-------------------------+------------+ | mls_num | num_images | address | listdate | +---------+------------+-------------------------+------------+ | 051768 | 1 | 7540 Country Pride Lane | 2005-05-31 | +---------+------------+-------------------------+------------+ 1 row in set (0.00 sec) When I add one more LEFT JOIN clause (the second one below) to get additional fields for the detailed view, I get a different first home, even though none of the search parameters have changed, and the table hasn't changed: mysql> SELECT lutar_homes.mls_num, lutar_images.num_images, lutar_homes_supplemental.address,lutar_homes.listdate FROM lutar_homes, lutar_homes_supplemental LEFT JOIN lutar_images ON lutar_homes.mls_num = lutar_images.mls_numLEFT JOIN lutar_homes_stats ON lutar_homes.mls_num = lutar_homes_stats.mls_numWHERE ((lutar_homes.listdate >= (NOW() - INTERVAL '14 00:00:00' DAY_SECOND))) AND lutar_homes.mls_num = lutar_homes_supplemental.mls_numORDER BY lutar_homes.listdate DESC LIMIT 1; +---------+------------+------------------+------------+ | mls_num | num_images | address | listdate | +---------+------------+------------------+------------+ | 051770 | 9 | 9149 Frankenmuth | 2005-05-31 | +---------+------------+------------------+------------+ 1 row in set (0.02 sec) This change in ordering screws up my system, since if the user clicked on the first result in the summary view, the detailed view may display a completely different home. So my question is: Is this normal behavior for MySQL, or is it a bug? Any suggestions for dealing with it? Thanks! Please let me know if any of this is confusing, and I'll clarify. ---ScottG.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
