Spring Boot's CrudRepository's custom find method not working properly when id is not unique

Question

It appears that Spring Boot is not capable of interpreting SELECT query results when id is not unique.

For example, let us say that there is a table t in the database which has two columns c and c2. Table does not have a primary key, so both columns may be duplicated, but it has an unique constraint on combination of c and c2 columns so that rows can not be duplicated. Here are sample data for this table:

c | c2
______
a | a1
a | a2
a | a3
b | a1

What I need to achieve in this table is to have one and only one b in the c column and zero to many as. I defined a Spring's repository for this table as follows:

package P;
public interface tr extends org.springframework.data.repository.PagingAndSortingRepository<P.t, java.lang.String>
{
    public java.util.List<P.t> findAllByc(java.lang.String c);
}

Here I specified that Spring should use a string for an table id. Although I do not use any of repository's "*ById" methods. This is just something what is required to be specified here whether or not I need it.

And the table is represented in the Java Persistence Application programming interface class t like this:

package P;
@javax.persistence.Entity()
@javax.persistence.Table(schema = "s")
public class t implements java.io.Serializable
{
    @javax.persistence.Id()
    public java.lang.String c;
    public java.lang.String c2;
}

Here I marked column c as an id, because it is Spring's requirement, although, as I said, I do not use "*byId" methods, so, it is not important to me.

Now, in the repository I defined (or, to be precise, declared) a custom method findAllByc which, as the name suggests, should be able to find zero to many rows of the table t where c column matches the given string.

Unfortunately, this only works when I try to search for row with unique value in the c column, for example, findAllByc("b");. When the c column is not unique, this method behaves very strangely. It works, it finds correct number of rows, but, strange thing which it does is that it finds only the first row in the table that matches the given c value, and then it simply copies that row the number of times of the count of the rows that match given c value.

So, for example, for this sample table, if I execute findAllByc("a"); the result will be:

a|a1
a|a1
a|a1

So, as you can see from the sample table which I provided above, a1 value is written only once in the table where c column is equal to a. So what Spring Boot does is that it simply copies the first row that matches into all other rows.

Why, on the Earh, Spring would do that??? This is very strange and unexpected behaviour.

This only occurs on the column which is marked as table's id. So, for example, if I declare findAllByc2 method, then everything works as expected, that is, if I call that method and give it, for example, a2 as parameter, it will find only one row, and if I give it a1 as a parameter, it will find both rows, and will not duplicate them, for example:

a|a1
b|a1

So, I know that the cause of the problem is that c is marked as id, and id is supposed to be unique, but that should be true only for "*ById" methods which construct the queries based on the id. I do not use these methods for this table. The method which I use is "Byc" method, which means that it will use c when constructing queries, and not id. In this case, c is the same as id, I know. But the difference between findById and findAllByc method should be in that that findById method can assume that id is unique and perform some optimisations, simplifications or whatever it does based on that information, but findAllByc can not since when searching by some column in the table, you can not assume that that column is unique. Even one more reason why Spring should not assume that that column is unique is because I added "All" to the method name, which indicates to the Spring Boot that I expect this method to return a list which may contain more than one item. So how could Spring assume that c is unique inside this method when it returnes a list of rows. So if row is unique, then how could there be multiple rows with the same id? It simply does not make any sense to assume this.

Since I did not implement this method, but merely declared it, and let the Spring implement it, this, I would say, is a bug in the Spring Boot.

In any case, I went to investigate how Spring Boot parses this method name and converts it into query, and it turned out that it does it correctly! Just like one would expect it to parse this method name. I went on the server log and looked up what query is sent to the server by my program when findAllByc("a"); is called and this is the result written in the server log at the time of program execution:

select t0_.c as c1_0_, t0_.c2 as c2_0_ from s.t t0_ where t0_.c=$1  
parameters: $1 = 'a'

As you can see, Spring Boot constructed the correct query from the method name, but still managed to mess up the results.

Of course, if I directly execute this query using plain Java Data Base Connectivity Application Programming Interface (so by bypassing Spring and Hibernate), the returned result is as expected:

a|a1
a|a2
a|a3

So why does Spring Boot messes up results despite that correct query is used, and correct table snippet really is returned from the server? Why does it duplicate rows?

Sure, quick fix is to mark both c and c2 columns with Id annotation in my t class, to explicitely tell Spring that c can not be assumed to be unique, since it is only part of the id, but, first of all, why would I have to do that. It is clear from the method name what I want, and correct Structured Query Language query is the proof that Spring Boot understood me perfectly well.

And secondly, marking both columns as id would work just fine if both as and bs can occur zero to many times in the c column. But, in this case as can occur zero to many times, but a b must occur once and only once in the column. So, if I would to mark both c and c2 as id, I would get that Spring Boot treats a b in the c column as being just one of multiple bs. It would allow multiple bs to be inserted into the table.

For example, if I mark both columns as being ids, and then try to update c2 value of a b in c column by using the save method, Spring would simply think of this entity as being another entity and would simply add another b into the table, which should not happen.

So, I want entities with a in the c column to be inserted into the table when save method is called and the one with b in the c column to be updated when this method is called. Since save is not splited into insert and update methods, I can not tell Spring explicitely whether I want it to perform an update or insert operation. So that is why I can not set both columns as id. I mean save does not work now as it is because it only updates the first row with a in the c column instead of inserting it, but that is another problem. I will override the save method in tr repository, so this is not important for this question. Focus in this question is in findAllByc method.

Sure, the best solution is to add id column in the t table and give it some random number to make Spring happy and then move the Id annotation to that field in t class, sure, but why would I be required to do so when findAllByc is recognized correctly and correct query is produced. Spring simply messes up when extracting the data returned from the server. Beside, this only generates useless data in the database table where I simply do not need ids. If I were to add id column, I would never use it. Is it really worh wasting data storage just to make Spring happy?

Is this a bug?

Can I fix it on some other way without adding unique id column to the table?