#2612 (ok 2.11.5) Export of query results (LIMIT and CONCAT_WS())

2.11.3
fixed
1
2013-06-11
2007-12-31
oh4real
No

Howdy,

Summary: CSV & SQL exports of query results with CONCAT() and LIMIT set fail.

Running MySQL 5.0.22, Linux FC6, using FF2.0 as browser. Ran a rather helpful query:

SELECT CONCAT_WS('+','http://www.google.com/search?q=','foo','bar') FROM table_a

It worked perfectly when viewing results in phpmyadmin. When I added LIMIT 20 or LIMIT 0,20 for test/pre-viewing purposes for the bash scripts that are gonna use it and tried to export ('Query results operations->Export') as CSV or SQL as file or direct display, i got the error seen in attached 'sql-error'. The CSV error is the HTML portion of the sql error message.

The most salient error line I could make out was/is:

<code>
#1305 - FUNCTION contacts.CONCAT_WS does not exist
</code><br />

OR:

<code>
#1305 - FUNCTION contacts.CONCAT does not exist
</code><br />

Seems, phpMyAdmin is sticking when a LIMIT is included in query, because when I remove limits (and export query form shows Dump: # of all records in record count), then it works as expected.

Very, very reproducible. LIMIT error reproduces when URL is escaped or unescaped. LIMIT error reproduces with concat_ws() and concat(). I usually export queries without LIMITs so don't know if this phenomena is related to concat or not, althought above error messages are again somewhat indicative.

Regards,
oh4real

Discussion

  • oh4real

    oh4real - 2007-12-31

    SQL export

     
  • Marc Delisle

    Marc Delisle - 2008-01-01

    Logged In: YES
    user_id=210714
    Originator: NO

    Reproduced also in 2.11.3.

     
  • Marc Delisle

    Marc Delisle - 2008-01-01
    • milestone: 772648 --> 2.11.3
     
  • oh4real

    oh4real - 2008-01-11

    Logged In: YES
    user_id=1970802
    Originator: YES

    More Query Export problem. Here is the friendly query, which works just fine:

    SELECT SUBSTRING_INDEX( email, '@', -1 ) , count( * )
    FROM `targets_X`
    WHERE email NOT LIKE '%yahoo%'
    AND email NOT LIKE '%aol%'
    AND email NOT LIKE '%gmail%'
    AND email NOT LIKE '%hotmail%'
    AND email NOT LIKE '%msn%'
    AND email NOT LIKE '%att%'
    AND email NOT LIKE '%comcast%'
    AND email NOT LIKE '%earthlink%'
    AND email NOT LIKE '%mindspring%'
    AND email NOT LIKE '%mac.com%'
    AND email NOT LIKE '%att%'
    AND email NOT LIKE '%netcom%'
    AND email NOT LIKE '%rr.com%'
    AND (
    email LIKE '%.com%' || email LIKE '%.net%'
    )
    GROUP BY SUBSTRING_INDEX( email, '@', -1 )

    Here is the error in the iframe that holds the text export (same for export as file):

    <!-- PMA-SQL-ERROR -->
    <div class="error"><h1>Error</h1>
    <p><strong>SQL query:</strong>
    <a href="http://dev.mysql.com/doc/refman/5.0/en/select.html" target="mysql_doc"><img class="icon" src="./themes/original/img/b_help.png" width="11" height="11" alt="Documentation" title="Documentation" /></a><a href="tbl_sql.php?db=contacts&table=targets_X&token=62f4b1624df09cdcf1c9d9be6a555381&sql_query=SELECT+SUBSTRING_INDEX+%28+email+%2C+%27%40%27+%2C+-+1+%29+%2C+count+%28+%2A+%29+FROM+%60targets_X%60+WHERE+email+NOT+LIKE+%27%25yahoo%25%27+AND+email+NOT+LIKE+%27%25aol%25%27+AND+email+NOT+LIKE+%27%25gmail%25%27+AND+email+NOT+LIKE+%27%25hotmail%25%27+AND+email+NOT+LIKE+%27%25msn%25%27+AND+email+NOT+LIKE+%27%25att%25%27+AND+email+NOT+LIKE+%27%25comcast%25%27+AND+email+NOT+LIKE+%27%25earthlink%25%27+AND+email+NOT+LIKE+%27%25mindspring%25%27+AND+email+NOT+LIKE+%27%25mac.com%25%27+AND+email+NOT+LIKE+%27%25att%25%27+AND+email+NOT+LIKE+%27%25netcom%25%27+AND+email+NOT+LIKE+%27%25rr.com%25%27+AND+%28+email+LIKE+%27%25.com%25%27+%7C%7C+email+LIKE+%27%25.net%25%27+%29+GROUP+BY+SUBSTRING_INDEX+%28+email+%2C+%27%40%27+%2C+-+1+%29++LIMIT+929&show_query=1"><img class="icon" src=" ./themes/original/img/b_edit.png" width="16" height="16" alt="Edit" /></a> </p>
    <p>
    <span class="syntax"><span class="syntax_alpha syntax_alpha_reservedWord">SELECT</span> <span class="syntax_alpha syntax_alpha_functionName">SUBSTRING_INDEX</span><span class="syntax_punct syntax_punct_bracket_open_round">(</span> <span class="syntax_alpha syntax_alpha_identifier">email</span><span class="syntax_punct syntax_punct_listsep">,</span> <span class="syntax_quote syntax_quote_single">'@'</span><span class="syntax_punct syntax_punct_listsep">,</span> <span class="syntax_punct">-</span><span class="syntax_digit syntax_digit_integer">1</span> <span class="syntax_punct syntax_punct_bracket_close_round">)</span> <span class="syntax_punct syntax_punct_listsep">,</span> <span class="syntax_alpha syntax_alpha_functionName">count</span><span class="syntax_punct syntax_punct_bracket_open_round">(</span> <span class="syntax_punct">*</span> <span class="syntax_punct syntax_punct_bracket_close_round">)</span> <br /><span class="syntax_alpha syntax_alpha_reservedWord">FROM</span> <span class="syntax_quote syntax_quote_backtick">`targets_X`</span> <br /><span class="syntax_alpha syntax_alpha_reservedWord">WHERE</span> <span class="syntax_alpha syntax_alpha_identifier">email</span> <span class="syntax_alpha syntax_alpha_reservedWord">NOT</span> <span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span> <span class="syntax_quote syntax_quote_single">'%yahoo%'</span><br /><span class="syntax_alpha syntax_alpha_reservedWord">AND</span> <span class="syntax_alpha syntax_alpha_identifier">email</span> <span class="syntax_alpha syntax_alpha_reservedWord">NOT</span> <span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span> <span class="syntax_quote syntax_quote_single">'%aol%'</span><br /><span class="syntax_alpha syntax_alpha_reservedWord">AND</span> <span class="syntax_alpha syntax_alpha_identifier">email</span> <span class="syntax_alpha syntax_alpha_reservedWord">NOT</span> <span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span> <span class="syntax_quote syntax_quote_single">'%gmail%'</span><br /><span class="syntax_alpha syntax_alpha_reservedWord">AND</span> <span class="syntax_alpha syntax_alpha_identifier">email</span> <span class="syntax_alpha syntax_alpha_reservedWord">NOT</span> <span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span> <span class="syntax_quote syntax_quote_single">'%hotmail%'</span><br /><span class="syntax_alpha syntax_alpha_reservedWord">AND</span> <span class="syntax_alpha syntax_alpha_identifier">email</span> <span class="syntax_alpha syntax_alpha_reservedWord">NOT</span> <span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span> <span class="syntax_quote syntax_quote_single">'%msn%'</span><br /><span class="syntax_alpha syntax_alpha_reservedWord">AND</span> <span class="syntax_alpha syntax_alpha_identifier">email</span> <span class="syntax_alpha syntax_alpha_reservedWord">NOT</span> <span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span> <span class="syntax_quote syntax_quote_single">'%att%'</span><br /><span class="syntax_alpha syntax_alpha_reservedWord">AND</span> <span class="syntax_alpha syntax_alpha_identifier">email</span> <span class="syntax_alpha syntax_alpha_reservedWord">NOT</span> <span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span> <span class="syntax_quote syntax_quote_single">'%comcast%'</span><br /><span class="syntax_alpha syntax_alpha_reservedWord">AND</span> <span class="syntax_alpha syntax_alpha_identifier">email</span> <span class="syntax_alpha syntax_alpha_reservedWord">NOT</span> <span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span> <span class="syntax_quote syntax_quote_single">'%earthlink%'</span><br /><span class="syntax_alpha syntax_alpha_reservedWord">AND</span> <span class="syntax_alpha syntax_alpha_identifier">email</span> <span class="syntax_alpha syntax_alpha_reservedWord">NOT</span> <span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span> <span class="syntax_quote syntax_quote_single">'%mindspring%'</span><br /><span class="syntax_alpha syntax_alpha_reservedWord">AND</span> <span class="syntax_alpha syntax_alpha_identifier">email</span> <span class="syntax_alpha syntax_alpha_reservedWord">NOT</span> <span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span> <span class="syntax_quote syntax_quote_single">'%mac.com%'</span><br /><span class="syntax_alpha syntax_alpha_reservedWord">AND</span> <span class="syntax_alpha syntax_alpha_identifier">email</span> <span class="syntax_alpha syntax_alpha_reservedWord">NOT</span> <span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span> <span class="syntax_quote syntax_quote_single">'%att%'</span><br /><span class="syntax_alpha syntax_alpha_reservedWord">AND</span> <span class="syntax_alpha syntax_alpha_identifier">email</span> <span class="syntax_alpha syntax_alpha_reservedWord">NOT</span> <span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span> <span class="syntax_quote syntax_quote_single">'%netcom%'</span><br /><span class="syntax_alpha syntax_alpha_reservedWord">AND</span> <span class="syntax_alpha syntax_alpha_identifier">email</span> <span class="syntax_alpha syntax_alpha_reservedWord">NOT</span> <span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span> <span class="syntax_quote syntax_quote_single">'%rr.com%'</span><br /><span class="syntax_alpha syntax_alpha_reservedWord">AND</span> <span class="syntax_punct syntax_punct_bracket_open_round">(</span><div class="syntax_indent1"><span class="syntax_alpha syntax_alpha_identifier">email</span> <span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span> <span class="syntax_quote syntax_quote_single">'%.com%'</span> <span class="syntax_punct">||</span> <span class="syntax_alpha syntax_alpha_identifier">email</span> <span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span> <span class="syntax_quote syntax_quote_single">'%.net%'</span></div><span class="syntax_punct syntax_punct_bracket_close_round">)</span><br /><span class="syntax_alpha syntax_alpha_reservedWord">GROUP</span> <span class="syntax_alpha syntax_alpha_reservedWord">BY</span> <span class="syntax_alpha syntax_alpha_functionName">SUBSTRING_INDEX</span><span class="syntax_punct syntax_punct_bracket_open_round">(</span> <span class="syntax_alpha syntax_alpha_identifier">email</span><span class="syntax_punct syntax_punct_listsep">,</span> <span class="syntax_quote syntax_quote_single">'@'</span><span class="syntax_punct syntax_punct_listsep">,</span> <span class="syntax_punct">-</span><span class="syntax_digit syntax_digit_integer">1</span> <span class="syntax_punct syntax_punct_bracket_close_round">)</span> <br /><span class="syntax_alpha syntax_alpha_reservedWord">LIMIT</span> <span class="syntax_digit syntax_digit_integer">929</span> </span>
    </p>
    <p>
    <strong>MySQL said: </strong><a href="http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html" target="mysql_doc"><img class="icon" src="./themes/original/img/b_help.png" width="11" height="11" alt="Documentation" title="Documentation" /></a>
    </p>
    <code>
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* ) FROM `targets_X` WHERE email NOT LIKE '%yahoo%' AND email NOT LIKE '%aol%' A' at line 1
    </code><br />
    </div><fieldset class="tblFooters"> </fieldset>

    <script type="text/javascript">
    //<![CDATA[
    // updates current settings
    if (window.parent.setAll) {
    window.parent.setAll('en-utf-8', 'utf8_unicode_ci', '1', 'contacts', 'targets_X');
    }
    // set current db, table and sql query in the querywindow
    if (window.parent.reload_querywindow) {
    window.parent.reload_querywindow(
    'contacts',
    'targets_X',
    '');
    }

    if (window.parent.frame_content) {
    // reset content frame name, as querywindow needs to set a unique name
    // before submitting form data, and navigation frame needs the original name
    if (typeof(window.parent.frame_content.name) != 'undefined'
    && window.parent.frame_content.name != 'frame_content') {
    window.parent.frame_content.name = 'frame_content';
    }
    if (typeof(window.parent.frame_content.id) != 'undefined'
    && window.parent.frame_content.id != 'frame_content') {
    window.parent.frame_content.id = 'frame_content';
    }
    //window.parent.frame_content.setAttribute('name', 'frame_content');
    //window.parent.frame_content.setAttribute('id', 'frame_content');
    }
    //]]>
    </script>
    </body>
    </html>

    If I re-'edit' the query via pop-up (FC6/FF2) and get rid of all the weird PMA formatting spaces, it works and I get expected output.

    FAILS: SELECT SUBSTRING_INDEX ( email , '@' , -1 ) , count ( * ) FROM ...
    WORKS: SELECT SUBSTRING_INDEX(email,'@',-1), count(*) FROM ...

    But the thing is, I DIDN'T ADD THE SPACES! The latter is how I typed it in. PMA does this often, why should it always add these spaces around braces and commas and quotes...

     
  • Marc Delisle

    Marc Delisle - 2008-01-18
    • assigned_to: nobody --> lem9
     
  • Marc Delisle

    Marc Delisle - 2008-01-20

    Logged In: YES
    user_id=210714
    Originator: NO

    Bug from original reporter fixed for 2.11.5 (by the fix for 1873110).

     
  • Marc Delisle

    Marc Delisle - 2008-01-20

    Logged In: YES
    user_id=210714
    Originator: NO

    To: oh4real,
    for the SUBSTRING_INDEX() example, I wasn't able to reproduce it with phpMyAdmin 2.11.2.2 or 2.11.4. I tested with:

    CREATE TABLE `targets_X` (
    `email` varchar(100) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    INSERT INTO `targets_X` VALUES('marc@yahoo.com');
    INSERT INTO `targets_X` VALUES('marc@phpmyadmin.net');
    INSERT INTO `targets_X` VALUES('marc@virginmobile.ca');

     
  • Marc Delisle

    Marc Delisle - 2008-01-23
    • priority: 5 --> 1
    • summary: Export of query results with LIMIT and CONCAT_WS() Fails --> (ok 2.11.5) Export of query results (LIMIT and CONCAT_WS())
    • status: open --> open-fixed
     
  • Marc Delisle

    Marc Delisle - 2008-01-23

    Logged In: YES
    user_id=210714
    Originator: NO

    Fixed in subversion, thanks for reporting.

     
  • Marc Delisle

    Marc Delisle - 2008-03-01
    • status: open-fixed --> closed-fixed
     
  • Michal Čihař

    Michal Čihař - 2013-06-11
    • Status: closed-fixed --> fixed
     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks