java.sql.SQLSyntaxErrorException: No such column: id

Question

java.sql.SQLSyntaxErrorException: No such column: id

When table already contains the column id

I'm trying to have the following native SQL query that runs with expected results from within MariaDB SQL from a Repository class:

SELECT name
FROM table1 t1
WHERE t1.table2_id IN (
  SELECT id
  FROM table2 t2
  WHERE t2.column1_number = 1 AND
        t2.id IS NOT NULL
  )
ORDER BY t1.name 

The entity classes are as follows:

@Entity
@Table(name = "table1")
public class Table1 {

    @Column(nullable = false)
    private String name;

    @Column(nullable = false)
    private Integer number;

    @ManyToOne
    @JoinColumn(name = "table2_id")
    private Table2 table2;

   // Getters and Setters
}

@Entity
@Table(name="table2")
public class Table2 {
    private  Integer id;
    private  Integer column1_number;

    @Override
    public Integer getId() {
        return id;
    }

    @Override
    public void setId(Integer id) {
        this.id = id;
    }

   // Getters and Setters
}

The Repository is:

@Repository
public interface Table1Repository extends JpaRepository<Table1, Integer> {

    @Query(value = "SELECT name FROM table1 t1 WHERE t1.table2_id IN (SELECT id FROM table2 t2 WHERE t2.column1_number = ?1 AND t2.id IS NOT NULL) ORDER BY t1.name", nativeQuery = true)
    List<Table1> findByNumberOrderByName(Integer number);
}

Any ideas as to why I am getting this error at run time? This error does not occur at compile time.

Answer 1

@Query(value = "SELECT name FROM table1 t1 WHERE t1.table2_id IN (SELECT id FROM table2 t2 WHERE t2.column1_number = ?1 AND t2.id IS NOT NULL) ORDER BY t1.name", nativeQuery = true)
    List<Table1> findByNumberOrderByName(Integer number);

should have been List<String> and not List<Table1>

and so the solution was:

@Query(value = "SELECT name FROM table1 t1 WHERE t1.table2_id IN (SELECT id FROM table2 t2 WHERE t2.column1_number = ?1 AND t2.id IS NOT NULL) ORDER BY t1.name", nativeQuery = true)
    List<String> findByNumberOrderByName(Integer number);