INSERT INTO table SELECT not working with constant values in H2 DB

Question

Following is the H2DB query

 INSERT INTO userpermission (userid, permissionid, createddt, createdby, updateddt, updatedby)
            SELECT u.userid, p.permissionid, now(), ("1"), now(), ("1") FROM user u, permission p
            WHERE u.email = "[email protected]";

What I am trying to achieve is to insert a record into userpermission for user with given email address in user table, for each permission in permission table.

Following is the error

 Reason: liquibase.exception.DatabaseException: Column "1" not found; SQL statement:
                                    INSERT INTO userpermission (userid, permissionid, createddt, createdby, updateddt, updatedby)
                                                SELECT u.userid, p.permissionid, now(), ("1"), now(), ("1") FROM user u, permission p
                                                WHERE u.email = "[email protected]" [42122-193] [Failed SQL: INSERT INTO userpermission (userid, permissionid, createddt, createdby, updateddt, updatedby)
                                                SELECT u.userid, p.permissionid, now(), ("1"), now(), ("1") FROM user u, permission p
                                                WHERE u.email = "[email protected]"]

The same query is working in MySQL.

Answer 1

As mentioned in the comments,

1. Use single quotes for sting constants.
2. Do not use braces with constants in select clause.
3. Check the join condition. Without matching 2 tables based on common column, you are doing a Cartesian product

Answer 2

I encountered a similar issue. I want to insert some default value into my H2 when my springboot app first runs. I had the schema.sql and data.sql all correct.

However, It still won't load the sample data into the H2 Schema table when I do select *. after looking around for a hour. I decided to look into the application.properties file. I changed

spring.jpa.hibernate.ddl-auto=create-drop

to spring.jpa.hibernate.ddl-auto=none

or spring.jpa.hibernate.ddl-auto=update

both of them work. just in case you wondering why both none and update works, here is why