[FIXED] Invalid path in HQL and weird substitutions

Issue

I hope you can help me with this.

I am working on the upgrade of a customized installation of DSpace from 5.5 to 6.3 and I am running into a weird issue with HQL.

The SQL that I am trying to implement is this:

SELECT bt.* FROM bitstream AS bt 
    INNER JOIN authorprofile2bitstream AS ap2b 
    ON bt.bitstream_id=ap2b.bitstream_legacy_id 
    WHERE ap2b.authorprofile_id='xxx';

and this is the HQL that I wrote in my code and it is supposed to do the same:

SELECT bt FROM Bitstream bt, AuthorProfile2Bitstream ap2b 
    WHERE bt.legacyId=ap2b.bitstream_legacy_id AND AuthorProfile2Bitstream.authorprofile_id=:apid

and this is the error that it is raising:

org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: '8.authorprofile_id' [SELECT bt FROM org.dspace.content.Bitstream bt, org.dspace.content.AuthorProfile2Bitstream ap2b WHERE bt.legacyId=ap2b.bitstream_legacy_id AND AuthorProfile2Bitstream.authorprofile_id=:apid]

First question: Why it is changing AuthorProfile2Bitstream to 8?

Second: If it is correctly finding the AuthorProfile2Bitstream class (as per the expanded query showed in the exception), then question #1 again.

Third: Is this the way to represent that join?

Thanks in advance,

These are my classes:

Bitstream

@Entity
@Table(name="bitstream")
public class Bitstream extends DSpaceObject implements DSpaceObjectLegacySupport
{
    @Column(name="bitstream_id", insertable = false, updatable = false)
    private Integer legacyId;

    @Column(name = "sequence_id")
    private Integer sequenceId = -1;

    @Column(name = "checksum", length = 64)
    private String checksum;

    @Column(name = "checksum_algorithm", length = 32)
    private String checksumAlgorithm;

    @Column(name = "size_bytes")
    private long sizeBytes;

    @Column(name = "deleted")
    private boolean deleted = false;

    @Column(name = "internal_id", length = 256)
    private String internalId;

    @Column(name = "store_number")
    private int storeNumber;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "bitstream_format_id")
    private BitstreamFormat bitstreamFormat;

    @ManyToMany(fetch = FetchType.LAZY, mappedBy = "bitstreams")
    private List<Bundle> bundles = new ArrayList<>();

    @OneToOne(fetch = FetchType.LAZY, mappedBy="logo")
    private Community community;

    @OneToOne(fetch = FetchType.LAZY, mappedBy="logo")
    private Collection collection;

AuthorProfile2Bitstream

@Entity
@Table(name="authorprofile2bitstream")
public class AuthorProfile2Bitstream extends DSpaceObject implements DSpaceObjectLegacySupport
{

    @Column(name="id")
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private int id;
    
    @Column(name = "authorprofile_id")
    private UUID authorprofile_id;
    
    @Column(name = "bitstream_id")
    private UUID bitstream_id;
    
    @Column(name = "bitstream_legacy_id")
    private int bitstream_legacy_id;

And these are their representations in DB:

Bitstream

# \d bitstream
                                Table "public.bitstream"
       Column        |          Type          | Collation | Nullable |      Default
---------------------+------------------------+-----------+----------+-------------------
 bitstream_id        | integer                |           |          |
 bitstream_format_id | integer                |           |          |
 size_bytes          | bigint                 |           |          |
 checksum            | character varying(64)  |           |          |
 checksum_algorithm  | character varying(32)  |           |          |
 internal_id         | character varying(256) |           |          |
 deleted             | boolean                |           |          |
 store_number        | integer                |           |          |
 sequence_id         | integer                |           |          |
 uuid                | uuid                   |           | not null | gen_random_uuid()
Indexes:
    "bitstream_pkey" PRIMARY KEY, btree (uuid)
    "bitstream_id_unique" UNIQUE CONSTRAINT, btree (uuid)
    "bitstream_uuid_key" UNIQUE CONSTRAINT, btree (uuid)
    "bit_bitstream_fk_idx" btree (bitstream_format_id)
    "bitstream_id_idx" btree (bitstream_id)
Foreign-key constraints:
    "bitstream_bitstream_format_id_fkey" FOREIGN KEY (bitstream_format_id) REFERENCES bitstreamformatregistry(bitstream_format_id)
    "bitstream_uuid_fkey" FOREIGN KEY (uuid) REFERENCES dspaceobject(uuid)
Referenced by:
    TABLE "authorprofile2bitstream" CONSTRAINT "authorprofile2bitstream_bitstream_id_fkey" FOREIGN KEY (bitstream_id) REFERENCES bitstream(uuid)
    TABLE "bundle2bitstream" CONSTRAINT "bundle2bitstream_bitstream_id_fkey" FOREIGN KEY (bitstream_id) REFERENCES bitstream(uuid)
    TABLE "bundle" CONSTRAINT "bundle_primary_bitstream_id_fkey" FOREIGN KEY (primary_bitstream_id) REFERENCES bitstream(uuid)
    TABLE "checksum_history" CONSTRAINT "checksum_history_bitstream_id_fkey" FOREIGN KEY (bitstream_id) REFERENCES bitstream(uuid)
    TABLE "community" CONSTRAINT "community_logo_bitstream_id_fkey" FOREIGN KEY (logo_bitstream_id) REFERENCES bitstream(uuid)
    TABLE "most_recent_checksum" CONSTRAINT "most_recent_checksum_bitstream_id_fkey" FOREIGN KEY (bitstream_id) REFERENCES bitstream(uuid)
    TABLE "requestitem" CONSTRAINT "requestitem_bitstream_id_fkey" FOREIGN KEY (bitstream_id) REFERENCES bitstream(uuid)

AuthorProfile2Bitstream

# \d authorprofile2bitstream
                  Table "public.authorprofile2bitstream"
       Column        |  Type   | Collation | Nullable |      Default
---------------------+---------+-----------+----------+-------------------
 id                  | integer |           | not null |
 bitstream_legacy_id | integer |           |          |
 uuid                | uuid    |           | not null | gen_random_uuid()
 authorprofile_id    | uuid    |           |          |
 bitstream_id        | uuid    |           |          |
Indexes:
    "authorprofile2bitstream_pkey" PRIMARY KEY, btree (uuid)
    "authorprofile2bitstream_id_unique" UNIQUE CONSTRAINT, btree (uuid)
    "authorprofile2bitstream_uuid_key" UNIQUE CONSTRAINT, btree (uuid)
    "authorprofile2bitstream_authorprofile_idx" btree (authorprofile_id)
    "authorprofile2bitstream_bitstream_fk_idx" btree (bitstream_legacy_id)
    "authorprofile2bitstream_bitstream_idx" btree (bitstream_id)
Foreign-key constraints:
    "authorprofile2bitstream_authorprofile_id_fkey" FOREIGN KEY (authorprofile_id) REFERENCES authorprofile(uuid)
    "authorprofile2bitstream_bitstream_id_fkey" FOREIGN KEY (bitstream_id) REFERENCES bitstream(uuid)
    "authorprofile2bitstream_uuid_fkey" FOREIGN KEY (uuid) REFERENCES dspaceobject(uuid)

Solution

OK, it is true that sometimes you just need to see your things posted somewhere or elaborate for another people to get the answer on your own.

My mistake was that in the WHERE clause I wasn't referencing the AuthorProfile2Bistream instance ap2b but the class itself.

So, the correct (and now working) HQL query is this

SELECT bt FROM Bitstream bt, AuthorProfile2Bitstream ap2b 
    WHERE bt.legacyId=ap2b.bitstream_legacy_id AND ap2b.authorprofile_id=:apid

Answered By - Daniel Ponticelli
Answer Checked By - Marie Seifert (JavaFixing Admin)