Обсуждение: Implicit conversion bugaboo in beta2?
Guys, got this problem in 7.4 beta 2: treedemo=# SELECT LPAD ((team_name), (LENGTH(team_name) + (3*(tlevel-2)))) AS teams_display,team_id, lnode treedemo-# FROM teams treedemo-# WHERE lnode > 0 treedemo-# ORDER BY lnode; ERROR: function lpad(character varying, bigint) does not exist (the above query worked fine in 7.3.4, as I recall) treedemo=# \df lpad List of functions Result data type | Name | Argument data types ------------------+------+--------------------- text | lpad | text, integer text | lpad | text, integer, text Now, I've been in favor of reducing problematic implicit conversions. But VARCHAR --> TEXT is one that needs to stay, as there's no possibility of ambiguity, and most users count on doing it transparently. Either that, or we need to build all string function for varchar. -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes: > ERROR: function lpad(character varying, bigint) does not exist > (the above query worked fine in 7.3.4, as I recall) Really? I get regression=# select lpad('xyz'::varchar, 4::int8); ERROR: Function lpad(character varying, bigint) does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts in 7.3.4, and the same spelled a tad differently in CVS tip ... regards, tom lane
Tom, > regression=# select lpad('xyz'::varchar, 4::int8); > ERROR: Function lpad(character varying, bigint) does not exist > Unable to identify a function that satisfies the given argument > types You may need to add explicit typecasts Oops! Sorry. The problem is there, it's just something different than I orginally thought; the issue is the BIGINT. What confuses me is how the bigint got there; it's from this view: CREATE VIEW vw_teams AS SELECT teams_desc.team_id, team_name, team_code, notes, MIN(teams_tree.treeno) as lnode, MAX(teams_tree.treeno) as rnode, parent.team_id as parent_id, COUNT(*)/2 as tlevel FROM teams_desc JOIN teams_tree USING (team_id) JOIN teams_tree parent ON parent.treeno < teams_tree.treeno JOIN teams_tree parents ON parents.treeno < teams_tree.treeno WHERE parent.treeno = (SELECT max(p1.treeno) FROM teams_tree p1 WHERE p1.treeno < teams_tree.treeno AND EXISTS (select treeno from teams_tree p2 where p2.treeno > teams_tree.treeno and p2.team_id = p1.team_id)) AND EXISTS (select parents2.team_id from teams_tree parents2 where parents2.treeno > teams_tree.treeno AND parents2.team_id = parents.team_id) GROUP BY teams_desc.team_id, team_name, team_code, notes, parent.team_id; In 7.4 beta2, the "tlevel" column comes out as BIGINT, not INT as it certainly did in 7.2.4 and I think it did in 7.3.4. Are we now defaulting COUNT(*) to BIGINT? IF so, that's going to be a *huge* backwards compatibility warning for people .... -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes: > Are we now defaulting COUNT(*) to BIGINT? Uh, yeah, but we did in 7.2 and 7.3 as well... count() hasn't been int4 since 7.1... regards, tom lane
Tom, > Uh, yeah, but we did in 7.2 and 7.3 as well... count() hasn't been > int4 since 7.1... Hmmm ... can't be 7.2. The query is taken from a production database written for 7.2; I'd have noticed the BIGINT problem before now. Either that, or in 7.2 we were doing implicit conversion from BIGINT to INT for function calls? However, it's certainly possible it happend in 7.3, as this particular app was not ported to 7.3. -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes: >> Uh, yeah, but we did in 7.2 and 7.3 as well... count() hasn't been >> int4 since 7.1... > Hmmm ... can't be 7.2. The query is taken from a production database written > for 7.2; I'd have noticed the BIGINT problem before now. Either that, or in > 7.2 we were doing implicit conversion from BIGINT to INT for function calls? That could be --- I don't recall exactly when we decided implicit bigint->int conversion was a bad idea ... regards, tom lane
Tom, > That could be --- I don't recall exactly when we decided implicit > bigint->int conversion was a bad idea ... Well, it is a bad idea, so I won't argue. Sorry for the false alarm. -- Josh Berkus Aglio Database Solutions San Francisco