CODEWITHSUNDEEP
javamysql
arturojain
arturojain

Reputation: 167

java.sql.SQLException: Subquery returns more than 1 row

I have a program that when executed it gets lots of words from a file and inserts them into a database, after being inserted if the word is inserted twice it calculates the "IDF" again using a trigger. The problem is that if I do this directly into MySQL it has no problem, but if I do this on Java it returns this error:

Exception in thread "main" java.sql.SQLException: Subquery returns more than 1 row
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2828)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2777)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:949)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:795)
    at model.Consultas.altaBajaCambio(Consultas.java:29)
    at model.DatosBD.agregarPalabra(DatosBD.java:23)
    at search.Search.main(Search.java:36)
Java Result: 1

I assume the problem has to be with the st.execute(), since it only gives back one int, but I have search on the web for a solution and I cannot find one.

Query:

String query2 = "INSERT IGNORE INTO Search.IndiceInv (DocID, Term, TF) VALUES ("+doc+",'"+term+"',1) ON DUPLICATE KEY UPDATE `TF` = `TF` + 1;";
c.altaBajaCambio(query2);

Execution:

try (Connection con = c.getConnection()) {
            if (con == null) {
                System.out.println("No hay conexion");
            } else {
                Statement st = con.createStatement();
                st.execute(query);
            }

Database:

-- -----------------------------------------------------
-- Table `Search`.`Doc`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Search`.`Doc` (
  `DocID` INT NOT NULL,
  PRIMARY KEY (`DocID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Search`.`Term`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Search`.`Term` (
  `Term` VARCHAR(45) NOT NULL,
  `IDF` INT NOT NULL,
  PRIMARY KEY (`Term`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Search`.`IndiceInv`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Search`.`IndiceInv` (
  `DocID` INT NOT NULL,
  `Term` VARCHAR(45) NOT NULL,
  `TF` INT NOT NULL,
  PRIMARY KEY (`DocID`, `Term`),
ENGINE = InnoDB;

Trigger:

delimiter //
create trigger IDF
after update on IndiceInv
for each row
begin
  update Term
  set IDF = (SELECT LOG((SELECT count(*) FROM Doc)/(SELECT count(DocID) FROM IndiceInv WHERE Term = new.Term)) FROM Doc, IndiceInv)
  where Term = new.Term;
end;//
delimiter ;

Upvotes: 1

Views: 2488

Answers (1)

Nir Alfasi
Nir Alfasi

Reputation: 53535

Try to run manually: 

SELECT LOG((SELECT count(*) FROM Doc)/(SELECT count(DocID) FROM IndiceInv WHERE Term = new.Term)) FROM Doc, IndiceInv

(assign the relevant values to new.Term etc)

Upvotes: 2

Related Questions