#1700 INSERT using SELECT from data.sql, unexpected token: SELECT

[Jose Luis Bernal Zambrano]

Hi team,

I created tables using UUID. And I'm using an import.sql file:

CREATE TRIGGER TRIG_ROLES BEFORE INSERT ON SECURITY_ROLES REFERENCING NEW ROW AS NEW_ROLE FOR EACH ROW SET NEW_ROLE.ID = UUID();
CREATE TRIGGER TRIG_AUTHORITIES BEFORE INSERT ON SECURITY_AUTHORITIES REFERENCING NEW ROW AS NEW_AUTHORITY FOR EACH ROW SET NEW_AUTHORITY.ID = UUID();

INSERT INTO SECURITY_ROLES (name) VALUES('ADMIN');
INSERT INTO SECURITY_ROLES (name) VALUES('USER');
INSERT INTO SECURITY_ROLES (name) VALUES('GUEST');

INSERT INTO SECURITY_AUTHORITIES (authority) VALUES('READ_ALL');
INSERT INTO SECURITY_AUTHORITIES (authority) VALUES('READ_SELF');
INSERT INTO SECURITY_AUTHORITIES (authority) VALUES('CREATE');

When I performed these Queries I had the UUID well!

SELECT id FROM SECURITY_AUTHORITIES WHERE authority = 'READ_ALL';
SELECT id FROM SECURITY_ROLES WHERE name = 'ADMIN';

Unfortunately this INSERT doesn't work.

INSERT INTO ROLE_AUTHORITY_MAPPING (authority_id, role_id) VALUES ( SELECT id FROM SECURITY_AUTHORITIES WHERE authority = 'READ_SELF', SELECT id FROM SECURITY_ROLES WHERE name = 'USER');

The error Log was:

Hibernate: INSERT INTO ROLE_AUTHORITY_MAPPING (authority_id, role_id) VALUES ( SELECT id FROM SECURITY_AUTHORITIES WHERE authority = 'READ_SELF', SELECT id FROM SECURITY_ROLES WHERE name = 'USER')
2024-03-30T01:22:16.929-05:00  WARN 4732 --- [ms-people] [           main] o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error executing DDL "INSERT INTO ROLE_AUTHORITY_MAPPING (authority_id, role_id) VALUES ( SELECT id FROM SECURITY_AUTHORITIES WHERE authority = 'READ_SELF', SELECT id FROM SECURITY_ROLES WHERE name = 'USER')" via JDBC [unexpected token: SELECT]

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "INSERT INTO ROLE_AUTHORITY_MAPPING (authority_id, role_id) VALUES ( SELECT id FROM SECURITY_AUTHORITIES WHERE authority = 'READ_SELF', SELECT id FROM SECURITY_ROLES WHERE name = 'USER')" via JDBC [unexpected token: SELECT]
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:94) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.tool.schema.internal.Helper.applySqlString(Helper.java:233) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.tool.schema.internal.Helper.applyScript(Helper.java:255) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applyImportFiles(SchemaCreatorImpl.java:665) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applyImportSources(SchemaCreatorImpl.java:584) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:184) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:142) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:118) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:256) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]

I thought maybe is my import.sql file, but doing directly inside of the jar.tool fails too as can be seen in the image files.

Thanks in advance

[Fred Toussi]

Your INSERT statement is not valid SQL.
I can see what you want to achieve. You probably want only one row for GUEST, two rows for USER and three rows for ADMIN. In that case use a JOIN

INSERT INTO ROLE_AUTHORITY_MAPPING (authority_id, role_id) (SELECT security_authorities.id, security_roles.id FROM SECURITY_AUTHORITIES cross join SECURITY_ROLES WHERE authority = 'READ_SELF' and name = 'USER');