Sqoop
  1. Sqoop
  2. SQOOP-445

Wrong query for getListColumnsQuery in PostgresqlManager

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.4.0-incubating, 1.4.1-incubating
    • Fix Version/s: 1.4.2
    • Component/s: connectors/postgresql
    • Labels:
      None
    • Environment:

      PostgreSQL 9.1

      Description

      When querying for column available in a postgres table from postgres catalog, it returns also the dropped columns from the table in object. It can be easily fixed adding the extra constraint "AND col.ATTISDROPPED = 'f'" at the end of the query which sort out the dropped columns.

      1. SQOOP-445.patch
        0.6 kB
        Cheolsoo Park
      2. SQOOP-445.patch
        0.6 kB
        Cheolsoo Park

        Activity

        Hide
        Cheolsoo Park added a comment -

        I manually tested the fix that Davide is proposing, and it seems to work perfectly. I don't think that there are any backward compatibility issues that we have to worry about since nobody shouldn't have relied on this behavior...? If anyone disagrees with me, please let me know.

        Here are commands that I ran to verify the proposed fix:

        sqooptest=# create table foo (i integer);
        CREATE TABLE
        
        sqooptest=# alter table foo drop column i;
        ALTER TABLE
        
        // without col.ATTISDROPPED = 'f'
        
        sqooptest=# SELECT col.ATTNAME FROM PG_CATALOG.PG_NAMESPACE sch,  PG_CATALOG.PG_CLASS tab, PG_CATALOG.PG_ATTRIBUTE col WHERE sch.OID = tab.RELNAMESPACE   AND tab.OID = col.ATTRELID   AND sch.NSPNAME = (SELECT CURRENT_SCHEMA())   AND tab.RELNAME = 'foo' AND col.ATTNUM >= 1;
                   attname            
        ------------------------------
         ........pg.dropped.1........
        (1 row)
        
        // with col.ATTISDROPPED = 'f'
        
        sqooptest=# SELECT col.ATTNAME FROM PG_CATALOG.PG_NAMESPACE sch,  PG_CATALOG.PG_CLASS tab, PG_CATALOG.PG_ATTRIBUTE col WHERE sch.OID = tab.RELNAMESPACE   AND tab.OID = col.ATTRELID   AND sch.NSPNAME = (SELECT CURRENT_SCHEMA())   AND tab.RELNAME = 'foo' AND col.ATTNUM >= 1  AND col.ATTISDROPPED = 'f';
         attname 
        ---------
        (0 rows)
        
        Show
        Cheolsoo Park added a comment - I manually tested the fix that Davide is proposing, and it seems to work perfectly. I don't think that there are any backward compatibility issues that we have to worry about since nobody shouldn't have relied on this behavior...? If anyone disagrees with me, please let me know. Here are commands that I ran to verify the proposed fix: sqooptest=# create table foo (i integer); CREATE TABLE sqooptest=# alter table foo drop column i; ALTER TABLE // without col.ATTISDROPPED = 'f' sqooptest=# SELECT col.ATTNAME FROM PG_CATALOG.PG_NAMESPACE sch, PG_CATALOG.PG_CLASS tab, PG_CATALOG.PG_ATTRIBUTE col WHERE sch.OID = tab.RELNAMESPACE AND tab.OID = col.ATTRELID AND sch.NSPNAME = (SELECT CURRENT_SCHEMA()) AND tab.RELNAME = 'foo' AND col.ATTNUM >= 1; attname ------------------------------ ........pg.dropped.1........ (1 row) // with col.ATTISDROPPED = 'f' sqooptest=# SELECT col.ATTNAME FROM PG_CATALOG.PG_NAMESPACE sch, PG_CATALOG.PG_CLASS tab, PG_CATALOG.PG_ATTRIBUTE col WHERE sch.OID = tab.RELNAMESPACE AND tab.OID = col.ATTRELID AND sch.NSPNAME = (SELECT CURRENT_SCHEMA()) AND tab.RELNAME = 'foo' AND col.ATTNUM >= 1 AND col.ATTISDROPPED = 'f'; attname --------- (0 rows)
        Hide
        jiraposter@reviews.apache.org added a comment -

        -----------------------------------------------------------
        This is an automatically generated e-mail. To reply, visit:
        https://reviews.apache.org/r/4752/
        -----------------------------------------------------------

        Review request for Sqoop.

        Summary
        -------

        When querying for column available in a postgres table from postgres catalog, it returns also the dropped columns from the table in object. It can be easily fixed adding the extra constraint "AND col.ATTISDROPPED = 'f'" at the end of the query which sort out the dropped columns.

        This addresses bug SQOOP-445.
        https://issues.apache.org/jira/browse/SQOOP-445

        Diffs


        /src/java/org/apache/sqoop/manager/PostgresqlManager.java 1326931

        Diff: https://reviews.apache.org/r/4752/diff

        Testing
        -------

        ant test, ant test -Dthirdparty=true, ant checkstyle.

        In addition, I manually tested the constraint "col.ATTISDROPPED = 'f'" as follows:

        sqooptest=# create table foo (i integer);
        CREATE TABLE

        sqooptest=# alter table foo drop column i;
        ALTER TABLE

        <without col.ATTISDROPPED = 'f'>

        sqooptest=# SELECT col.ATTNAME FROM PG_CATALOG.PG_NAMESPACE sch, PG_CATALOG.PG_CLASS tab, PG_CATALOG.PG_ATTRIBUTE col WHERE sch.OID = tab.RELNAMESPACE AND tab.OID = col.ATTRELID AND sch.NSPNAME = (SELECT CURRENT_SCHEMA()) AND tab.RELNAME = 'foo' AND col.ATTNUM >= 1;
        attname
        ------------------------------
        ........pg.dropped.1........
        (1 row)

        <with col.ATTISDROPPED = 'f'>

        sqooptest=# SELECT col.ATTNAME FROM PG_CATALOG.PG_NAMESPACE sch, PG_CATALOG.PG_CLASS tab, PG_CATALOG.PG_ATTRIBUTE col WHERE sch.OID = tab.RELNAMESPACE AND tab.OID = col.ATTRELID AND sch.NSPNAME = (SELECT CURRENT_SCHEMA()) AND tab.RELNAME = 'foo' AND col.ATTNUM >= 1 AND col.ATTISDROPPED = 'f';
        attname
        ---------
        (0 rows)

        Thanks,

        Cheolsoo

        Show
        jiraposter@reviews.apache.org added a comment - ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/4752/ ----------------------------------------------------------- Review request for Sqoop. Summary ------- When querying for column available in a postgres table from postgres catalog, it returns also the dropped columns from the table in object. It can be easily fixed adding the extra constraint "AND col.ATTISDROPPED = 'f'" at the end of the query which sort out the dropped columns. This addresses bug SQOOP-445 . https://issues.apache.org/jira/browse/SQOOP-445 Diffs /src/java/org/apache/sqoop/manager/PostgresqlManager.java 1326931 Diff: https://reviews.apache.org/r/4752/diff Testing ------- ant test, ant test -Dthirdparty=true, ant checkstyle. In addition, I manually tested the constraint "col.ATTISDROPPED = 'f'" as follows: sqooptest=# create table foo (i integer); CREATE TABLE sqooptest=# alter table foo drop column i; ALTER TABLE <without col.ATTISDROPPED = 'f'> sqooptest=# SELECT col.ATTNAME FROM PG_CATALOG.PG_NAMESPACE sch, PG_CATALOG.PG_CLASS tab, PG_CATALOG.PG_ATTRIBUTE col WHERE sch.OID = tab.RELNAMESPACE AND tab.OID = col.ATTRELID AND sch.NSPNAME = (SELECT CURRENT_SCHEMA()) AND tab.RELNAME = 'foo' AND col.ATTNUM >= 1; attname ------------------------------ ........pg.dropped.1........ (1 row) <with col.ATTISDROPPED = 'f'> sqooptest=# SELECT col.ATTNAME FROM PG_CATALOG.PG_NAMESPACE sch, PG_CATALOG.PG_CLASS tab, PG_CATALOG.PG_ATTRIBUTE col WHERE sch.OID = tab.RELNAMESPACE AND tab.OID = col.ATTRELID AND sch.NSPNAME = (SELECT CURRENT_SCHEMA()) AND tab.RELNAME = 'foo' AND col.ATTNUM >= 1 AND col.ATTISDROPPED = 'f'; attname --------- (0 rows) Thanks, Cheolsoo
        Hide
        jiraposter@reviews.apache.org added a comment -

        -----------------------------------------------------------
        This is an automatically generated e-mail. To reply, visit:
        https://reviews.apache.org/r/4752/#review7294
        -----------------------------------------------------------

        Ship it!

        Look good to me.

        It would be even better if you could have a test case to
        1. create a table
        2. drop a column
        3. perform sqoop import
        4. would succeed with the fix and fail without

        • Bilung

        On 2012-04-17 06:12:11, Cheolsoo Park wrote:

        -----------------------------------------------------------

        This is an automatically generated e-mail. To reply, visit:

        https://reviews.apache.org/r/4752/

        -----------------------------------------------------------

        (Updated 2012-04-17 06:12:11)

        Review request for Sqoop.

        Summary

        -------

        When querying for column available in a postgres table from postgres catalog, it returns also the dropped columns from the table in object. It can be easily fixed adding the extra constraint "AND col.ATTISDROPPED = 'f'" at the end of the query which sort out the dropped columns.

        This addresses bug SQOOP-445.

        https://issues.apache.org/jira/browse/SQOOP-445

        Diffs

        -----

        /src/java/org/apache/sqoop/manager/PostgresqlManager.java 1326931

        Diff: https://reviews.apache.org/r/4752/diff

        Testing

        -------

        ant test, ant test -Dthirdparty=true, ant checkstyle.

        In addition, I manually tested the constraint "col.ATTISDROPPED = 'f'" as follows:

        sqooptest=# create table foo (i integer);

        CREATE TABLE

        sqooptest=# alter table foo drop column i;

        ALTER TABLE

        <without col.ATTISDROPPED = 'f'>

        sqooptest=# SELECT col.ATTNAME FROM PG_CATALOG.PG_NAMESPACE sch, PG_CATALOG.PG_CLASS tab, PG_CATALOG.PG_ATTRIBUTE col WHERE sch.OID = tab.RELNAMESPACE AND tab.OID = col.ATTRELID AND sch.NSPNAME = (SELECT CURRENT_SCHEMA()) AND tab.RELNAME = 'foo' AND col.ATTNUM >= 1;

        attname

        ------------------------------

        ........pg.dropped.1........

        (1 row)

        <with col.ATTISDROPPED = 'f'>

        sqooptest=# SELECT col.ATTNAME FROM PG_CATALOG.PG_NAMESPACE sch, PG_CATALOG.PG_CLASS tab, PG_CATALOG.PG_ATTRIBUTE col WHERE sch.OID = tab.RELNAMESPACE AND tab.OID = col.ATTRELID AND sch.NSPNAME = (SELECT CURRENT_SCHEMA()) AND tab.RELNAME = 'foo' AND col.ATTNUM >= 1 AND col.ATTISDROPPED = 'f';

        attname

        ---------

        (0 rows)

        Thanks,

        Cheolsoo

        Show
        jiraposter@reviews.apache.org added a comment - ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/4752/#review7294 ----------------------------------------------------------- Ship it! Look good to me. It would be even better if you could have a test case to 1. create a table 2. drop a column 3. perform sqoop import 4. would succeed with the fix and fail without Bilung On 2012-04-17 06:12:11, Cheolsoo Park wrote: ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/4752/ ----------------------------------------------------------- (Updated 2012-04-17 06:12:11) Review request for Sqoop. Summary ------- When querying for column available in a postgres table from postgres catalog, it returns also the dropped columns from the table in object. It can be easily fixed adding the extra constraint "AND col.ATTISDROPPED = 'f'" at the end of the query which sort out the dropped columns. This addresses bug SQOOP-445 . https://issues.apache.org/jira/browse/SQOOP-445 Diffs ----- /src/java/org/apache/sqoop/manager/PostgresqlManager.java 1326931 Diff: https://reviews.apache.org/r/4752/diff Testing ------- ant test, ant test -Dthirdparty=true, ant checkstyle. In addition, I manually tested the constraint "col.ATTISDROPPED = 'f'" as follows: sqooptest=# create table foo (i integer); CREATE TABLE sqooptest=# alter table foo drop column i; ALTER TABLE <without col.ATTISDROPPED = 'f'> sqooptest=# SELECT col.ATTNAME FROM PG_CATALOG.PG_NAMESPACE sch, PG_CATALOG.PG_CLASS tab, PG_CATALOG.PG_ATTRIBUTE col WHERE sch.OID = tab.RELNAMESPACE AND tab.OID = col.ATTRELID AND sch.NSPNAME = (SELECT CURRENT_SCHEMA()) AND tab.RELNAME = 'foo' AND col.ATTNUM >= 1; attname ------------------------------ ........pg.dropped.1........ (1 row) <with col.ATTISDROPPED = 'f'> sqooptest=# SELECT col.ATTNAME FROM PG_CATALOG.PG_NAMESPACE sch, PG_CATALOG.PG_CLASS tab, PG_CATALOG.PG_ATTRIBUTE col WHERE sch.OID = tab.RELNAMESPACE AND tab.OID = col.ATTRELID AND sch.NSPNAME = (SELECT CURRENT_SCHEMA()) AND tab.RELNAME = 'foo' AND col.ATTNUM >= 1 AND col.ATTISDROPPED = 'f'; attname --------- (0 rows) Thanks, Cheolsoo
        Hide
        jiraposter@reviews.apache.org added a comment -

        On 2012-04-27 06:07:54, Bilung Lee wrote:

        > Look good to me.

        >

        > It would be even better if you could have a test case to

        > 1. create a table

        > 2. drop a column

        > 3. perform sqoop import

        > 4. would succeed with the fix and fail without

        >

        Hi Bilung,

        Thanks for reviewing the patch. Yes, I can add a test for this too. I will open a subtask to add a test.

        • Cheolsoo

        -----------------------------------------------------------
        This is an automatically generated e-mail. To reply, visit:
        https://reviews.apache.org/r/4752/#review7294
        -----------------------------------------------------------

        On 2012-04-17 06:12:11, Cheolsoo Park wrote:

        -----------------------------------------------------------

        This is an automatically generated e-mail. To reply, visit:

        https://reviews.apache.org/r/4752/

        -----------------------------------------------------------

        (Updated 2012-04-17 06:12:11)

        Review request for Sqoop.

        Summary

        -------

        When querying for column available in a postgres table from postgres catalog, it returns also the dropped columns from the table in object. It can be easily fixed adding the extra constraint "AND col.ATTISDROPPED = 'f'" at the end of the query which sort out the dropped columns.

        This addresses bug SQOOP-445.

        https://issues.apache.org/jira/browse/SQOOP-445

        Diffs

        -----

        /src/java/org/apache/sqoop/manager/PostgresqlManager.java 1326931

        Diff: https://reviews.apache.org/r/4752/diff

        Testing

        -------

        ant test, ant test -Dthirdparty=true, ant checkstyle.

        In addition, I manually tested the constraint "col.ATTISDROPPED = 'f'" as follows:

        sqooptest=# create table foo (i integer);

        CREATE TABLE

        sqooptest=# alter table foo drop column i;

        ALTER TABLE

        <without col.ATTISDROPPED = 'f'>

        sqooptest=# SELECT col.ATTNAME FROM PG_CATALOG.PG_NAMESPACE sch, PG_CATALOG.PG_CLASS tab, PG_CATALOG.PG_ATTRIBUTE col WHERE sch.OID = tab.RELNAMESPACE AND tab.OID = col.ATTRELID AND sch.NSPNAME = (SELECT CURRENT_SCHEMA()) AND tab.RELNAME = 'foo' AND col.ATTNUM >= 1;

        attname

        ------------------------------

        ........pg.dropped.1........

        (1 row)

        <with col.ATTISDROPPED = 'f'>

        sqooptest=# SELECT col.ATTNAME FROM PG_CATALOG.PG_NAMESPACE sch, PG_CATALOG.PG_CLASS tab, PG_CATALOG.PG_ATTRIBUTE col WHERE sch.OID = tab.RELNAMESPACE AND tab.OID = col.ATTRELID AND sch.NSPNAME = (SELECT CURRENT_SCHEMA()) AND tab.RELNAME = 'foo' AND col.ATTNUM >= 1 AND col.ATTISDROPPED = 'f';

        attname

        ---------

        (0 rows)

        Thanks,

        Cheolsoo

        Show
        jiraposter@reviews.apache.org added a comment - On 2012-04-27 06:07:54, Bilung Lee wrote: > Look good to me. > > It would be even better if you could have a test case to > 1. create a table > 2. drop a column > 3. perform sqoop import > 4. would succeed with the fix and fail without > Hi Bilung, Thanks for reviewing the patch. Yes, I can add a test for this too. I will open a subtask to add a test. Cheolsoo ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/4752/#review7294 ----------------------------------------------------------- On 2012-04-17 06:12:11, Cheolsoo Park wrote: ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/4752/ ----------------------------------------------------------- (Updated 2012-04-17 06:12:11) Review request for Sqoop. Summary ------- When querying for column available in a postgres table from postgres catalog, it returns also the dropped columns from the table in object. It can be easily fixed adding the extra constraint "AND col.ATTISDROPPED = 'f'" at the end of the query which sort out the dropped columns. This addresses bug SQOOP-445 . https://issues.apache.org/jira/browse/SQOOP-445 Diffs ----- /src/java/org/apache/sqoop/manager/PostgresqlManager.java 1326931 Diff: https://reviews.apache.org/r/4752/diff Testing ------- ant test, ant test -Dthirdparty=true, ant checkstyle. In addition, I manually tested the constraint "col.ATTISDROPPED = 'f'" as follows: sqooptest=# create table foo (i integer); CREATE TABLE sqooptest=# alter table foo drop column i; ALTER TABLE <without col.ATTISDROPPED = 'f'> sqooptest=# SELECT col.ATTNAME FROM PG_CATALOG.PG_NAMESPACE sch, PG_CATALOG.PG_CLASS tab, PG_CATALOG.PG_ATTRIBUTE col WHERE sch.OID = tab.RELNAMESPACE AND tab.OID = col.ATTRELID AND sch.NSPNAME = (SELECT CURRENT_SCHEMA()) AND tab.RELNAME = 'foo' AND col.ATTNUM >= 1; attname ------------------------------ ........pg.dropped.1........ (1 row) <with col.ATTISDROPPED = 'f'> sqooptest=# SELECT col.ATTNAME FROM PG_CATALOG.PG_NAMESPACE sch, PG_CATALOG.PG_CLASS tab, PG_CATALOG.PG_ATTRIBUTE col WHERE sch.OID = tab.RELNAMESPACE AND tab.OID = col.ATTRELID AND sch.NSPNAME = (SELECT CURRENT_SCHEMA()) AND tab.RELNAME = 'foo' AND col.ATTNUM >= 1 AND col.ATTISDROPPED = 'f'; attname --------- (0 rows) Thanks, Cheolsoo
        Hide
        Hudson added a comment -

        Integrated in Sqoop-ant-jdk-1.6 #110 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6/110/)
        SQOOP-445 Wrong query for getListColumnsQuery in PostgresqlManager (Revision 1331245)

        Result = SUCCESS
        blee :
        Files :

        • /sqoop/trunk/src/java/org/apache/sqoop/manager/PostgresqlManager.java
        Show
        Hudson added a comment - Integrated in Sqoop-ant-jdk-1.6 #110 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6/110/ ) SQOOP-445 Wrong query for getListColumnsQuery in PostgresqlManager (Revision 1331245) Result = SUCCESS blee : Files : /sqoop/trunk/src/java/org/apache/sqoop/manager/PostgresqlManager.java

          People

          • Assignee:
            Cheolsoo Park
            Reporter:
            Davide Grohmann
          • Votes:
            1 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Time Tracking

              Estimated:
              Original Estimate - 1h
              1h
              Remaining:
              Remaining Estimate - 1h
              1h
              Logged:
              Time Spent - Not Specified
              Not Specified

                Development