{"action":"created","issue":{"url":"https://api.github.com/repos/h2database/h2database/issues/1402","repository_url":"https://api.github.com/repos/h2database/h2database","labels_url":"https://api.github.com/repos/h2database/h2database/issues/1402/labels{/name}","comments_url":"https://api.github.com/repos/h2database/h2database/issues/1402/comments","events_url":"https://api.github.com/repos/h2database/h2database/issues/1402/events","html_url":"https://github.com/h2database/h2database/pull/1402","id":354150312,"node_id":"MDExOlB1bGxSZXF1ZXN0MjEwOTg1NDA0","number":1402,"title":"Duplicate conditions in column check constraint","user":{"login":"andreitokar","id":8950218,"node_id":"MDQ6VXNlcjg5NTAyMTg=","avatar_url":"https://avatars2.githubusercontent.com/u/8950218?v=4","gravatar_id":"","url":"https://api.github.com/users/andreitokar","html_url":"https://github.com/andreitokar","followers_url":"https://api.github.com/users/andreitokar/followers","following_url":"https://api.github.com/users/andreitokar/following{/other_user}","gists_url":"https://api.github.com/users/andreitokar/gists{/gist_id}","starred_url":"https://api.github.com/users/andreitokar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/andreitokar/subscriptions","organizations_url":"https://api.github.com/users/andreitokar/orgs","repos_url":"https://api.github.com/users/andreitokar/repos","events_url":"https://api.github.com/users/andreitokar/events{/privacy}","received_events_url":"https://api.github.com/users/andreitokar/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2018-08-27T01:32:25Z","updated_at":"2018-08-27T10:16:42Z","closed_at":null,"author_association":"CONTRIBUTOR","pull_request":{"url":"https://api.github.com/repos/h2database/h2database/pulls/1402","html_url":"https://github.com/h2database/h2database/pull/1402","diff_url":"https://github.com/h2database/h2database/pull/1402.diff","patch_url":"https://github.com/h2database/h2database/pull/1402.patch"},"body":"This PR fixes two problems in Column. addCheckConstraint()\r\n1. Column resolver can not be redefined, because it is used as a value holder.\r\n2. When user-defined type (domain) is used as column type and it has a check constraint, this constraint does not retain it's origin, so columns SQL will have both reference to user-defined type and check constraint inherited. This may eventually lead to constraint duplication, i.e. when reconnect happened. Here is a manifestation:\r\n```\r\n15:08:26 11:03:44.527 org.h2.test.scripts.TestScript Running commands in testScript.sql\r\nERROR: org/h2/test/scripts/testScript.sql\r\nline: 2575\r\nexp: > ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\ngot: > ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n------------------------------\r\nERROR: org/h2/test/scripts/testScript.sql\r\nline: 2586\r\nexp: > CREATE MEMORY TABLE PUBLIC.ADDRESS( ID INT NOT NULL, NAME EMAIL CHECK (POSITION('@', NAME) > 1), NAME2 GMAIL DEFAULT '@gmail.com' CHECK ((POSITION('@', NAME2) > 1) AND (POSITION('gmail', NAME2) > 1)) );\r\ngot: > CREATE MEMORY TABLE PUBLIC.ADDRESS( ID INT NOT NULL, NAME EMAIL CHECK ((POSITION('@', NAME) > 1) AND (POSITION('@', NAME) > 1)), NAME2 GMAIL DEFAULT '@gmail.com' CHECK (((POSITION('@', NAME2) > 1) AND (POSITION('gmail', NAME2) > 1)) AND ((POSITION('@', NAME2) > 1) AND (POSITION('gmail', NAME2) > 1))) );\r\n------------------------------\r\nException in thread \"main\" java.lang.Exception: errors in testScript.sql found\r\n\tat org.h2.test.scripts.TestScript.testScript(TestScript.java:223)\r\n\tat org.h2.test.scripts.TestScript.test(TestScript.java:110)\r\n\tat org.h2.test.scripts.TestScript.main(TestScript.java:74)\r\n```\r\nWhile de-duplication  uses hacky SQL comparison, my attempt to use straightforward approach by implementing Expression.equals(), has failed, because assumptions are made elsewhere (by using expression as a key in HashMap), that it is not overridden and expression instance is equal to itself only. This smells fishy and somewhat counter-intuitive.\r\n\r\nAnother change here disables re-connection while running a few test sql scripts, where session state may be lost. It will avoid the following exceptions:\r\n```\r\n15:10:46 11:06:04.892 org.h2.test.scripts.TestScript Running commands in ddl/createAlias.sql\r\nException in thread \"main\" org.h2.jdbc.JdbcSQLException: Function alias \"TRUNC\" already exists; SQL statement:\r\nCREATE FORCE ALIAS PUBLIC.TRUNC FOR \"java.lang.Math.floor(double)\" [90076-198]\r\n\tat org.h2.message.DbException.getJdbcSQLException(DbException.java:394)\r\n\tat org.h2.message.DbException.get(DbException.java:182)\r\n\tat org.h2.message.DbException.get(DbException.java:158)\r\n\tat org.h2.command.Parser.parseCreateFunctionAlias(Parser.java:5630)\r\n\tat org.h2.command.Parser.parseCreate(Parser.java:5090)\r\n\tat org.h2.command.Parser.parsePrepared(Parser.java:729)\r\n\tat org.h2.command.Parser.parse(Parser.java:673)\r\n\tat org.h2.command.Parser.parse(Parser.java:645)\r\n\tat org.h2.command.Parser.prepare(Parser.java:600)\r\n\tat org.h2.engine.Session.prepare(Session.java:588)\r\n\tat org.h2.engine.Session.prepare(Session.java:572)\r\n\tat org.h2.engine.MetaRecord.execute(MetaRecord.java:58)\r\n\tat org.h2.engine.Database.open(Database.java:812)\r\n\tat org.h2.engine.Database.openDatabase(Database.java:317)\r\n\tat org.h2.engine.Database.<init>(Database.java:311)\r\n\tat org.h2.engine.Engine.openSession(Engine.java:69)\r\n\tat org.h2.engine.Engine.openSession(Engine.java:200)\r\n\tat org.h2.engine.Engine.createSessionAndValidate(Engine.java:178)\r\n\tat org.h2.engine.Engine.createSession(Engine.java:161)\r\n\tat org.h2.engine.Engine.createSession(Engine.java:31)\r\n\tat org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:335)\r\n\tat org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:123)\r\n\tat org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:102)\r\n\tat org.h2.Driver.connect(Driver.java:69)\r\n\tat java.sql.DriverManager.getConnection(DriverManager.java:664)\r\n\tat java.sql.DriverManager.getConnection(DriverManager.java:247)\r\n\tat org.h2.test.TestDb.getConnectionInternal(TestDb.java:166)\r\n\tat org.h2.test.TestDb.getConnection(TestDb.java:38)\r\n\tat org.h2.test.scripts.TestScript.reconnect(TestScript.java:372)\r\n\tat org.h2.test.scripts.TestScript.process(TestScript.java:336)\r\n\tat org.h2.test.scripts.TestScript.testFile(TestScript.java:298)\r\n\tat org.h2.test.scripts.TestScript.testScript(TestScript.java:219)\r\n\tat org.h2.test.scripts.TestScript.test(TestScript.java:140)\r\n\tat org.h2.test.scripts.TestScript.main(TestScript.java:74)\r\n\r\n\r\n15:22:32 11:17:50.619 org.h2.test.scripts.TestScript Running commands in ddl/dropSchema.sql\r\nException in thread \"main\" org.h2.jdbc.JdbcSQLException: Schema \"TEST_SCHEMA\" not found; SQL statement:\r\nCREATE SYNONYM TEST_SCHEMA.TEST FOR PUBLIC.SRC [90079-198]\r\n\tat org.h2.message.DbException.getJdbcSQLException(DbException.java:394)\r\n\tat org.h2.message.DbException.get(DbException.java:182)\r\n\tat org.h2.message.DbException.get(DbException.java:158)\r\n\tat org.h2.command.Parser.getSchema(Parser.java:1025)\r\n\tat org.h2.command.Parser.getSchema(Parser.java:1031)\r\n\tat org.h2.command.Parser.parseCreateSynonym(Parser.java:7326)\r\n\tat org.h2.command.Parser.parseCreate(Parser.java:5143)\r\n\tat org.h2.command.Parser.parsePrepared(Parser.java:729)\r\n\tat org.h2.command.Parser.parse(Parser.java:673)\r\n\tat org.h2.command.Parser.parse(Parser.java:645)\r\n\tat org.h2.command.Parser.prepare(Parser.java:600)\r\n\tat org.h2.engine.Session.prepare(Session.java:588)\r\n\tat org.h2.engine.Session.prepare(Session.java:572)\r\n\tat org.h2.engine.MetaRecord.execute(MetaRecord.java:58)\r\n\tat org.h2.engine.Database.open(Database.java:812)\r\n\tat org.h2.engine.Database.openDatabase(Database.java:317)\r\n\tat org.h2.engine.Database.<init>(Database.java:311)\r\n\tat org.h2.engine.Engine.openSession(Engine.java:69)\r\n\tat org.h2.engine.Engine.openSession(Engine.java:200)\r\n\tat org.h2.engine.Engine.createSessionAndValidate(Engine.java:178)\r\n\tat org.h2.engine.Engine.createSession(Engine.java:161)\r\n\tat org.h2.engine.Engine.createSession(Engine.java:31)\r\n\tat org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:335)\r\n\tat org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:123)\r\n\tat org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:102)\r\n\tat org.h2.Driver.connect(Driver.java:69)\r\n\tat java.sql.DriverManager.getConnection(DriverManager.java:664)\r\n\tat java.sql.DriverManager.getConnection(DriverManager.java:247)\r\n\tat org.h2.test.TestDb.getConnectionInternal(TestDb.java:166)\r\n\tat org.h2.test.TestDb.getConnection(TestDb.java:38)\r\n\tat org.h2.test.scripts.TestScript.reconnect(TestScript.java:378)\r\n\tat org.h2.test.scripts.TestScript.process(TestScript.java:342)\r\n\tat org.h2.test.scripts.TestScript.testFile(TestScript.java:304)\r\n\tat org.h2.test.scripts.TestScript.testScript(TestScript.java:219)\r\n\tat org.h2.test.scripts.TestScript.test(TestScript.java:140)\r\n\tat org.h2.test.scripts.TestScript.main(TestScript.java:74)\r\n\r\n15:26:42 11:22:00.985 org.h2.test.scripts.TestScript Running commands in functions/numeric/rand.sql\r\nERROR: org/h2/test/scripts/functions/numeric/rand.sql\r\nline: 19\r\nexp: >> 0.20771484130971707\r\ngot: >> 0.7777427184534654\r\n------------------------------\r\nException in thread \"main\" java.lang.Exception: errors in functions/numeric/rand.sql found\r\n\tat org.h2.test.scripts.TestScript.testScript(TestScript.java:229)\r\n\tat org.h2.test.scripts.TestScript.test(TestScript.java:161)\r\n\tat org.h2.test.scripts.TestScript.main(TestScript.java:74)\r\n\r\n```"},"comment":{"url":"https://api.github.com/repos/h2database/h2database/issues/comments/416181639","html_url":"https://github.com/h2database/h2database/pull/1402#issuecomment-416181639","issue_url":"https://api.github.com/repos/h2database/h2database/issues/1402","id":416181639,"node_id":"MDEyOklzc3VlQ29tbWVudDQxNjE4MTYzOQ==","user":{"login":"andreitokar","id":8950218,"node_id":"MDQ6VXNlcjg5NTAyMTg=","avatar_url":"https://avatars2.githubusercontent.com/u/8950218?v=4","gravatar_id":"","url":"https://api.github.com/users/andreitokar","html_url":"https://github.com/andreitokar","followers_url":"https://api.github.com/users/andreitokar/followers","following_url":"https://api.github.com/users/andreitokar/following{/other_user}","gists_url":"https://api.github.com/users/andreitokar/gists{/gist_id}","starred_url":"https://api.github.com/users/andreitokar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/andreitokar/subscriptions","organizations_url":"https://api.github.com/users/andreitokar/orgs","repos_url":"https://api.github.com/users/andreitokar/repos","events_url":"https://api.github.com/users/andreitokar/events{/privacy}","received_events_url":"https://api.github.com/users/andreitokar/received_events","type":"User","site_admin":false},"created_at":"2018-08-27T10:16:42Z","updated_at":"2018-08-27T10:16:42Z","author_association":"CONTRIBUTOR","body":"If you ran TestScript in \"big\" mode - exception from PR description, shows condition duplication.\r\nSCRIPT command output, whwrw you have user-defined type (i.e. \"gmail\" in this case) and check constraint, inherited from it, but mentioned explicitely, is a problem right there.\r\nYes, we can analyze AND sub-expressions now for even better dup handling now, But this one closes immediate problem."}}
{"actor":{"display_login":"andreitokar"}}