Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#2017 (ok 2.9) Exporting specific rows from query with join

Latest_Git_snapshot
fixed
1
2013-06-11
2006-02-14
Ryan Schmidt
No

A coworker of mine discovered a problem in PMA 2.6.0-pl3
and I confirmed that it still exists in yesterday's PMA
2.9.0-dev CVS snapshot. The problem arises when trying to
export specific rows of a query which was produced using a
join or other "WHERE" conditions. As a simple test case,
run the attached setup1.sql which creates tables foo and
bar. Then select some data from foo, joining in bar:

SELECT foo.*
FROM foo, bar
WHERE foo.id=bar.foo_id
ORDER BY bar.id;

In the result set, click the checkboxes for the first two
rows ("three" and "four") and click the Export button. PMA
generates this SQL for the export:

SELECT foo. *
FROM foo, bar
WHERE (
`id` =3
AND CONVERT( `name`
USING utf8 ) = 'three'
)
OR (
`id` =4
AND CONVERT( `name`
USING utf8 ) = 'four'
)
ORDER BY bar.id;

Click the Refresh link beneath the SQL to execute it
normally and we see a problem:

#1052 - Column 'id' in where clause is ambiguous

--> Although PMA is including both table names, it fails
to use these table names before the field names. <--

Suppose we change the tables then so that the columns are
not ambiguous. (This is the situation my coworker reported
to me.) Load setup2.sql to set up the table structure a
different way. Now try selecting some data with a join
again:

SELECT foo.*
FROM foo, bar
WHERE foo.foo_id=bar.fk_foo_id
ORDER BY bar.bar_id;

Again click the first two checkboxes followed by Export.
In the generated SQL we see:

SELECT foo. *
FROM foo, bar
WHERE (
`foo_id` =3
AND CONVERT( `name`
USING utf8 ) = 'three'
)
OR (
`foo_id` =4
AND CONVERT( `name`
USING utf8 ) = 'four'
)
ORDER BY bar.bar_id;

--> After clicking Refresh, we now see the second problem:
the result set shows the rows with foo_ids 3 and 4
repeated four times. The problem is that the original
query's "WHERE" conditions are being thrown away! <--

If it is difficult for PMA to rewrite the SQL statement
correctly, then at least it should prevent me from
attempting the export. It is not user-friendly that
clicking on a PMA interface element should produce either
a MySQL error or a completely unexpected result set.

This is with:
- phpMyAdmin-20060213-053501 CVS snapshot
- PHP 5.1.2
- MySQL 5.0.18
- Lighttpd 1.4.10
- Safari 2.0.2
- Mac OS X 10.4.4 PPC

Discussion

  • Ryan Schmidt
    Ryan Schmidt
    2006-02-14

    Setup script #1

     
    Attachments
  • Ryan Schmidt
    Ryan Schmidt
    2006-02-14

    Setup script #2

     
    Attachments
  • Logged In: YES
    user_id=326580

    doing this crashes my Windows MySQL 5.0.18 Server ... :-(

    SELECT FOUND_ROWS( ) AS count ;
    #2013 - Lost connection to MySQL server during query

     
    • assigned_to: nobody --> cybot_tm
    • status: open --> open-accepted
     
  • Logged In: YES
    user_id=326580

    ok ... updated my 'PHP/MySQL connector' ;-)

    missing table names is not the only problems, also only the
    UVA-condition is applied to every operation but the original
    WHERE condition is omitted, what leads to
    unexpected/unwanted result-sets

    and possible we should not allow edit and delete on rows
    from joined queries, whether the displayed columns are from
    one table or not ...

     
  • Logged In: YES
    user_id=326580

    should be fixed in CVS

     
    • priority: 5 --> 1
    • summary: Exporting specific rows from query with join --> (ok 2.9) Exporting specific rows from query with join
     
  • Marc Delisle
    Marc Delisle
    2006-09-20

    • status: open-accepted --> closed-accepted
     
  • Michal Čihař
    Michal Čihař
    2013-06-11

    • Status: closed-accepted --> fixed