Обсуждение: MySQL -> PostgreSQL conversion issue
Hi I have the following rather complicated SQL which works under MySQL but fails under PostgreSQL 8.3. The SQL is generated by A2Billing (http://www.asterisk2billing.org/cgi-bin/trac.cgi). The issue is the following part of the SQL: as tf on tf.dnid=substr(cdr.dnid,1,length(tf.dnid)) where tf.dnid is a bigint and cdr.dnid is varchar(40). The error returned is ERROR: function length(bigint) does not exist at character 2521 2010-10-29 13:34:27 NZDT HINT: No function matches the given name and argument types. You might need to add explicit type casts. I have tried adding explicit casts: tf.dnid = substr(cdr.dnid, 1, length(cast(td.dnid) as varchar(40))) which complains with ERROR: operator does not exist: bigint = text at character 2502 and tf.dnid = cast(substr(cdr.dnid, 1, length(cast(td.dnid) as varchar(40))) as bigint) which complains with ERROR: invalid input syntax for integer: "dnid" What would be the best way to convert this to work under PostgreSQL? The complete log entry is given below: Thanks 2010-10-29 13:34:27 NZDT ERROR: function length(bigint) does not exist at character 2521 2010-10-29 13:34:27 NZDT HINT: No function matches the given name and argument types. You might need to add explicit type casts. 2010-10-29 13:34:27 NZDT STATEMENT: create temporary table pnl_report as select id,name,call_count,time_minutes,tall_free_buy_cost,pay_phone_buy_cost,orig_only,credits,orig_total, tall_free_sell_cost,pay_phone_sell_cost,term_only,charges,term_total, first_use,discount, net_revenue,(net_revenue-orig_total) as profit, (net_revenue- orig_total)/net_revenue*100 as margin from( select main_id as id, name,call_count,time_minutes,tall_free_buy_cost,pay_phone_buy_cost,orig_only,credits,orig_cost+credits as orig_total, tall_free_sell_cost,pay_phone_sell_cost,term_only,charges,term_cost+charges as term_total, first_use,discount, ((term_cost+charges))*( 1-discount/100) as net_revenue from( select t1.id_group as main_id,cg.name,call_count,time_minutes,tall_free_buy_cost,pay_phone_buy_cost, orig_cost-tall_free_buy_cost-pay_phone_buy_cost as orig_only,orig_cost, case when credits is null then 0 else credits end as credits,0 as total, tall_free_sell_cost,pay_phone_sell_cost,term_cost- tall_free_sell_cost-pay_phone_sell_cost as term_only,term_cost, case when charges is null then 0 else charges end as charges, first_use,discount from ( select id_group,count(*) as call_count ,sum(sessiontime) / 60 as time_minutes, sum( case when tall_free=0 then 0 else real_sessiontime/60*tf_cost end) as tall_free_buy_cost, sum( case when pay_phone=0 then 0 else real_sessiontime/60*tf_cost end) as pay_phone_buy_cost, sum(buycost) as orig_cost, sum( case when tall_free=0 then 0 else real_sessiontime/60*tf_sell_cost end) as tall_free_sell_cost, sum( case when pay_phone=0 then 0 else real_sessiontime/60*tf_sell_cost end) as pay_phone_sell_cost, sum(sessionbill) as term_cost, sum(discount*sessionbill)/sum(sessionbill) as discount from ( select cc.id_group, cdr.sessiontime,cdr.dnid,cdr.real_sessiontime,sessionbill,buycost,cc.discount, case when tf.cost is null then 0 else tf.cost end as tf_cost, case when tf.sell_cost is null then 0 else tf.sell_cost end as tf_sell_cost, case when tf.dnid_type is null then 0 when tf.dnid_type=1 then 1 else 0 end as tall_free, case when tf.dnid_type is null then 0 when tf.dnid_type=2 then 1 else 0 end as pay_phone from cc_call cdr left join cc_card cc on cdr.card_id=cc.id left join (select 'dnid' as dnid, 0.1 as sell_cost,0.1 as cost,0 as dnid_type union select 8887798764,0.02,0.06 ,1 ) as tf on tf.dnid=substr(cdr.dnid,1,length(tf.dnid)) where sessiontime>0 and CURRENT_TIMESTAMP - interval '6 hours' <= cdr.starttime order by cdr.starttime desc ) as a group by id_group ) as t1 left join cc_card_group as cg on cg.id=id_group left join pnl_report_sub1 as t2 on t1.id_group=t2.id_group left join pnl_report_sub2 as t3 on t1.id_group=t3.id_group left join pnl_report_sub3 as t4 on t1.id_group=t4.id_group )as result )as final 2010-10-29 13:34:27 NZDT ERROR: relation "pnl_report" does not exist 2010-10-29 13:34:27 NZDT STATEMENT: SELECT name,call_count,time_minutes,tall_free_buy_cost,pay_phone_buy_cost,orig_only,credits,orig_total, tall_free_sell_cost,pay_phone_sell_cost,term_only,charges,term_total, first_use,discount, net_revenue, margin, profit, id, id FROM pnl_report ORDER BY id ASC LIMIT 10 OFFSET 0 2010-10-29 13:34:27 NZDT ERROR: relation "pnl_report" does not exist 2010-10-29 13:34:27 NZDT STATEMENT: SELECT count(*) FROM pnl_report 2010-10-29 13:34:27 NZDT ERROR: relation "pnl_report" does not exist 2010-10-29 13:34:27 NZDT STATEMENT: select 'TOTAL',sum(call_count),sum(time_minutes),sum(tall_free_buy_cost),sum(pay_phone_buy_cost),sum(orig_only),sum(credits),sum(orig_total),sum( tall_free_sell_cost),sum(pay_phone_sell_cost),sum(term_only),sum(charges),sum(term_total),sum(first_use), (1-sum(net_revenue)/sum(term_total))*100,sum( net_revenue),sum(profit)/sum(net_revenue)*100,sum(profit),sum(id) from pnl_report -- Regards Scott Newton Software Engineer @ Vadacom Limited Ph: (09) 9690600 x 280
On Thu, Oct 28, 2010 at 6:44 PM, Scott Newton <scott.newton@vadacom.co.nz> wrote: > Hi > > I have the following rather complicated SQL which works under MySQL but fails > under PostgreSQL 8.3. The SQL is generated by A2Billing > (http://www.asterisk2billing.org/cgi-bin/trac.cgi). The issue is the following > part of the SQL: > > as tf on tf.dnid=substr(cdr.dnid,1,length(tf.dnid)) > > where tf.dnid is a bigint and cdr.dnid is varchar(40). The error returned is > ERROR: function length(bigint) does not exist at character 2521 > 2010-10-29 13:34:27 NZDT HINT: No function matches the given name and > argument types. You might need to add explicit type casts. This is usually a precedence thing, i.e. you're trying to join to a set that doesn't exist yet to that part of the query. Newer versions of mysql will also choke on such queries I believe as well. Didn't have time to go through your whole query but that's what to look for.
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Scott Marlowe > Sent: Thursday, October 28, 2010 6:31 PM > To: Scott Newton > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] MySQL -> PostgreSQL conversion issue > > On Thu, Oct 28, 2010 at 6:44 PM, Scott Newton > <scott.newton@vadacom.co.nz> wrote: > > Hi > > > > I have the following rather complicated SQL which works under MySQL > but fails > > under PostgreSQL 8.3. The SQL is generated by A2Billing > > (http://www.asterisk2billing.org/cgi-bin/trac.cgi). The issue is the > following > > part of the SQL: > > > > as tf on tf.dnid=substr(cdr.dnid,1,length(tf.dnid)) > > > > where tf.dnid is a bigint and cdr.dnid is varchar(40). The error > returned is > > ERROR: function length(bigint) does not exist at character 2521 > > 2010-10-29 13:34:27 NZDT HINT: No function matches the given name > and > > argument types. You might need to add explicit type casts. > > This is usually a precedence thing, i.e. you're trying to join to a > set that doesn't exist yet to that part of the query. Newer versions > of mysql will also choke on such queries I believe as well. Didn't > have time to go through your whole query but that's what to look for. If the operation is a substring, and if tf.dnid is a bigint, then the query is absurd. The data length of a bigint will be 8 bytes. Length has no meaning since tf.dnid is not a string. Also, assignment of asubstring to a bigint is a rather silly operation. Probably what the o.p. wants is just: tf.dnid = cdr.dnid::bigint But that assumes that the types bigint and varchar and the o.p. wants to store the number contained in the string into thebig integer. I would hate to see the rest of the design. I am ill from this tiny fragment.
Scott Newton <scott.newton@vadacom.co.nz> writes: > I have the following rather complicated SQL which works under MySQL but fails > under PostgreSQL 8.3. The SQL is generated by A2Billing > (http://www.asterisk2billing.org/cgi-bin/trac.cgi). The issue is the following > part of the SQL: > as tf on tf.dnid=substr(cdr.dnid,1,length(tf.dnid)) > where tf.dnid is a bigint and cdr.dnid is varchar(40). [ blanch... ] That's not the worst SQL code I've ever seen, but it might be in the top ten. They're apparently trying to see whether tf.dnid, taken as a string, matches a prefix of cdr.dnid --- but what if you have say 123 versus "1234foo"? This will match, but most likely it shouldn't. They need to acquire a clue, and a better data representation. Anyway, you've got two different typing violations there, so you need two casts to fix it: as tf on tf.dnid::text = substr(cdr.dnid,1,length(tf.dnid::text)) (salt to taste with CAST and/or varchar if you prefer, but it's all the same) regards, tom lane
On Fri, 29 Oct 2010 14:40:51 Tom Lane wrote: > [ blanch... ] That's not the worst SQL code I've ever seen, but it > might be in the top ten. They're apparently trying to see whether > tf.dnid, taken as a string, matches a prefix of cdr.dnid --- but what if > you have say 123 versus "1234foo"? This will match, but most likely > it shouldn't. They need to acquire a clue, and a better data > representation. Agreed - it's not very pretty. > Anyway, you've got two different typing violations there, so you need > two casts to fix it: > > as tf on tf.dnid::text = substr(cdr.dnid,1,length(tf.dnid::text)) Unfortunately not: from cc_call cdr left join cc_card cc on cdr.card_id=cc.id left join (select 'dnid' as dnid, 0.1 as sell_cost,0.1 as cost,0 as dnid_type union select 8887798764,0.02,0.06 ,1 ) as tf on tf.dnid::text=substr(cdr.dnid,1,length(tf.dnid::text)) still gives 2010-10-29 14:58:09 NZDT ERROR: invalid input syntax for integer: "dnid" Thanks -- Regards Scott Newton Software Engineer @ Vadacom Limited Ph: (09) 9690600 x 280
On Thu, Oct 28, 2010 at 20:02, Scott Newton <scott.newton@vadacom.co.nz> wrote: > On Fri, 29 Oct 2010 14:40:51 Tom Lane wrote: >> Anyway, you've got two different typing violations there, so you need >> two casts to fix it: > Unfortunately not: > from cc_call cdr left join cc_card cc on cdr.card_id=cc.id left join > (select 'dnid' as dnid, 0.1 as sell_cost,0.1 as cost,0 > as dnid_type union select 8887798764,0.02,0.06 ,1 > ) as tf on > tf.dnid::text=substr(cdr.dnid,1,length(tf.dnid::text)) > > still gives > > 2010-10-29 14:58:09 NZDT ERROR: invalid input syntax for integer: "dnid" You can fix that by casting 8887798764 as text (or by making "dnid" an int). The columns for a union need to be of the same type.