Identical field names causing error in system

The question itself will be updated along until the error is clear.

We recently did an upgrade on our system a few months back from JBoss to Wildfly and our system has gone buggy ever since.

Lets say I have the following tables

CREATE TABLE students(
    ID int,
    Classid int,
    Name varchar(50),
    Version int
)

CREATE TABLE classstudent(
    ID int,
    Classname varchar(50),
    Version int
)

I write the following query and it worked

select *
from students

I write the following but it doesnt work

select *
from students s
join classStudent cs on cs.id = s.classid

So I decided to select each field

select s.id,s.classid,c.name,s.version,cs.id,cs.classname,cs.version
from students s
join classStudent cs on cs.id = s.classid

I still get an error so I decided to select the 2 ids from each table

select s.id,cs.id
from students s
join classStudent cs on cs.id = s.classid

I got an error one more time but then I renamed the ids to different names

select s.id as sid,cs.id as csid
from students s
join classStudent cs on cs.id = s.classid

This query worked, so clearly whenever there are identical field names on either table, the system throws an error.

What is this problem and how can I solve this problem? Where should I start looking? Any link to help point me in the right direction would be greatful!

error
----

WFLYEJB0443: EJB 3.1 FR 13.3.3: BMT bean SqlSearchExecutionSessionBean should complete transaction before returning.

As I said however before, this is a general error and I sometimes get it too when I misspell a table name or field name!

Solution

Your query result can't have identical column names as a result. So the column name id can only exist once in your SELECT statement.

Therefore I would try this:

select s.id, s.classid, c.name, s.version, cs.classname, cs.version
from Students s
join ClassStudent cs on cs.id = s.classid

In here you don't have to mention cs.id since it is always equal to s.id as it is in your join statement. However, if you do want it in your query you could give it an alias like this cs.id AS csid (or some other unused name) and add this to your SELECT statement.