View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 4902 | Composr | core | public | 2022-08-21 01:03 | 2022-08-23 00:15 |
| Reporter | Adam Edington | Assigned To | Chris Graham | ||
| Priority | normal | Severity | trivial | ||
| Status | resolved | Resolution | fixed | ||
| Product Version | 10.0.43 | ||||
| Fixed in Version | 10.0.44 | ||||
| Summary | 4902: Duplicate ID column in Search | ||||
| 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=) | ||||
| Tags | No tags attached. | ||||
| Attach Tags | |||||
| Attached Files | |||||
| Time estimation (hours) | |||||
| Sponsorship open | |||||
|
|
Hmm, this query is not failing on my machine which is running MySQL 8.0.18. |
|
|
I could not reproduce this at all. 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. |
|
|
Automated response: Unknown database configurations may have problem with repeated column names in SELECT queries 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. |
|
|
Fixed in git commit 3fd41e2986 (https://gitlab.com/composr-foundation/composr/commit/3fd41e2986 - link will become active once code pushed to GitLab) A hotfix (a TAR of files to upload) has been uploaded to this issue. These files are made to the latest intra-version state (i.e. may roll in earlier fixes too if made to the same files) - so only upload files newer than what you have already. If there are files in a hot-fix that you don't have then they probably relate to addons that you don't have installed and should be skipped. Always take backups of files you are replacing or keep a copy of the manual installer for your version, and only apply fixes you need. These hotfixes are not necessarily reliable or well supported. Not sure how to extract TAR files to your Windows computer? Try 7-zip (http://www.7-zip.org/). |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2022-08-21 01:03 | Adam Edington | New Issue | |
| 2022-08-22 19:37 | Chris Graham | Note Added: 0007471 | |
| 2022-08-22 19:37 | Chris Graham | Note Edited: 0007471 | |
| 2022-08-22 22:30 | Chris Graham | Note Added: 0007472 |
