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
      There are no Sub-Tasks for this issue.

        Activity

        Transition Time In Source Status Execution Times Last Executer Last Execution Date
        Open Open Patch Available Patch Available
        52d 15h 43m 1 Cheolsoo Park 17/Apr/12 07:15
        Patch Available Patch Available Resolved Resolved
        10d 10h 21m 1 Cheolsoo Park 27/Apr/12 17:37
        Abhijeet Gaikwad made changes -
        Fix Version/s 1.4.2 [ 12320141 ]
        Cheolsoo Park made changes -
        Status Patch Available [ 10002 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        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
        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
        Cheolsoo Park made changes -
        Attachment SQOOP-445.patch [ 12524819 ]
        Cheolsoo Park made changes -
        Attachment SQOOP-445.patch [ 12524818 ]
        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
        Cheolsoo Park made changes -
        Status Open [ 1 ] Patch Available [ 10002 ]
        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
        Cheolsoo Park made changes -
        Assignee Cheolsoo Park [ cheolsoo ]
        Cheolsoo Park made changes -
        Field Original Value New Value
        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:

        {code}
        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)
        {code} ]
        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)
        Davide Grohmann created issue -

          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