SQL dialect error after update

alextarasyuk79 · August 31, 2024, 7:19pm

In
@Query(value = “SELECT cast((EXTRACT(HOUR FROM AVG(j.finished - j.started)) * 3600 * 1000 + EXTRACT(MINUTE FROM AVG(j.finished - j.started)) * 60 * 1000 + EXTRACT(SECOND FROM AVG(j.finished - j.started)) * 1000 + EXTRACT(MILLISECONDS FROM AVG(j.finished - j.started))) as long) FROM JobFile jf LEFT JOIN jf.job j WHERE j.finished >= :date AND j.status = 2 AND jf.advancedProcessing = :advanced”)
Long getAverageTimeAdvanced(@Param(“date”) Date date, @Param(“advanced”) boolean b);

I face the following exception after update
Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.Date org.epo.cc.file.malwareinspector.hibernate.repository.JobRepository.getAverageTimeAdvanced(java.util.Date,boolean)
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:100)
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.(SimpleJpaQuery.java:70)
at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:60)
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:170)
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:252)
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:95)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:111)
… 92 more
Caused by: java.lang.IllegalArgumentException: org.hibernate.query.SyntaxException: At 1:218 and token ‘FROM’, no viable alternative at input ‘SELECT cast((EXTRACT(HOUR FROM AVG(j.finished - j.started)) * 3600 * 1000 + EXTRACT(MINUTE FROM AVG(j.finished - j.started)) * 60 * 1000 + EXTRACT(SECOND FROM AVG(j.finished - j.started)) * 1000 + EXTRACT(MILLISECONDS *FROM AVG(j.finished - j.started))) as date) FROM JobFile jf LEFT JOIN jf.job j WHERE j.finished >= :date AND j.status = 2 AND jf.advancedProcessing = :advanced’ [SELECT cast((EXTRACT(HOUR FROM AVG(j.finished - j.started)) * 3600 * 1000 + EXTRACT(MINUTE FROM AVG(j.finished - j.started)) * 60 * 1000 + EXTRACT(SECOND FROM AVG(j.finished - j.started)) * 1000 + EXTRACT(MILLISECONDS FROM AVG(j.finished - j.started))) as date) FROM JobFile jf LEFT JOIN jf.job j WHERE j.finished >= :date AND j.status = 2 AND jf.advancedProcessing = :advanced]

Would be grateful for a hint

mbladel · September 2, 2024, 6:46am

The query looks fine to me syntax-wise, is you entity named JobFile? Please post the full stack trace of the Hibernate SyntaxException, not the Spring error that’s wrapping it please.

alextarasyuk79 · September 2, 2024, 7:26am

Caused by: org.hibernate.query.SyntaxException: At 1:218 and token ‘FROM’, no viable alternative at input ‘SELECT cast((EXTRACT(HOUR FROM AVG(j.finished - j.started)) * 3600 * 1000 + EXTRACT(MINUTE FROM AVG(j.finished - j.started)) * 60 * 1000 + EXTRACT(SECOND FROM AVG(j.finished - j.started)) * 1000 + EXTRACT(MILLISECONDS *FROM AVG(j.finished - j.started))) as long) FROM JobFile jf LEFT JOIN jf.job j WHERE j.finished >= :date AND j.status = 2 AND jf.advancedProcessing = :advanced’ [SELECT cast((EXTRACT(HOUR FROM AVG(j.finished - j.started)) * 3600 * 1000 + EXTRACT(MINUTE FROM AVG(j.finished - j.started)) * 60 * 1000 + EXTRACT(SECOND FROM AVG(j.finished - j.started)) * 1000 + EXTRACT(MILLISECONDS FROM AVG(j.finished - j.started))) as long) FROM JobFile jf LEFT JOIN jf.job j WHERE j.finished >= :date AND j.status = 2 AND jf.advancedProcessing = :advanced]
at org.hibernate.query.hql.internal.StandardHqlTranslator$1.syntaxError(StandardHqlTranslator.java:109) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41) ~[antlr4-runtime-4.13.0.jar:4.13.0]
at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544) ~[antlr4-runtime-4.13.0.jar:4.13.0]
at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310) ~[antlr4-runtime-4.13.0.jar:4.13.0]
at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136) ~[antlr4-runtime-4.13.0.jar:4.13.0]
at org.hibernate.grammars.hql.HqlParser.queryExpression(HqlParser.java:2072) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.grammars.hql.HqlParser.selectStatement(HqlParser.java:410) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.grammars.hql.HqlParser.statement(HqlParser.java:340) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.query.hql.internal.StandardHqlTranslator.parseHql(StandardHqlTranslator.java:133) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:67) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.createHqlInterpretation(QueryInterpretationCacheStandardImpl.java:145) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheStandardImpl.java:132) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.internal.AbstractSharedSessionContract.interpretHql(AbstractSharedSessionContract.java:802) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:852) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
… 162 common frames omitted

alextarasyuk79 · September 2, 2024, 7:29am

ALTER TABLE job ADD COLUMN started TIMESTAMP;
ALTER TABLE job ADD COLUMN finished TIMESTAMP;

mbladel · September 2, 2024, 7:34am

You didn’t reply. If you think this is an Hibernate bug, I suggest trying to reproduce the problem using our test case template and if you are able to reproduce the issue, create a new ticket in our issue tracker and attach that reproducer .

alextarasyuk79 · September 2, 2024, 7:58am

CREATE TABLE job_file(
id BIGSERIAL NOT NULL,
status INT NOT NULL,
allowed INT NOT NULL,
job_id UUID NOT NULL,
file_id BIGINT NOT NULL,
file_path VARCHAR(255) NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
uid VARCHAR(255),
mdc_data_id VARCHAR(32),
mdc_server VARCHAR(255),
PRIMARY KEY (id),
FOREIGN KEY (job_id)
REFERENCES job(id)
ON DELETE CASCADE,
FOREIGN KEY (file_id)
REFERENCES uploaded_file(id)
ON DELETE CASCADE
);

alextarasyuk79 · September 2, 2024, 8:20am

The functionality worked before update. But after fails. So I am bewildered

mbladel · September 2, 2024, 8:48am

I’m asking you about your entity name and you are pasting DDL statements, I don’t know what that has to do with it. HQL language is based on your domain model, so entity names and java properties rather than SQL tables / column.

I already told you how to proceed if you believe this is an Hibernate bug, otherwise if you won’t provide the details I asked for I cannot help you further.

alextarasyuk79 · September 2, 2024, 9:06am

@Entity
@Table(name = "job_file")
public class JobFile {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", updatable = false)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "job_id")
    private Job job;

    @Column(name = "retry_count", nullable = false)
    private int retryCount = 0;

    @ManyToOne
    @JoinColumn(name = "file_id")
    private UploadedFile uploadedFile;

    @Column(name = "allowed", nullable = true)
    @Expose
    private int allowed = 0;

    @Column(name = "advanced_processing", nullable = false)
    @Expose
    @SerializedName("advanced")
    private Boolean advancedProcessing = false;

    @Column(name = "has_warning", nullable = false)
    @Expose
    @SerializedName("warning")
    private Boolean hasWarning = false;

    @Column(name = "uid", nullable = true)
    @Expose
    private String uid;

    @Column(name = "mdc_data_id", nullable = true)
    private String mdcDataId;

    @Column(name = "mdc_server", nullable = true)
    private String mdcServer;

    @Column(name = "blocked_reason", nullable = true)
    private String blockedReason;

    @Column(name = "threat", nullable = true)
    private String threat;

    @Column(name = "status", nullable = false)
    private Integer status = 0;

    @Column(name = "file_path", nullable = true)
    private String filePath;

    @Column(name = "file_type", nullable = true)
    private String fileType;

    @CreationTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "created", updatable = true)
    private Date created;

    @UpdateTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "updated", updatable = true)
    private Date updated;

alextarasyuk79 · September 2, 2024, 9:08am

@Entity
@Table(name = "job")
public class Job {
    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    @Column(columnDefinition = "uuid", updatable = false)
    @Expose
    private UUID id;

    @Column(name = "kafka_id")
    private UUID kafkaId;

    @Column(name = "retry_count", nullable = false)
    private int retryCount = 0;

    @PrePersist
    public void initializeUUID() {
        if (kafkaId == null) {
            kafkaId = UUID.randomUUID();
        }
    }

    @Column(name = "status", nullable = false)
    @Expose
    private Integer status;

    @Column(name = "callbackurl", nullable = true)
    private String callbackurl;

    @Column(name = "source", nullable = true)
    private String source;

    @Column(name = "trace_id", nullable = true)
    private String traceId;

    @Column(name = "client", nullable = true)
    private String client;

    public UUID getId() {
        return id;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    @OneToMany(cascade = PERSIST, fetch = FetchType.EAGER)
    @JoinColumn(name = "job_id")
    @Expose
    private List<JobFile> files = new ArrayList<>();

    @CreationTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "created", updatable = true)
    private Date created;

    public List<JobFile> getFiles() {
        return files;
    }

    public void setFiles(List<JobFile> files) {
        this.files = files;
    }

    @UpdateTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "updated", updatable = true)
    private Date updated;

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "started", updatable = true)
    private Date started;

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "finished", updatable = true)
    private Date finished;

    @Column(name = "processing_time", updatable = true, nullable = true)
    private Date processing;

    @Column(name = "quick_scan", updatable = true)
    private Boolean quickScan;

mbladel · September 2, 2024, 9:13am

Once again, I don’t see any issues from the Hibernate side, but I cannot help you with any of the other components your application is using. The only suggestion I can give you is, once again, to try reproducing the issue using Hibernate only if you believe this might be an Hibernate bug.

Topic		Replies	Views
How to fix SQL Error: 1013, SQLState: 72000 Hibernate ORM	4	5695	September 29, 2021
Got IllegalArgumentException after hibernat upgrade Hibernate ORM	1	831	September 22, 2023
Semantic exception on emptyString and GetDate Hibernate ORM	3	43	September 19, 2024
Migration of 5.2.17.FINAL to 6.4.1 problems Hibernate ORM	4	717	April 24, 2024
Query logging in Hibernate 6 causes an Exception when a query contains a % (LIKE qerry) Hibernate ORM	5	1443	June 23, 2023

SQL dialect error after update

Related Topics