#4902 - Duplicate ID column in Search
-
By Adam Edington
- Added
- 3 views
| Identifier | #4902 |
|---|---|
| Issue type | Trivial issue (does not break functionality) |
| Title | Duplicate ID column in Search |
| Status | Completed |
| Handling member | Chris Graham |
| Version | 10.0.43 |
| Addon | core |
| Description | Unfortunately a query has failed [(SELECT r.*,r.id AS id,r.cc_id AS r_cc_id,f0.cv_value AS b_cv_value,MATCH (f0.cv_value) AGAINST ('2020') AS contextual_relevance FROM cms_catalogue_entries r LEFT JOIN cms_catalogue_efv_short f0 ON (f0.ce_id=r.id AND f0.cf_id=37) WHERE MATCH (f0.cv_value) AGAINST ('2020') AND r.c_name='videos' LIMIT 0,10)/* fbcfdadd0dcfd */ LIMIT 10] [Duplicate column name 'id'] (version: 10.0.43, PHP version: 8.0.20, URL: /composr/mediafeeder/dev/v10/zone_test/performers/search/results/catalogue_entries.htm?specific=1&catalogue_name=videos&all_defaults=0&content=2020&author=&days=60&sort=relevance&direction=DESC&option_37=&search_under=) |
| Steps to reproduce | |
| Funded? | No |
| Commits |
The system will post a comment when this issue is modified (e.g., status changes). To be notified of this, click "Enable comment notifications".
Comments
The issue has to be with the 'id' in the SELECT clause, as there is no ambiguous id anywhere else. 'id' comes in from 'r.*' but also 'r.id AS id'. But it's the same field, and we do this pattern pretty commonly.
The MySQL SELECT documentation has this clause saying it is fine:
"MySQL permits duplicate column names. That is, there can be more than one select_expr with the same name. This is an extension to standard SQL"
MariaDB has its own documentation (it can't use MySQL's for copyright reasons), and has no similar clause in it. But that is probably just because they didn't feel a need to go into the same in-depth detail that MySQL's already does.
I even tested on Postgres, and it was fine.
And I trawled the MySQL manual to see if there was some extra strict mode, and there wasn't.
And I tested on the very latest MariaDB and it was fine.
Maybe a bug in a very specific version of MariaDB or MySQL.
Or possibly it's an error message from a previous query, but the mysqli_error function is documented as "Returns the error code for the most recent function call", so it should not be possible. And besides, we should not be having previous queries failing with that either, it's not something we'd sweep under the rug.
All that said, I think I have a fix, and it puts us inline with what other parts of our code already do so I'm happy to include it.
MySQL and MariaDB and Postgres (at least) seem to support selecting multiple columns under the same ID. But we had a report of it not working. Fix any obvious cases of this happening, and alter the XML DB driver to be very strict so that we can pick up on such cases if they happen in the future.