From owner-freebsd-questions@FreeBSD.ORG Tue Sep 16 06:30:39 2008 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id 8F331106566C for ; Tue, 16 Sep 2008 06:30:39 +0000 (UTC) (envelope-from jonathan+freebsd-questions@hst.org.za) Received: from hermes.hst.org.za (onix.hst.org.za [209.203.2.133]) by mx1.freebsd.org (Postfix) with ESMTP id 9F5638FC1B for ; Tue, 16 Sep 2008 06:30:37 +0000 (UTC) (envelope-from jonathan+freebsd-questions@hst.org.za) Received: from sysadmin.hst.org.za (sysadmin.int.dbn.hst.org.za [10.1.1.20]) (authenticated bits=0) by hermes.hst.org.za (8.13.8/8.13.8) with ESMTP id m8G6NW4e088678 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Tue, 16 Sep 2008 08:23:32 +0200 (SAST) (envelope-from jonathan+freebsd-questions@hst.org.za) From: Jonathan McKeown Organization: Health Systems Trust To: freebsd-questions@freebsd.org Date: Tue, 16 Sep 2008 08:34:21 +0200 User-Agent: KMail/1.9.7 References: <48C13CBA.4070407@zedat.fu-berlin.de> <20080915174917.0a01255e.wmoran@potentialtech.com> In-Reply-To: <20080915174917.0a01255e.wmoran@potentialtech.com> X-Face: $@VrUx^RHy/}yu]jKf/<4T%/d|F+$j-Ol2"2J$q+%OK1]&/G_S9(=?utf-8?q?HkaQ*=60!=3FYOK=3FY!=27M=60C=0A=09aP=5C9nVPF8Q=7DCilHH8l=3B=7E!4?= =?utf-8?q?2HK6=273lg4J=7Daz?=@1Dqqh:J]M^"YPn*2IWrZON$1+G?oX3@ =?utf-8?q?k=230=0A=0954XDRg=3DYn=5FF-etwot4U=24b?=dTS{i X-Spam-Score: -4.377 () ALL_TRUSTED,AWL,BAYES_00 X-Scanned-By: MIMEDefang 2.61 on 209.203.2.133 Subject: Re: flyspray and php 5/postgresql weirdness, help ... X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 16 Sep 2008 06:30:39 -0000 On Monday 15 September 2008 23:49:17 Bill Moran wrote: > In response to "O. Hartmann" : > > hello, > > for development in our scientific environment I setup > > /usr/ports/devel/flyspray, running with a postgresql 8.3.3 backend on a > > FreeBSD 7.1-PRE box. > > After a successful installation, I get this error while trying to reach > > the freshly installed server via https://host/flyspray/: > > > > Query {SELECT p.*, c.content AS pm_instructions, c.last_updated AS > > cache_update FROM "flyspray_projects" p LEFT JOIN "flyspray_cache" c ON > > c.topic = p.project_id AND c.type = 'msg' WHERE p.project_id = ?} with > > params {1} Failed! (ERROR: operator does not exist: character varying = > > integer LINE 3: ... LEFT JOIN "flyspray_cache" c ON c.topic = > > p.projec... ^ HINT: No operator matches the given name and argument > > type(s). You might need to add explicit type casts.) > > > > I do not know whether the shown problem is postgresql-syntactical or a > > php 5 problem, so I guess it's a kind of both. > > The problem is that flyspray does not work on PG 8.3. Use an older version > of Postgres (I know that 8.1 works, and I suspect that 8.2 does) or help > the FS people fix their SQL. :) The ``problem'' (which I've encountered with RT and PostgreSQL 8.3) is that PG 8.3 is much stricter about argument types for operators: most of the implicit casts have gone away. In RT's case, this manifested itself as a refusal to do substr() on a date (which was being used to extract the year and month). Here, the message indicates that c.topic is a ``character varying'' and p.project_id is an integer, and pg is saying there isn't an equality operator which compares varchar with integer. It suggests casting one of the arguments to the type of the other. Looking back at the PG mailing lists, this introduction of type-strictness seems to have been a contentious decision, but one which the PG developers felt was worth the pain it will cause (it will also remove a few corner cases where the implicit cast caused surprising results). Jonathan