#2861 PDO/Postgresql: error in branches list

v4.2.4
closed-fixed
Gerry Kroll
None
5
2011-03-08
2011-03-02
Blaza
No

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42P10]: Invalid column reference: 7 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list' in /includes/classes/class_pgv_db.php:835 Stack trace: #0 [internal function]: PDOStatement->execute(Array) #1 /includes/classes/class_pgv_db.php(835): call_user_func_array(Array, Array) #2 /branches.php(227): PGV_DBStatement->__call('execute', Array) #3 /branches.php(227): PGV_DBStatement->execute(Array) #4 /branches.php(91): indis_array('SURNAME', false, false) #5 {main} thrown in /includes/classes/class_pgv_db.php on line 835

PhpGedView 4.2.4, PDO/Postgresql 8.2.5

Discussion

  • Gerry Kroll
    Gerry Kroll
    2011-03-04

    That "SURNAME" looks odd. What selection criteria did you use?

    When you upgraded to version 4.2.4, did you remember to re-import your GEDCOM? This is applicable ONLY to an "ugrade" -- not to a completely fresh install.

    I don't think PGV fully supports postgresql; mysql is the only database system that's been properly checked. My server runs mysql, and I don't see your error, and I don't have enough information to reproduce the problem.

     
  • Blaza
    Blaza
    2011-03-04

    This "SURnAME" thing was just an example. I have found the problem. the function indis_array in uses an SELECT query with ORDER BY expression. The thing is that in Postgresql you can only order by columns you select. So the query
    SELECT DISTINCT n_id
    FROM pgv_name
    WHERE n_file=? AND n_type!=? AND (n_surn=? OR n_surname=?)
    ORDER BY n_sort
    is not correct.
    It works when ORDER BY is removed or n_sort is added after n_id.

    According to readme file phpgedview supports mysql, postgresql, sql-lite, sql server :)

     
  • Gerry Kroll
    Gerry Kroll
    2011-03-04

    Thank you for your analysis of this problem.

    Can you tell me whether the requirement for having the "ORDER BY" columns also appearing in the SELECT columns applies to SELECT statements without the "UNIQUE" clause?

    The error message you reproduced seems to imply that it's a requirement of the "SELECT UNIQUE" statement without also mentioning the regular "SELECT" statement.

     
  • Gerry Kroll
    Gerry Kroll
    2011-03-04

    • assigned_to: nobody --> canajun2eh
    • summary: error in branches list --> PDO/Postgresql: error in branches list
     
  • Gerry Kroll
    Gerry Kroll
    2011-03-04

    It would appear that your implementation of postgresql is faulty, or that the specifications have changed. Perhaps there's a bug in postgresql 8.2.5?

    I'm looking at the on-line postgresql documentation:
    http://www.postgresql.org/docs/8.2/static/sql-select.html#SQL-ORDERBY

    I quote from the text:
    It is also possible to use arbitrary expressions in the ORDER BY clause, including columns that do not appear in the SELECT result list. Thus the following statement is valid:

    SELECT name FROM distributors ORDER BY code;

     
  • Blaza
    Blaza
    2011-03-08

    I have tested this more. This is only a problem when SELECT DISTINCT is used. Then one can't use non-selected columns in ORDER BY clause. I have verified this in Posgresql 8.2.5 and also on Postgresql 9.0.2.

    I have found a good explanation here:
    http://archives.postgresql.org/pgsql-sql/2007-02/msg00171.php

    So I would suggest to remove the ordering in this case. For me it works correctly with this modification.

     
  • Gerry Kroll
    Gerry Kroll
    2011-03-08

    Fixed in SVN 7032.

    "branches.php" is the only place in the entire program where this problem occurs. It is **ONLY** a problem with postgresql.

    Download the attached ZIP file, xpand on your local PC, and then copy the result to the server directory where PhpGedView is installed.

     
  • Gerry Kroll
    Gerry Kroll
    2011-03-08

    • status: open --> closed-fixed
     
  • Gerry Kroll
    Gerry Kroll
    2011-03-08

    "branches.php" for version 4.2.4

     
    Attachments