Operand should contain 1 column(s)

Posted on

Question :

Hello I have problem with this query

INSERT INTO radacct 
    (acctsessionid,acctuniqueid,username, 
    realm,nasipaddress,nasportid, nasporttype,acctstarttime,
    acctupdatetime, acctstoptime,acctsessiontime, acctauthentic,
     connectinfo_start,connectinfo_stop, acctinputoctets, 
     acctoutputoctets,calledstationid, callingstationid, 
     acctterminatecause,servicetype,framedprotocol, 
     framedipaddress,dup,t_session, nas_name,groupname,
     percent_q,down_uq,up_uq,all_tu,up_dq,dwon_dq,time_dq,t_update)
     VALUES ('80a000cc', '678593b487e8a42f68685f896b9d6d5d',
     '1', '', '10.40.47.1', 'WAN6', 'Wireless-802.11', 
     DATE_SUB('2019-05-18 03:06:18' , INTERVAL 10934 SECOND), 
     CURRENT_TIMESTAMP(), NULL, 10934, '', '', '',
     '0' << 32 | '29802288', '0' << 32 | '739955022', 
     'hotspot1', '3C:D9:2B:50:B2:BC', '', '', '', '10.40.47.44',
     '80a000cc', unix_timestamp(), 'Router 21',
     COALESCE((SELECT groupname FROM `radusergroup` WHERE username = '1' ORDER BY `id` DESC LIMIT 1),'0'),
     COALESCE((SELECT userinfo.percent_now,SUM(r1.acctoutputoctets * (r1.percent_q/100)),SUM(r1.acctinputoctets * (r1.percent_q/100)),SUM(`r1`.`acctsessiontime`) FROM radacct as `r1` INNER JOIN userinfo ON (userinfo.username=r1.username) WHERE r1.username = '1' AND UNIX_TIMESTAMP(STR_TO_DATE(r1.acctstarttime, '%Y-%m-%d %H:%i:%s')) >= userinfo.updatedate),(SELECT 100,SUM(r2.acctoutputoctets),SUM(r2.acctinputoctets),SUM(`r9`.`acctsessiontime`) FROM radacct as `r2` INNER JOIN radcheck ON (radcheck.username=r2.username AND radcheck.is_card ='1' AND `radcheck`.`attribute`='Cleartext-Password' ) WHERE r2.username = '1')), 
     COALESCE((SELECT SUM(`r5`.`acctinputoctets` * (r5.percent_q/100)),SUM(`r6`.`acctoutputoctets` * (r6.percent_q/100)),SUM(`r6`.`acctsessiontime`)  FROM `radacct` as `r5` WHERE  DATE_FORMAT(STR_TO_DATE(`r5`.`acctstarttime`,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') AND r5.username = '1'),(SELECT 0,0,0)), '')

Answer :

The error is due to the last two COASLESCE() function in your query:

For an example, you are expecting 3 values from the below COALESCE() function, but it doesn’t

COALESCE((
        SELECT SUM(`r5`.`acctinputoctets` * (r5.percent_q / 100)),
               SUM(`r6`.`acctoutputoctets` * (r6.percent_q / 100)),
               SUM(`r6`.`acctsessiontime`)
        FROM `radacct` AS `r5`
        WHERE DATE_FORMAT(STR_TO_DATE(`r5`.`acctstarttime`, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d')
            AND r5.username = '1'
        ), (
        SELECT 0,
            0,
            0
        )),

You need to change it to individual select value, then it will work.

Leave a Reply

Your email address will not be published.