extreme query returning unexpected results

[michaellunsford]

I'm doing a FULLTEXT search across four tables. Some tables have certain fields that others do not, so to get around the "wrong column count" errors, I added [color=blue]'' AS `field_name`[/color] to those queries who were missing that field.

For example:

[code]
(SELECT `pages_per_minute`,`paper_trays`,'' AS `processor`, '' AS `hard_drive`,'printers' AS `table_name` FROM `printers`)
UNION
(SELECT '' AS `pages_per_minute`,'' AS `paper_trays`,`processor`, `hard_drive`, 'computers' AS `table_name`FROM `computers`)
[/code]

What's weird, I get the occasional weirdo response:

[0]['pages_per_minute'] = 225
[0]['paper_trays'] = 2 trays
[0]['processor'] =
[0]['hard_drive'] =
[0]['table_name'] = printers

[1]['pages_per_minute'] =
[1]['paper_trays'] = [b]250 Gigabyte[b]
[1]['processor'] = Intel
[1]['hard_drive'] = 250 Gigabyte
[1]['table_name'] = computers

I'm really scratching my head on this one, though. So any insight would be awesome.

[hvle]

why do you have 250 Gigabyte between [1]['paper_trays'] =
and [1]['processor'] = Intel

is that a typo or real output?

[michaellunsford]

no, that's the real output (and the problem). Here is the output again, in code tags to make it a little cleaner.

[code]
[0]['pages_per_minute'] = 225
[0]['paper_trays'] = 2 trays
[0]['processor'] =
[0]['hard_drive'] =
[0]['table_name'] = printers

[1]['pages_per_minute'] =
[1]['paper_trays'] = 250 Gigabyte
[1]['processor'] = Intel
[1]['hard_drive'] = 250 Gigabyte
[1]['table_name'] = computers
[/code]

[hvle]

This is very weird, I would swap the query to see what happen?
If you have a solution, please post it.

[michaellunsford]

weird... I reverse it and it works fine. Wonder what's up with that

By the way, duplicating the query in phpMyAdmin does the same thing: one way, it "creates" data (apparently based on another record in the same row), the other way, the data seems accurate (at least for the same 9 records it returns).

[hvle]

is it possible that you can dump your 2 tables and data here?
I like to know what caused this glitch.

[michaellunsford]

Well, it's actually 4 tables. All told it's about 3,000 rows x 80+ columns.

[hvle]

well, I only need 2 tables but it's problem, I can duplicate your data and table and do some test.

[btherl]

It's actually 4 tables?  I think we need the full details to solve this.  All four tables, the exact query, as well as a full dump of the output.

Edit: I mean the definition of all 4 tables, not the contents :)

[michaellunsford]

I gave a simplified example to simplify the nuances of the different tables -- printers and computers are pretty easy in this crowd  :)

Anyway, you can download the tables (with contents) [url=http://www.virtual-showcase.net/mls_lafayette.sql.zip]here[/url] (it's about 1.2 megabytes)

You can perform a search on the test-site [url=http://new.realestatedela.com/residential.html]here[/url]. To reproduce the results search for [color=blue]breaux bridge[/color]. The second match is the culprit: L06134084.

The problem is it's a commercial property that's returning bedrooms. And there isn't even a bedrooms field in that table. Reveal source and you can easily find the SQL query tucked inside HTML comments, and a little further down, I'm print_r()ing the entire $row.

[michaellunsford]

New information:
1. I've acheived the same error using a different search method. I can pull up the same record by searching by just the number: [color=blue]L06134084[/color]. The difference is the number search doesn't use MATCH() AGAINST() and it's still producing the same results.

2. phpMyAdmin. I've run the search a dozen different ways. Basically, if I put residential in front of commercial, the bedrooms get "created" from one of the [color=blue]square foot[/color] fields -- even if eliminate all the other tables.

If anyone has been ambitious enough to try loading the database and duplicating the search, I'd be very curious to know if you get the same results. PS, I'm running MySQL 4.1.21

[michaellunsford]

here we go... I think I found it. MySQL is returning the fields in the order I specify them (instead of putting them in the right column automatically).

so,
[code](select `one`,`two`,`three` from `table`) UNION (select `two`,`one`,`three` from `table2`)[/code]

is scrambling the results

[fenway]

It has no idea what else to do with them... it's just blindly merging two separate result sets, the only caveat being they have the same number of columns.

[michaellunsford]

the four columns actually do have quite a bit of overlap, price, product number, availability, etc. Anyway, I finally got it working by calling the columns in the same order on each UNION call. Since then, all is smooth sailing.

Thanks!