Uploaded image for project: 'Sqoop (Retired)'
  1. Sqoop (Retired)
  2. SQOOP-445

Wrong query for getListColumnsQuery in PostgresqlManager

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • 1.4.0-incubating, 1.4.1-incubating
    • 1.4.2
    • connectors/postgresql
    • None
    • 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.

      Attachments

        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

          cheolsoo 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)
          
          cheolsoo 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)

          -----------------------------------------------------------
          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

          jiraposter@reviews.apache.org 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

          -----------------------------------------------------------
          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

          jiraposter@reviews.apache.org 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

          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

          jiraposter@reviews.apache.org 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
          hudson 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
          hudson 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

            cheolsoo Cheolsoo Park
            menene 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