View Issue Details

IDProjectCategoryView StatusLast Update
0014070mantisbtdb postgresqlpublic2015-03-25 17:50
Reporterfhoffmann Assigned Todregad  
PrioritynormalSeveritycrashReproducibilityalways
Status closedResolutionno change required 
OScentos 
Product Version1.2.9 
Summary0014070: Error while order by userdefinied fields on "Show Issues" with pg sql
Description

Order by user definied fields will produce errors like:

APPLICATION ERROR 0000401
Datenbankabfrage fehlgeschlagen. Die Reportmeldung lautet #-1: ERROR: syntax error at or near "custom_field_4" at character 78 für die Abfrage: SELECT DISTINCT mantis_bug_table.*, mantis_custom_field_string_table_4.value custom_field_4 FROM mantis_project_table ....

Error can be solved by adding an "as" before col-alias

file

filter_api.php

line 922

$t_cf_select = "$t_cf_table_alias.value $c_cf_alias";
should be (for pg, no idea whats happen with other dbs) =>
$t_cf_select = "$t_cf_table_alias.value as $c_cf_alias";

TagsNo tags attached.

Activities

dregad

dregad

2012-03-23 09:08

developer   ~0031514

Last edited: 2012-03-23 09:09

According to PostgreSQL documentation [1], this can happen when the column alias matches a keyword, and is due to them not fully adhering to SQL standard. However, in your case the alias 'custom_field_4' is actually a valid identifier which is not a PostgreSQL keyword, so I'm a bit surprised by the error you're getting.

I tested here, but do not get any syntax error: <pre>
psql (9.1.1)
Type "help" for help.

mantis_12x=# select value custom_field_4 from mantis_custom_field_string_table;
custom_field_4

(0 rows)</pre>

Which version of PostgreSQL are you on ?

I don't think adding "as" to the SQL would be a major issue, but before doing it I'd like to understand why you're getting this error and I don't.

EDIT: It would also be interesting to know if you were getting this error prior to upgrading to 1.2.9, in which case it might have been caused by the fix for 0012404

[1] http://www.postgresql.org/docs/current/static/sql-select.html section "Omitting the AS Key Word".

grangeway

grangeway

2014-05-16 15:02

reporter   ~0040503

MantisBT currently supports Mysql and has support for other database engines.

The support for other databases is known to be problematic.

Having implemented the current database layer into Mantis 10 years ago, I'm currently working on replacing the current layer.

If you are interested in using Mantis with non-mysql databases - for example, Oracle, PGSQL or MSSQL, and would be willing to help out testing the new database layer, please drop me an email at paul@mantisforge.org

In the meantime, I'd advise running Mantis with Mysql Only to avoid issues.

Thanks
Paul

atrol

atrol

2014-06-16 15:40

developer   ~0040816

Reminder sent to: dregad, grangeway

Not sure, maybe this issue should be moved back to project mantis.
If so, grangeway could make a copy of it in project "Track DBAL replacement"

dregad

dregad

2015-03-10 18:32

developer   ~0049171

Resolving this since fhoffmann never provided feedback on my 2012 notes in 0014070:0031514. I'm pretty confident this would not be a problem on Mantis >= 1.2.9