Description:
On larger queries in SP/Triggers there is an annoying cursor postioning behavior when entering a single or double quote in the wrong place. see recreate for sample
How to repeat:
copy and paste the SP below into either query editor SP window or a routine window in the modeler (Used an old slightly larege sql statement to demonstrate the cursor problem)
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`admin`@`%` PROCEDURE `arorec_sum`(v_date DATE,
v_state varchar(2), v_salesmn varchar(2), v_custno varchar(6))
BEGIN
SELECT te.*,
te.invamt - te.paidamt as balance,
SUM(IF(te.gp='C',te.invamt - te.paidamt,0.00)) AS current,
SUM(IF(te.gp='1',te.invamt - te.paidamt,0.00)) AS pd1,
SUM(IF(te.gp='2',te.invamt - te.paidamt,0.00)) AS pd2,
SUM(IF(te.gp='3',te.invamt - te.paidamt,0.00)) AS pd3,
SUM(IF(te.gp='3',te.invamt - te.paidamt,0.00)) AS pd4,
SUM(IF(te.gp='O',te.invamt - te.paidamt,0.00)) AS pdover
FROM (SELECT a.custno,
a.invdte,
getpd(v_date, a.invdte) AS gp,
a.paidamt - COALESCE((SELECT SUM(f.apply + f.disc)
FROM cc_apply f USE INDEX(invno, appdate)
WHERE f.invno = a.invno AND f.appdate > v_date), 0) AS paidamt,
a.invamt - COALESCE((SELECT SUM(f.apply)
FROM cc_apply f USE INDEX(ccati, appdate)
WHERE f.ccati = a.invno AND f.appdate > v_date), 0) AS invamt,
0000000000.00 as future,
cu.limit,
cu.company,
cu.phone,
cu.faxno,
cu.contact,
cu.address1,
cu.address2,
cu.city,
cu.state AS state,
cu.zip,
cu.pterms,
cu.lastpay AS lastpay,
cu.lpymt,
cu.ldate AS ldate,
cu.lsale,
cu.inactive AS inactive
FROM `invoice master` a USE INDEX(custno, invdte, arstat)
LEFT OUTER JOIN customer cu USE INDEX(PRIMARY, state, salesmn) ON a.custno = cu.custno
WHERE (v_custno is null or a.custno = v_custno) AND
(v_state is null or cu.state = v_state) AND (v_salesmn is null OR cu.salesmn = v_salesmn)
AND a.invdte <= v_date AND a.balance <> 0 AND a.arstat = ' '
UNION SELECT a.custno,
a.invdte,
getpd(v_date, a.invdte) AS gp,
a.paidamt - COALESCE((SELECT SUM(f.apply + f.disc)
FROM cc_apply f USE INDEX(invno, appdate)
WHERE f.invno = a.invno AND f.appdate > v_date), 0) AS paidamt,
a.invamt - COALESCE((SELECT SUM(f.apply)
FROM cc_apply f USE INDEX(ccati, appdate)
WHERE f.ccati = a.invno AND f.appdate > v_date), 0) AS invamt,
0000000000.00 as future,
cu.limit,
cu.company,
cu.phone,
cu.faxno,
cu.contact,
cu.address1,
cu.address2,
cu.city,
cu.state AS state,
cu.zip,
cu.pterms,
cu.lastpay AS lastpay,
cu.lpymt,
cu.ldate AS ldate,
cu.lsale,
cu.inactive AS inactive
FROM `invoice master` a USE INDEX(custno, invdte, arstat)
INNER JOIN cc_apply b USE INDEX(invno, appdate) ON a.invno = b.invno
LEFT OUTER JOIN customer cu USE INDEX(PRIMARY, state, salesmn) ON a.custno = cu.custno
WHERE (v_state is null or cu.state = v_state) AND (v_salesmn is null OR cu.salesmn = v_salesmn)
AND (v_custno is null or a.custno = v_custno)
AND a.invdte <= v_date AND a.balance = 0 AND a.arstat = ' ' AND b.appdate > v_date
UNION SELECT a.custno,
a.invdte,
getpd(v_date, a.invdte) AS gp,
a.paidamt - COALESCE((SELECT SUM(f.apply + f.disc)
FROM cc_apply f USE INDEX(invno, appdate)
WHERE f.invno = a.invno AND f.appdate > v_date), 0) AS paidamt,
a.invamt - COALESCE((SELECT SUM(f.apply)
FROM cc_apply f USE INDEX(ccati, appdate)
WHERE f.ccati = a.invno AND f.appdate > v_date), 0) AS invamt,
0000000000.00 as future,
cu.limit,
cu.company,
cu.phone,
cu.faxno,
cu.contact,
cu.address1,
cu.address2,
cu.city,
cu.state AS state,
cu.zip,
cu.pterms,
cu.lastpay AS lastpay,
cu.lpymt,
cu.ldate AS ldate,
cu.lsale,
cu.inactive AS inactive
FROM `invoice master` a USE INDEX(custno, invdte, arstat)
INNER JOIN cc_apply b USE INDEX(ccati, appdate) ON a.invno = b.ccati
LEFT OUTER JOIN customer cu USE INDEX(PRIMARY, state, salesmn) ON a.custno = cu.custno
WHERE (v_state is null or cu.state = v_state) AND (v_salesmn is null OR cu.salesmn = v_salesmn)
AND (v_custno is null or a.custno = v_custno)
AND a.invdte <= v_date AND a.balance = 0 AND a.arstat = ' ' AND b.appdate > v_date
UNION SELECT a.custno,
a.invdte,
getpd(v_date, a.invdte) AS gp,
a.paidamt - COALESCE((SELECT SUM(f.apply + f.disc)
FROM cc_apply f USE INDEX(invno, appdate)
WHERE f.invno = a.invno AND f.appdate > v_date), 0) AS paidamt,
a.invamt - COALESCE((SELECT SUM(f.apply)
FROM cc_apply f USE INDEX(ccati, appdate)
WHERE f.ccati = a.invno AND f.appdate > v_date), 0) AS invamt,
a.invamt - COALESCE((SELECT SUM(f.apply)
FROM cc_apply f USE INDEX(ccati, appdate)
WHERE f.ccati = a.invno AND f.appdate > v_date), 0)
- a.paidamt - COALESCE((SELECT SUM(f.apply + f.disc)
FROM cc_apply f USE INDEX(invno, appdate)
WHERE f.invno = a.invno AND f.appdate > v_date), 0) AS future,
cu.limit,
cu.company,
cu.phone,
cu.faxno,
cu.contact,
cu.address1,
cu.address2,
cu.city,
cu.state AS state,
cu.zip,
cu.pterms,
cu.lastpay AS lastpay,
cu.lpymt,
cu.ldate AS ldate,
cu.lsale,
cu.inactive AS inactive
FROM `invoice master` a USE INDEX(custno, entered, invdte, arstat)
LEFT OUTER JOIN customer cu USE INDEX(PRIMARY, state, salesmn) ON a.custno = cu.custno
WHERE (v_state is null or cu.state = v_state) AND (v_salesmn is null OR cu.salesmn = v_salesmn)
AND (v_custno is null or a.custno = v_custno)
AND a.invdte > v_date AND a.entered <= v_date AND a.arstat = ' ') te
GROUP BY te.custno
ORDER BY te.custno ;
END$$
-------------------------------
now position the cursor at the 4th line from the bottome after
AND a.invdte > v_date AND a.entered <= v_date AND a.arstat = ' ') te
add an erroneous single or double quote after the te to get
AND a.invdte > v_date AND a.entered <= v_date AND a.arstat = ' ') te'
after about a second or so the screen jumps to the top of the routine. The cursor stays at the te all typing and deleting or what have you will done next to the te and out of sight. This even happens after you moved away from the eroneous entry by hitting the page up or down and started typing something else. This can be especially messy if typing fast and moving back and forth in a proc.
Suggested fix:
Don't jump around
Description: On larger queries in SP/Triggers there is an annoying cursor postioning behavior when entering a single or double quote in the wrong place. see recreate for sample How to repeat: copy and paste the SP below into either query editor SP window or a routine window in the modeler (Used an old slightly larege sql statement to demonstrate the cursor problem) -- -------------------------------------------------------------------------------- -- Routine DDL -- -------------------------------------------------------------------------------- DELIMITER $$ CREATE DEFINER=`admin`@`%` PROCEDURE `arorec_sum`(v_date DATE, v_state varchar(2), v_salesmn varchar(2), v_custno varchar(6)) BEGIN SELECT te.*, te.invamt - te.paidamt as balance, SUM(IF(te.gp='C',te.invamt - te.paidamt,0.00)) AS current, SUM(IF(te.gp='1',te.invamt - te.paidamt,0.00)) AS pd1, SUM(IF(te.gp='2',te.invamt - te.paidamt,0.00)) AS pd2, SUM(IF(te.gp='3',te.invamt - te.paidamt,0.00)) AS pd3, SUM(IF(te.gp='3',te.invamt - te.paidamt,0.00)) AS pd4, SUM(IF(te.gp='O',te.invamt - te.paidamt,0.00)) AS pdover FROM (SELECT a.custno, a.invdte, getpd(v_date, a.invdte) AS gp, a.paidamt - COALESCE((SELECT SUM(f.apply + f.disc) FROM cc_apply f USE INDEX(invno, appdate) WHERE f.invno = a.invno AND f.appdate > v_date), 0) AS paidamt, a.invamt - COALESCE((SELECT SUM(f.apply) FROM cc_apply f USE INDEX(ccati, appdate) WHERE f.ccati = a.invno AND f.appdate > v_date), 0) AS invamt, 0000000000.00 as future, cu.limit, cu.company, cu.phone, cu.faxno, cu.contact, cu.address1, cu.address2, cu.city, cu.state AS state, cu.zip, cu.pterms, cu.lastpay AS lastpay, cu.lpymt, cu.ldate AS ldate, cu.lsale, cu.inactive AS inactive FROM `invoice master` a USE INDEX(custno, invdte, arstat) LEFT OUTER JOIN customer cu USE INDEX(PRIMARY, state, salesmn) ON a.custno = cu.custno WHERE (v_custno is null or a.custno = v_custno) AND (v_state is null or cu.state = v_state) AND (v_salesmn is null OR cu.salesmn = v_salesmn) AND a.invdte <= v_date AND a.balance <> 0 AND a.arstat = ' ' UNION SELECT a.custno, a.invdte, getpd(v_date, a.invdte) AS gp, a.paidamt - COALESCE((SELECT SUM(f.apply + f.disc) FROM cc_apply f USE INDEX(invno, appdate) WHERE f.invno = a.invno AND f.appdate > v_date), 0) AS paidamt, a.invamt - COALESCE((SELECT SUM(f.apply) FROM cc_apply f USE INDEX(ccati, appdate) WHERE f.ccati = a.invno AND f.appdate > v_date), 0) AS invamt, 0000000000.00 as future, cu.limit, cu.company, cu.phone, cu.faxno, cu.contact, cu.address1, cu.address2, cu.city, cu.state AS state, cu.zip, cu.pterms, cu.lastpay AS lastpay, cu.lpymt, cu.ldate AS ldate, cu.lsale, cu.inactive AS inactive FROM `invoice master` a USE INDEX(custno, invdte, arstat) INNER JOIN cc_apply b USE INDEX(invno, appdate) ON a.invno = b.invno LEFT OUTER JOIN customer cu USE INDEX(PRIMARY, state, salesmn) ON a.custno = cu.custno WHERE (v_state is null or cu.state = v_state) AND (v_salesmn is null OR cu.salesmn = v_salesmn) AND (v_custno is null or a.custno = v_custno) AND a.invdte <= v_date AND a.balance = 0 AND a.arstat = ' ' AND b.appdate > v_date UNION SELECT a.custno, a.invdte, getpd(v_date, a.invdte) AS gp, a.paidamt - COALESCE((SELECT SUM(f.apply + f.disc) FROM cc_apply f USE INDEX(invno, appdate) WHERE f.invno = a.invno AND f.appdate > v_date), 0) AS paidamt, a.invamt - COALESCE((SELECT SUM(f.apply) FROM cc_apply f USE INDEX(ccati, appdate) WHERE f.ccati = a.invno AND f.appdate > v_date), 0) AS invamt, 0000000000.00 as future, cu.limit, cu.company, cu.phone, cu.faxno, cu.contact, cu.address1, cu.address2, cu.city, cu.state AS state, cu.zip, cu.pterms, cu.lastpay AS lastpay, cu.lpymt, cu.ldate AS ldate, cu.lsale, cu.inactive AS inactive FROM `invoice master` a USE INDEX(custno, invdte, arstat) INNER JOIN cc_apply b USE INDEX(ccati, appdate) ON a.invno = b.ccati LEFT OUTER JOIN customer cu USE INDEX(PRIMARY, state, salesmn) ON a.custno = cu.custno WHERE (v_state is null or cu.state = v_state) AND (v_salesmn is null OR cu.salesmn = v_salesmn) AND (v_custno is null or a.custno = v_custno) AND a.invdte <= v_date AND a.balance = 0 AND a.arstat = ' ' AND b.appdate > v_date UNION SELECT a.custno, a.invdte, getpd(v_date, a.invdte) AS gp, a.paidamt - COALESCE((SELECT SUM(f.apply + f.disc) FROM cc_apply f USE INDEX(invno, appdate) WHERE f.invno = a.invno AND f.appdate > v_date), 0) AS paidamt, a.invamt - COALESCE((SELECT SUM(f.apply) FROM cc_apply f USE INDEX(ccati, appdate) WHERE f.ccati = a.invno AND f.appdate > v_date), 0) AS invamt, a.invamt - COALESCE((SELECT SUM(f.apply) FROM cc_apply f USE INDEX(ccati, appdate) WHERE f.ccati = a.invno AND f.appdate > v_date), 0) - a.paidamt - COALESCE((SELECT SUM(f.apply + f.disc) FROM cc_apply f USE INDEX(invno, appdate) WHERE f.invno = a.invno AND f.appdate > v_date), 0) AS future, cu.limit, cu.company, cu.phone, cu.faxno, cu.contact, cu.address1, cu.address2, cu.city, cu.state AS state, cu.zip, cu.pterms, cu.lastpay AS lastpay, cu.lpymt, cu.ldate AS ldate, cu.lsale, cu.inactive AS inactive FROM `invoice master` a USE INDEX(custno, entered, invdte, arstat) LEFT OUTER JOIN customer cu USE INDEX(PRIMARY, state, salesmn) ON a.custno = cu.custno WHERE (v_state is null or cu.state = v_state) AND (v_salesmn is null OR cu.salesmn = v_salesmn) AND (v_custno is null or a.custno = v_custno) AND a.invdte > v_date AND a.entered <= v_date AND a.arstat = ' ') te GROUP BY te.custno ORDER BY te.custno ; END$$ ------------------------------- now position the cursor at the 4th line from the bottome after AND a.invdte > v_date AND a.entered <= v_date AND a.arstat = ' ') te add an erroneous single or double quote after the te to get AND a.invdte > v_date AND a.entered <= v_date AND a.arstat = ' ') te' after about a second or so the screen jumps to the top of the routine. The cursor stays at the te all typing and deleting or what have you will done next to the te and out of sight. This even happens after you moved away from the eroneous entry by hitting the page up or down and started typing something else. This can be especially messy if typing fast and moving back and forth in a proc. Suggested fix: Don't jump around