Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

CREATE TABLE - default-values on columns #1022

Closed
gitmotte opened this issue Jul 26, 2020 · 2 comments · Fixed by #1778
Closed

CREATE TABLE - default-values on columns #1022

gitmotte opened this issue Jul 26, 2020 · 2 comments · Fixed by #1778

Comments

@gitmotte
Copy link
Contributor

gitmotte commented Jul 26, 2020

   CREATE TABLE myschema.tableName (
                id bigint NOT NULL DEFAULT nextval('myschema.mysequence'::regclass), 
                bool_col boolean NOT NULL DEFAULT false, 
                int_col integer NOT NULL DEFAULT 0)

Default-Values not supported currently

  • boolean (true|false)
  • nextval (postgresql syntax)
net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "nextval" "NEXTVAL"
    at line 1, column 61.

Was expecting one of:

    "("
    ")"
    "+"
    ","
    "-"
    "::"
    "="
    "ASC"
    "BY"
    "CASCADE"
    "CHECK"
    "COLLATE"
    "COMMENT"
    "COMMIT"
    "CONSTRAINT"
    "DEFAULT"
    "DELETE"
    "DESC"
    "EXCLUDE"
    "FOREIGN"
    "IN"
    "KEY"
    "NOT"
    "NULL"
    "ON"
    "PARTITION"
    "PRIMARY"
    "REFERENCES"
    "ROWS"
    "TABLESPACE"
    "TEMP"
    "TEMPORARY"
    "TYPE"
    "UNIQUE"
    "UNSIGNED"
    "UPDATE"
    "USING"
    "WHERE"
    "WITH"
    <K_TIME_KEY_EXPR>
    <S_CHAR_LITERAL>
    <S_DOUBLE>
    <S_IDENTIFIER>
    <S_LONG>
    <S_QUOTED_IDENTIFIER>

	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:165)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:59)
	at net.sf.jsqlparser.test.TestUtils.assertSqlCanBeParsedAndDeparsed(TestUtils.java:88)
	at net.sf.jsqlparser.test.TestUtils.assertSqlCanBeParsedAndDeparsed(TestUtils.java:74)
	at net.sf.jsqlparser.test.TestUtils.assertSqlCanBeParsedAndDeparsed(TestUtils.java:59)
	at net.sf.jsqlparser.statement.create.CreateTableTest.testCreateTableWithDefault(CreateTableTest.java:677)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:89)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:41)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:542)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:770)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:464)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:210)
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "nextval" "NEXTVAL"
    at line 1, column 61.

Was expecting one of:

    "("
    ")"
    "+"
    ","
    "-"
    "::"
    "="
    "ASC"
    "BY"
    "CASCADE"
    "CHECK"
    "COLLATE"
    "COMMENT"
    "COMMIT"
    "CONSTRAINT"
    "DEFAULT"
    "DELETE"
    "DESC"
    "EXCLUDE"
    "FOREIGN"
    "IN"
    "KEY"
    "NOT"
    "NULL"
    "ON"
    "PARTITION"
    "PRIMARY"
    "REFERENCES"
    "ROWS"
    "TABLESPACE"
    "TEMP"
    "TEMPORARY"
    "TYPE"
    "UNIQUE"
    "UNSIGNED"
    "UPDATE"
    "USING"
    "WHERE"
    "WITH"
    <K_TIME_KEY_EXPR>
    <S_CHAR_LITERAL>
    <S_DOUBLE>
    <S_IDENTIFIER>
    <S_LONG>
    <S_QUOTED_IDENTIFIER>

	at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:25083)
	at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:24927)
	at net.sf.jsqlparser.parser.CCJSqlParser.CreateTable(CCJSqlParser.java:13665)
	at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:175)
	at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:79)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:163)
	... 28 more

gitmotte added a commit to gitmotte/JSqlParser that referenced this issue Jul 26, 2020
gitmotte added a commit to gitmotte/JSqlParser that referenced this issue Jul 26, 2020
@mo3000
Copy link
Contributor

mo3000 commented Aug 30, 2020

I was looking into this but things got more complecated than I thought.

  • there's no boolean keyword so I created one.
  • default defination in column is usually diffrent from table's "default". So I rename the original CreateParameter to CreateTableParameter for table only. Then created a new CreateParameter with a different "default" part like:
List<String> CreateParameter():
{
    String retval = "";
    Token tk = null;
    Token tk2 = null;
...
            tk=<K_PRIMARY> { param.add(tk.image); }
            |
            <K_DEFAULT> {param.add("DEFAULT");}
                (
                    (tk=<K_NULL> | tk=<S_DOUBLE> | tk=<S_LONG> | tk=<K_FALSE> | tk=<K_TRUE> | tk=<S_CHAR_LITERAL> | tk=<K_TIME_KEY_EXPR>) {param.add(tk.image);}
                    |
                    ("+" {retval = "+";} | "-" {retval = "-";}) ?
                    (
                       tk=<S_LONG> { retval += tk.image; }
                       |
                       tk=<S_DOUBLE> { retval += tk.image; }
                    )
                    {param.add(retval);}
                    // place a new nextval() here
                )
            |
            tk=<K_FOREIGN> { param.add(tk.image); }
...

If someone write a new NextValExpression(), place it to the comment above.
sql

CREATE TABLE T1 (isok boolean DEFAULT true);
CREATE TABLE T1 (isok boolean DEFAULT false);

will work due to this change (copy from a working branch, all test passed), but then I realized default value can be expressions. as in the mysql manual:

CREATE TABLE t1 (
  -- literal defaults
  i INT         DEFAULT 0,
  c VARCHAR(10) DEFAULT '',
  -- expression defaults
  f FLOAT       DEFAULT (RAND() * RAND()),
  b BINARY(16)  DEFAULT (UUID_TO_BIN(UUID())),
  d DATE        DEFAULT (CURRENT_DATE + INTERVAL 1 YEAR),
  p POINT       DEFAULT (Point(0,0)),
  j JSON        DEFAULT (JSON_ARRAY())
);

In the latest change there's a SimpleExpression() seems to fit, but unfortunately default exprs can't have refference to user or system variables.
That's too hard for me, so I write down things I got and leave it to you guys -_-!.

@gitmotte
Copy link
Contributor Author

Thank you for your thoughts, I didn't have the time to handle it myself because it is so complex. But I have placed a marker branch with a simple test case ... there are several syntax variants of nextval out there and other expressions may be allowed as well, as you have already noticed.

wumpz pushed a commit that referenced this issue Nov 6, 2020
* * add with prefix for fluent setters.

 #1004

* add getters

* * add with prefix for fluent setters. (revert to chaining setters, do
not break current api)

 #1004

* * add with prefix for fluent setters. (revert to chaining setters, do
not break current api)

 #1004

* use new methods within testcases

* use new methods within testcases

* use new methods within testcases

* use new methods within testcases

* use new methods within testcases

* use new methods within testcases

* use new methods within testcases

* use new methods within testcases

* remove create() methods - they do not add enough value to be justified

* * use new methods within testcases
* add some constructors
* fix and add "with" / "add" methods

* * use new methods within testcases

* * use new methods within testcases
* add some constructors

* * renamed constant

* use new methods within testcases

* use new methods within testcases

* use new methods within testcases

* use new methods within testcases

* * use new methods within testcases
* add some with-methods
* add getter/setter named after the field without abbrivation

* * use new methods within testcases

* remove empty implicit constructor

* return the deparsed Statement - object

* compare object tree

* compare object tree

* * fix ObjectTreeToStringStyle
* compare object tree

* remove casts not needed

* * use new methods within testcases
* add some "set" "with" "add" methods missing

* * use new methods within testcases

* add empty constructors and override with-/add-methods returning concrete
type

* * add ReflectionModelTest

* * use new methods within testcases

* fix checkstyle errors

* license header

* remove test-classes from ReflectionModelTest

* remove visitoradapter-classes from ReflectionModelTest

* * add SelectDeParser(StringBuilder)
* remove overriding setters/getters of buffer
#1007

* push to synbee-contrib

org.synbee.commons.contrib:jsqlparser:3.2-0.0.6-SNAPSHOT

* add ValidationUtil for simple validation of one or more statements

* remove overrides of
* getCause
* printStackTrace variants

why add an additional cause ?

set cause.getMessage() the message within constructor
JSQLParserException(Throwable cause), othewise cause.toString() will be
set as default.

* add ValidationVisitor showcase
#1005

* add ValidationUtil for simple validation of one or more statements

* remove overrides of
* getCause
* printStackTrace variants

why add an additional cause ?

set cause.getMessage() the message within constructor
JSQLParserException(Throwable cause), othewise cause.toString() will be
set as default.

* visit(ShowTablesStatement)

* copyright/license

* add stubs (use deparsers as template)

* Merge branch 'master.validate' of
https://github.com/gitmotte/JSqlParser.git into master.validate

* add ValidationVisitor showcase
#1005

* add ValidationUtil for simple validation of one or more statements

* remove overrides of
* getCause
* printStackTrace variants

why add an additional cause ?

set cause.getMessage() the message within constructor
JSQLParserException(Throwable cause), othewise cause.toString() will be
set as default.

* visit(ShowTablesStatement)

* add stubs (use deparsers as template)

* Merge branch 'master.validate' of
https://github.com/gitmotte/JSqlParser.git into master.validate

* add tests for ValidationUtil

* + implements OrderByVisitor

* split Expressionvalidator which implements both ItemsListVisitor and
Expressionvisitor into Expressionvalidator and ItemListValidator

* Merge branch 'github.validate'

* implement upsertvalidator

* add copyright

* validate through given ValidationCapability's

* * switch to new method forced by
ValidationCapability.validate(ValidationContext context,
Consumer<String> errorMessageConsumer);
* add AllowedTypesValidation

* add FeatureConfiguration

* use FeatureConfiguration within parser

* repair pom.xml

* repair pom.xml

* repair pom.xml

* repair pom.xml

* * make FeatureConfiguration not a singleton any more
* CCJSqlParser extends AbstractJSqlParser<CCJSqlParser>
* add FeaturesAllowed for testing against features allowed

* implement some Validators

* basic implementation of DatabaseMetaDataValidation /
JdbcDatabaseMetaDataCapability

* moving classes to sub-packages

* * moving classes to sub-packages
* fixing some bugs

* repair pom.xml

* add and fix validations

* add javadoc

* * force definition of ```public String getMessage(Feature feature)```
in FeatureSetValidation
* allow all objects as feature-value - this may be needed by the parser,
if a none-boolean configuration is needed

* impl.
* SelectValidator.visit(PlainSelect)
* OrderByValidator

* add Version-enums

* impl.
* InsertValidator
* multiple implementations of visit(SubSelect) -> forward to
SelectValidator
* add some known features to SqlServerVersion

* refactoring enum-name should be upper case

* add ansi sql enum

* refactoring enum-name should be upper case

* implement limitvalidator

* + validateOffset

* + validateFetch

* + validate Pivot, UnPivot, PivotXml

* + implement DropValidator

* change testcase to image a more probably usecase

* * add javadoc and
* predefined sets for EXECUTE, ALTER, DROP
* allow to combine FeatureSets

* * implement executevalidator

* implement ExpressionValidator

* implement GrantValidator

* javadoc and complete SELECT constant

* use utility methods from AbstractValidator

* more user friendly names

* javadoc

* add subtypes for ValidationException
* ValidationParseException
* DatabaseException
* UnexpectedValidationException
and change Set<String> errors to Set<ValidationException> for collect.

* javadoc & rename exception

* rename method

* extract parsing task into package - private class for {@link
ValidationUtil} to parse the statements
 * within it's own {@link ValidationCapability}

* add null-check for parsedStatement

* bugfix - do not collect duplicates

* implement toString() for
* ValidationError
* ValidationException

* add simple caching

* + validateOptionalFromItem(s)

* * implement GroupByValidator

* implement merge-validator

* renaming ItemListValidator -> ItemsListValidator

* + validateOptionalItemsList
+ implement ReplaceValidator
+ use validateOptionalColumns, validateOptionalExpression where possible

* * remove validateOptionalColumns -> switch to
validateOptionalExpressions
* move validateOptionalOrderByElements to AbstractValidator
* add validateOptional in AbstractValidator
* add validateOptionalList in AbstractValidator

* + SetStatementValidator

* + ValuesStatementValidator

* + UseStatementValidator

* * implement UpdateValidator

* * implement ShowStatementValidator/ShowColumnsStatementValidator

* * implement UpdateValidator

* * add Feature.jdbcParameter, Feature.jdbcNamedParameter, to all
featuresets
* + Version.getFeaturesClone
* add javadoc to Version-enum-constructors

* + validateOptionalFeature

* * implement DeleteValidator

* ...

* fix typo

* small optimization

* * move method getFeaturesClone to FeatureSet
* implement join - validation
* add copy(), add(Collection), remove(*) methods to FeaturesAllowed

* * add join - features to sqlserver, h2

* implementations

* bugfix - merging the errors

* copyright

* #1022

* add more fine granular control for setOperations

* fix nullpointerexception

* add more fine granular control for comments

* add Features supported

* * add javadoc
* add features to *Version-files

* extract methods isNotEmpty

* check for isNotEmpty

* * add features to *Version-files

* always parse net.sf.jsqlparser.statement.Statements and validate the
list of included net.sf.jsqlparser.statement.Statement's

* add known mariadb features

* new names-set for FeaturesAllowed

* new names-set for FeaturesAllowed

* new names-set for FeaturesAllowed

* add ature.withItem, Feature.withItemRecursive to H2

* Feature.setOperation, Feature.setOperationUnion,
Feature.setOperationIntersect, Feature.setOperationExcept,

                    for MariaDb

* add features to SQLServer

* Merge branch 'master.orig' into github.validate

* @OverRide() -> @OverRide

* fix typing error "joinStaight" > joinStraight

* rename Feature "insertValues" -> "values" and use "insertValues" for
INSERT INTO ... VALUES

* add javadoc

* add Feature.selectGroupByGroupingSets to PostgresqlVersion

* implement basic OracleVersion

* add Feature.mySql* - also supported by mariadb

* add some more finegraned control over "drop" Feature.
* drop,
* dropTable,
* dropIndex,
* dropView,
* dropSchema,
* dropSequence,
* dropIfExists,

* complete FeaturesAllowed groups INSERT/UPDATE/DELETE/MERGE/DML

* add link to documentation

* fix - duplicate use of feature "function" - the use of functions in
statements and "createFunction" as a ddl statement

* TODO this feature seams very close to a jsqlparser-user usecase

* * implement MySqlVersion
* replace feature Feature.dropIfExists by features dropTableIfExists,
dropIndexIfExists, dropViewIfExists, dropSchemaIfExists,
dropSequenceIfExists
* add methods FeatureSet.getNotContained FeatureSet.retainAll

* remove HSQLDBVersion - do not support this variant

* remove HSQLDBVersion - do not support this variant

* add unit-test

* + add unittests for
* UpdateValidator
* DeleteValidator
add stubs for all other Validator-classes

+ ModifyableFeatureSet

* add some utility-methods in ValidationTestAsserts

* complete unit-tests for InsertValidator

* remote Feature.insertReturningExpressionList for Oracle -
returning_clause requires INTO clause (only PL/SQL)

* add some more select validation tests

* add DropValidatorTests

* add DropValidatorTests

* add CreateTableValidatorTests

* add CreateTableValidatorTests

* add ExpressionValidatorTests

* add OrderByValidatorTest

* use isNotEmpty

* implement GroupByValidatorTest

* implement CreateSequenceValidatorTest

* remove @ignore - test is ok

* implement CreateIndexValidatorTest

* implement CreateViewValidatorTest

* enable validation of Feature.commentOnView (#1024 is merged already)

* change format of #toString() for better readability

* * implement MergeValidatorTest
* implement ReplaceValidatorTest
* implement StatementValidatorTest

* rename
* ValidationUtil -> Validation
* ValidatorUtil -> ValidationUtil
add testcases for ValidationUtil

* add DatabaseMetaDataValidationTest

* checkstyle fix

* add copyright statement

* add unit-tests for show tables, show column, show statements

* * add ExecuteValidatorTest
* as there is a difference between execute <procedure> and execute
[immediate] <dynamic sql> with USING expr, ... remove support for
execute on MYSQL, MARIADB, ORACLE

* * add ExecuteValidatorTest for CALL fnName (mysql, mariadb, postgres)

* add upsertvalidatortest

* add GrantValidatorTest

* add AlterSequenceValidatorTest

* add AlterSequenceValidatorTest

* add AlterViewValidatorTest

* add AlterValidatorTest

* replace != null by isNotEmpty on collections

* fix formatting

* add validate commit

* add validate block

* add DeclareStatementValidatorTest

* let NamesLookup implement UnaryOperator<String>

* let NamesLookup implement UnaryOperator<String>

* add javadoc

* add more DatabaseMetaDataValidationTest's

* extract JdbcDatabaseMetaDataCapability.splitAndValidateMinMax

* add pivot/unpivot/pivotxml validation testcases

* add testcase for Feature.tableFunction

* add test for lateral joins and subjoins

* add testValidationRowMovementOption

* add values validator test

* move tests to LimitValidatorTest

* move tests to UseStatementValidatorTest

* add tests for SET - statements

* fix checkstyle error

* new serialVersionUID

* add validation for NamedObject not existing

* need table/view reference to validate column names

* fix typo

* fix errormessage (Arrays.toString(types))

* add trigger, alias
return null, instead of throwing exception, if not found

* extract NamesLookup to own file (jdk-bug enum inner classes)

* fix name-check AlterOperation.ALTER

* fix error message

* remove methods not needed (they only delegate to ValidationContext)

* add tests - validate metadata

* fix compile error

* fix columnExists check - depending on the statement the prefix is an
alias, a table/view or it has no prefix (need to lookup within all
related tables/views)

* fix javadoc warnings
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants