Derby
  1. Derby
  2. DERBY-4586

Add compatibility tests to explore behavior differences between Derby and other DBMS implementations

    Details

    • Urgency:
      Normal

      Description

      It would be interesting to understand Derby's compatibility with other major DBMS implementations, such as: Oracle, Sybase, SQL Server, DB2, MySQL, etc.

      I believe this would be an excellent project for a motivated Google Summer of Code intern.

      The goal of the project would be to locate various existing compatibility test suites and bring them into the Derby test environment, as well as to augment those test suites with additional tests developed during the project.

        Activity

        Hide
        Bryan Pendleton added a comment -

        Assigned the issue to myself to reflect my willingness to be a mentor to a GSoC intern on this project.

        Show
        Bryan Pendleton added a comment - Assigned the issue to myself to reflect my willingness to be a mentor to a GSoC intern on this project.
        Hide
        Bryan Pendleton added a comment -

        [ From a derby-dev mail thread ... ]

        But I think we are specifically interested in the idea of compatibility
        tests which we could use to demonstrate compatibility with Oracle, SQL Server,
        MySQL, Postgres, etc.

        Are there existing compatibility test suites out there? Can you research this?

        What would it take to run those tests against a variety of implementations,
        including both Derby and others?

        How could we automate that process so that we could add it to our test
        suites and keep it up to date over time?

        We have some existing information about Derby compatibility in our wiki:

        What would we need to do in order to be able to write a similar page which
        discusses Derby compatibility with, e.g., MySQL and/or Postgres? That is,
        what tests do we have to run to gather this information?

        One place to start would be to see if either MySQL or Postgres have existing
        SQL-compliance or JDBC-compliance suites that we could run against Derby to
        compare the results.

        Show
        Bryan Pendleton added a comment - [ From a derby-dev mail thread ... ] But I think we are specifically interested in the idea of compatibility tests which we could use to demonstrate compatibility with Oracle, SQL Server, MySQL, Postgres, etc. Are there existing compatibility test suites out there? Can you research this? What would it take to run those tests against a variety of implementations, including both Derby and others? How could we automate that process so that we could add it to our test suites and keep it up to date over time? We have some existing information about Derby compatibility in our wiki: http://wiki.apache.org/db-derby/SQLvsDerbyFeatures http://wiki.apache.org/db-derby/JDBCSupport http://wiki.apache.org/db-derby/PlatformTestsDerby What would we need to do in order to be able to write a similar page which discusses Derby compatibility with, e.g., MySQL and/or Postgres? That is, what tests do we have to run to gather this information? One place to start would be to see if either MySQL or Postgres have existing SQL-compliance or JDBC-compliance suites that we could run against Derby to compare the results.
        Hide
        Isuru Anuranga Wijesinghe added a comment -

        Hi Bryan

        I'm a student with a great will to participate GSOC 2011. Your idea seems to be very interesting. I recently did a research project on "improving derby query optimization algorithms". There I got knowledge of comparing Derby with other RDBMS. This is a link I found http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems
        with huge collection of comparison between existing RDBMS.

        I have gone through links you provided. And did a small background research on H2 database engine (http://h2database.com/html/features.html#compatibility) to understand what exactly mean by compatibility. I found that, database engines behaves bit different from each other, SQL syntax support also can be different. But to some extent they can emulate the behavior of specific databases. One possible example for compatibility would be - import SQL Server database dump file to derby. To consolidate my idea I would like to have your comment on, to which extent I’m clear about project goal.

        Project goal - Bring existing suits for compatibility testing in to derby testing environment and test Derby compatibility with well-known database engines and then extending for further compatibility testing define by us.

        Show
        Isuru Anuranga Wijesinghe added a comment - Hi Bryan I'm a student with a great will to participate GSOC 2011. Your idea seems to be very interesting. I recently did a research project on "improving derby query optimization algorithms". There I got knowledge of comparing Derby with other RDBMS. This is a link I found http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems with huge collection of comparison between existing RDBMS. I have gone through links you provided. And did a small background research on H2 database engine ( http://h2database.com/html/features.html#compatibility ) to understand what exactly mean by compatibility. I found that, database engines behaves bit different from each other, SQL syntax support also can be different. But to some extent they can emulate the behavior of specific databases. One possible example for compatibility would be - import SQL Server database dump file to derby. To consolidate my idea I would like to have your comment on, to which extent I’m clear about project goal. Project goal - Bring existing suits for compatibility testing in to derby testing environment and test Derby compatibility with well-known database engines and then extending for further compatibility testing define by us.
        Hide
        Bryan Pendleton added a comment -

        I think that SQL Syntax compatibility and JDBC API compatibility are the two most important issues.

        The compatibility pages that you linked to are very interesting; these are definitely the sort
        of issues that we can work on.

        I'm particularly interested in finding test suites for other open source databases, which contain
        test cases which can be run against Derby, to determine whether Derby passes those tests or not.

        If Derby does NOT pass a certain test, we need to determine whether it is:

        a) a non-standard test which uses non-standard SQL or JDBC features, in which
        case the other database has chosen to support this non-standard behavior but
        Derby may not wish to. We would then add documentation of this sort of
        behavior difference to the Derby documentation (e.g., in the Derby wiki).

        b) OR, it is a standard test using standard SQL and JDBC features, but it reveals
        a bug in Derby as Derby does not implement that feature properly. In this case,
        we would log a new Jira issue against Derby, add that test case to the Derby test
        suite, and see if we could fix the bug in Derby.

        That is the goal of this project.

        Show
        Bryan Pendleton added a comment - I think that SQL Syntax compatibility and JDBC API compatibility are the two most important issues. The compatibility pages that you linked to are very interesting; these are definitely the sort of issues that we can work on. I'm particularly interested in finding test suites for other open source databases, which contain test cases which can be run against Derby, to determine whether Derby passes those tests or not. If Derby does NOT pass a certain test, we need to determine whether it is: a) a non-standard test which uses non-standard SQL or JDBC features, in which case the other database has chosen to support this non-standard behavior but Derby may not wish to. We would then add documentation of this sort of behavior difference to the Derby documentation (e.g., in the Derby wiki). b) OR, it is a standard test using standard SQL and JDBC features, but it reveals a bug in Derby as Derby does not implement that feature properly. In this case, we would log a new Jira issue against Derby, add that test case to the Derby test suite, and see if we could fix the bug in Derby. That is the goal of this project.
        Hide
        Isuru Anuranga Wijesinghe added a comment -

        Oh sorry I didn't notice that Eranda have shown his interest to this project before I did. Its ok . Anyway its a very interesting idea and I would love to see how it propagate and its outcome.

        Show
        Isuru Anuranga Wijesinghe added a comment - Oh sorry I didn't notice that Eranda have shown his interest to this project before I did. Its ok . Anyway its a very interesting idea and I would love to see how it propagate and its outcome.
        Hide
        Eranda Sooriyabandara added a comment -

        In this issue when we create a compatibility test,
        we cannot have a one global test for every DBMS for test compatibility of all the DBMS since their SQL slightly differ from each.
        So whenever a DBMS add a functionality we have to put it as compatible by manual. How can we address that issue?

        Show
        Eranda Sooriyabandara added a comment - In this issue when we create a compatibility test, we cannot have a one global test for every DBMS for test compatibility of all the DBMS since their SQL slightly differ from each. So whenever a DBMS add a functionality we have to put it as compatible by manual. How can we address that issue?
        Hide
        Bryan Pendleton added a comment -

        I don't think we have the resources to build and maintain automated tests for other DBMS implementations;
        it's hard enough to maintain sophisticated SQL tests for Derby.

        Mostly, my idea is that the test suites that exist for other databases (H2, MySQL, Postgres, etc.) may be a rich source of ideas for how to extend and improve the Derby test suites, particularly in the area of finding unusual SQL that different DBMS implementations handle differently (SQL is a very rich language with many variations on statements).

        So the goal is to improve the Derby test suites, not to build cross-database test suites.

        Note that there are other projects (e.g., Apache OpenJPA) which do in fact build sophisticated cross-database test suites, so such things do exist. They just aren't part of the Derby community's test suites.

        If we add a test to the Derby suite, there are two situations:

        1) The test uses non-standard SQL. In that case, the test should simply verify that Derby gives a reasonable error message. It would be useful and helpful if the test included comments describing how the non-standard SQL behaves on some other DBMS, but that's not required. It would also be useful and helpful to update the Derby wiki in such a case to describe the implementation differences.

        2) The test uses standard SQL; we found the test in another DBMS's open test suite, determined that Derby doesn't support that SQL properly, opened a bug against Derby to describe the problem we found, and added a test
        case to the Derby test suite. Here's an interesting example of such a case: https://issues.apache.org/jira/browse/DERBY-4558 The goal is, can we find more?

        This is all somewhat hypothetical, as first we need to find some test suites for other database systems that are worth running against Derby

        Show
        Bryan Pendleton added a comment - I don't think we have the resources to build and maintain automated tests for other DBMS implementations; it's hard enough to maintain sophisticated SQL tests for Derby. Mostly, my idea is that the test suites that exist for other databases (H2, MySQL, Postgres, etc.) may be a rich source of ideas for how to extend and improve the Derby test suites, particularly in the area of finding unusual SQL that different DBMS implementations handle differently (SQL is a very rich language with many variations on statements). So the goal is to improve the Derby test suites, not to build cross-database test suites. Note that there are other projects (e.g., Apache OpenJPA) which do in fact build sophisticated cross-database test suites, so such things do exist. They just aren't part of the Derby community's test suites. If we add a test to the Derby suite, there are two situations: 1) The test uses non-standard SQL. In that case, the test should simply verify that Derby gives a reasonable error message. It would be useful and helpful if the test included comments describing how the non-standard SQL behaves on some other DBMS, but that's not required. It would also be useful and helpful to update the Derby wiki in such a case to describe the implementation differences. 2) The test uses standard SQL; we found the test in another DBMS's open test suite, determined that Derby doesn't support that SQL properly, opened a bug against Derby to describe the problem we found, and added a test case to the Derby test suite. Here's an interesting example of such a case: https://issues.apache.org/jira/browse/DERBY-4558 The goal is, can we find more? This is all somewhat hypothetical, as first we need to find some test suites for other database systems that are worth running against Derby
        Hide
        Kathey Marsden added a comment -

        Bryan said:
        >This is all somewhat hypothetical, as first we need to find some test suites for other database systems that are worth running against Derby

        And I would like to add have appropriate licensing for import into Derby if we want to bring any of those test cases in.

        Show
        Kathey Marsden added a comment - Bryan said: >This is all somewhat hypothetical, as first we need to find some test suites for other database systems that are worth running against Derby And I would like to add have appropriate licensing for import into Derby if we want to bring any of those test cases in.

          People

          • Assignee:
            Unassigned
            Reporter:
            Bryan Pendleton
          • Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:

              Development