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

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 = "csr_admin@example.com";

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 = "csr_admin@example.com" [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 = "csr_admin@example.com"]

The same query is working in MySQL.

Answer

As mentioned in the comments,

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

Advertisement

Answer