Bug 66130 - LIKE gives error when used in a Join criterion
Summary: LIKE gives error when used in a Join criterion
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.1.0.1 rc
Hardware: Other All
: medium normal
Assignee: Lionel Elie Mamane
URL:
Whiteboard: BSA target:4.2.0 target:4.1.0.2
Keywords: regression
Depends on:
Blocks:
 
Reported: 2013-06-24 16:46 UTC by Fernand
Modified: 2013-07-01 13:31 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Fernand 2013-06-24 16:46:21 UTC
we use the Mysql Native connector for Windows extension to make the connection to the MySQL database
Like is still working in a general where clause but not when used in a JOIN clause

atable is a table in a MYSQL database
atable.field is a field in the atable


JOIN atable on atable.field like "text%"
gives a error

where atable.field like "text%" 
gives no error

Steps to reproduce:
1. ....
2. ....
3. ....

Current behavior:

Expected behavior:

              
Operating System: Windows XP
Version: 4.1.0.1 rc
Last worked in: 4.0.4.2 release
Comment 1 Joel Madero 2013-06-25 23:53:09 UTC
Lionel - looks quite serious, you mind poking at it?
Comment 2 Lionel Elie Mamane 2013-06-27 09:45:57 UTC
Let me guess... One of your "JOIN" tables has a TIME or TIMESTAMP (DATETIME) column, and the native connector you use is compiled for LibreOffice 4.0.

There is a backwards-binary-incompatible change in LibreOffice 4.1 for time-related datatypes. We may do another one between 4.1.0.rc1 and 4.1.0.final, or not, not completely decided yet. We plan that this will be the last one and the LibreOffice 4 ABI will stabilise once 4.1.0 is released.

The MySQL native connector needs a recompile for LibreOffice 4.1. Hopefully we can do that in time for the release.
Comment 3 tim 2013-06-27 13:14:35 UTC
I have datetime fields in MySql tables which are being misinterpreted in Base 4.1.0.1 on ubuntu 12.04 64 bit.  All the pre-existing dates appear to be  01/01/1800.

I am using Mysql native connector.  

Using JDBC I get other errors on datetime fields (it says the datetime is 0 and invalid).  Is this the same problem?
Comment 4 tim 2013-06-27 13:55:23 UTC
Correction - with JDBC it works fine.

I got an error using JDBC because a record had previously been saved using mysql connector creating an invalid datetime.  JDBC quite properly didn't like it when I tried to read it.
Comment 5 Lionel Elie Mamane 2013-06-27 14:41:54 UTC
(In reply to comment #3)
> I have datetime fields in MySql tables which are being misinterpreted in
> Base 4.1.0.1 on ubuntu 12.04 64 bit.  All the pre-existing dates appear to
> be  01/01/1800.

> I am using Mysql native connector.  

Are you using an Ubuntu build (PPA?) or a TDF build?

If you are using an Ubuntu build, make sure you use the MySQL native connector from the same Ubuntu source/version. Please show me:

COLUMNS=300 dpkg -l libreoffice
COLUMNS=300 dpkg -l libreoffice-mysql-connector


If you are using a TDF build, please explain which MySQL native connector you use. Compiled yourself against 4.1 sources? The ones on http://extensions.libreoffice.org/ are *not* compatible with LibreOffice 4.1. It is written on their download page.
Comment 6 Fernand 2013-06-27 16:36:36 UTC
>> Lionel 
Indead nearly all our tables have a timestamp (to know when and who has updated)
i most also admid that LIKE in a join is not a good practice and can been avoided.
Anyhow i hope Jésus finished sucesfull his Thesis and can make a recompile for 4.1
Comment 7 Terrence Enger 2013-06-27 17:13:27 UTC
Short version: I see an error, maybe the reported error or maybe not,
without going near any time-related column.  Workaround suggested.


Long version ...

I am setting bug status NEW, but this is merely a guess, as Fernand
does not tell us exactly what error he saw.  If his error is the error
that I see, then the bug summary should be changed to not reference
MySQL.


So, for specificity, let me say that in a quite different environment,
I issued SQL statement

    select * from Table1 a join Table2 b on a.number like '0%'

and LibreOffice presented a dialog 

    Syntax error in SQL statement

with <More> revealing SQL Status:HY000, Error code: 1000, and further
descriptions ...

    Syntax error in SQL statement

    SELECT * FROM "Table1" "a" JOIN "Table2" "b" ON LIKE '0%'

    Syntax error in SQL statement

Note that the second of these further descriptions omits the variable
before the LIKE operator.


For comparison, `isql` accepts and processes my sql statement
correctly, and LibreOffice accepts and processes correctly the
statement

    select *
      from Table1 a join Table2 b on a.number = b.number
     where a.number like '0%'

This suggests a workaround for the error.


My .odb file uses an ODBC connection to PostgreSQL, and my Libreoffice
(which no longer has the crash reported in bug 65619 "segfault
querying spreadsheet with LIKE criterion" and bug 65653 "EDITING: Base
crashes with queries with keyword LIKE in GUI-Mode") is master commit
60e2096, pulled 2013-06-18, configured with

    --enable-option-checking=fatal
    --enable-dbgutil
    --enable-crashdump
    --without-system-postgresql
    --without-myspell-dicts
    --without-help
    --with-extra-buildid
    --without-doxygen

built and executing ubunty-natty (11.04) 32-bit.


HTH,
Terry.
Comment 8 Lionel Elie Mamane 2013-06-27 17:23:01 UTC
(In reply to comment #7)
> Short version: I see an error, maybe the reported error or maybe not,
> without going near any time-related column.

Terrence, in your scenario, does it work in LibreOffice 4.0.x (or earlier)?
Comment 9 Terrence Enger 2013-06-27 19:53:09 UTC
I find the bug ABSENT in the following versions, all of them Linux
32-bit:
(*) 4.0.3.1 Linux 32-bit (build id a67943c)
(*) 3.6.4.3 Linux 32-bit (build id2ef5aff)
(*) 3.3.4 (OOO330m19 (Build:401), tag libreoffice-3.3.3.1, Ubuntu
    package 1:3.3.4-0ubuntu1.4)

Alas, I cannot try my daily bibisect (which is awfuly old, anyway)
because my 64-bit Linux lacks ODBC.

FWIW, the option "execute SQL statement directly" is another fine
workaround for the problem.
Comment 10 Terrence Enger 2013-06-27 20:04:09 UTC
On the assumption that Fernand is reporting the same error that I see,
I am changing ...

  - summary, because
      - I see the problem with ODBC to PostgreSQL
      - LIKE works in the WHERE clause

  - platform All, because I see the problem on Linux

  - priority way down, because there is an easy workaround

Of course, if Fernand is reporting a different error, then this is wrong.
Comment 11 Lionel Elie Mamane 2013-06-27 20:17:39 UTC
OK, this looks like it is unrelated to MySQL connector indeed.

This could be related to  http://cgit.freedesktop.org/libreoffice/core/commit/?id=16c9ce4877def18ca4578171a96615a632d08092

I'll try to investigate tommorrow.
Comment 12 tim 2013-06-28 07:22:09 UTC
(In reply to comment #5)
> (In reply to comment #3)
> > I have datetime fields in MySql tables which are being misinterpreted in
> > Base 4.1.0.1 on ubuntu 12.04 64 bit.  All the pre-existing dates appear to
> > be  01/01/1800.
> 
> > I am using Mysql native connector.  
> 
> Are you using an Ubuntu build (PPA?) or a TDF build?
> 
> If you are using an Ubuntu build, make sure you use the MySQL native
> connector from the same Ubuntu source/version. Please show me:
> 
> COLUMNS=300 dpkg -l libreoffice
> COLUMNS=300 dpkg -l libreoffice-mysql-connector
> 
> 
> If you are using a TDF build, please explain which MySQL native connector
> you use. Compiled yourself against 4.1 sources? The ones on
> http://extensions.libreoffice.org/ are *not* compatible with LibreOffice
> 4.1. It is written on their download page.

LO:

ii  libreoffice    1:4.1.0~rc1-buildfix1-0ubuntu1~precise1~ppa5  office productivity suite (metapackage)

and:

un  libreoffice-mysql-connector   <none>    (no description available)

I was not aware that the connector was not compatible.  I'll try the version from the PPA.
Comment 13 tim 2013-06-28 07:55:01 UTC
I tried using the connector version downloaded from the ppa, which seems to be 1.0.2 and it's got the same problem as 1.0.3.

I'll stick with jdbc for now (which works ok, just slower)

Will this be fixed in the full release version of 4.1?
Comment 14 tim 2013-06-28 12:47:44 UTC
Just to be clear, using the ppa to install the connector I now get:

ii  libreoffice-mysql-connector 1.0.2+LibO4.1.0~rc1-buildfix1-0ubuntu1~precise1~ppa5 MariaDB/MySQL Connector extension for LibreOffice

Date/time fields are still mangled.
Comment 15 Commit Notification 2013-06-28 14:30:02 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=ede7f6ae5fa9fd7a466066de74941a09117cc9a0

fdo#66130 correct "skip column name" condition for LIKE predicate



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 16 Lionel Elie Mamane 2013-06-28 14:40:43 UTC
(In reply to comment #14)
> Just to be clear, using the ppa to install the connector I now get:
 
> ii  libreoffice-mysql-connector
> 1.0.2+LibO4.1.0~rc1-buildfix1-0ubuntu1~precise1~ppa5 MariaDB/MySQL Connector
> extension for LibreOffice

> Date/time fields are still mangled.

OK, this is a different bug than this one.

1) I fixed several issues around mangling of time and datetime (timestamp)
   fields in the last 2-3 days. If you could retest when the PPA is updated
   after today, it would be nice. Or restes with something from
   http://dev-builds.libreoffice.org/daily/libreoffice-4-1/
   that is generated after today.

2) If you still get problems and/or you get problems with dates,
   please file a separate bug and put me in CC.
Comment 17 Lionel Elie Mamane 2013-06-28 14:41:46 UTC
Fix for 4.1 submitted for review on https://gerrit.libreoffice.org/4606
Comment 18 tim 2013-06-28 15:58:33 UTC
(In reply to comment #16)
> (In reply to comment #14)
> > Just to be clear, using the ppa to install the connector I now get:
>  
> > ii  libreoffice-mysql-connector
> > 1.0.2+LibO4.1.0~rc1-buildfix1-0ubuntu1~precise1~ppa5 MariaDB/MySQL Connector
> > extension for LibreOffice
> 
> > Date/time fields are still mangled.
> 
> OK, this is a different bug than this one.
> 
> 1) I fixed several issues around mangling of time and datetime (timestamp)
>    fields in the last 2-3 days. If you could retest when the PPA is updated
>    after today, it would be nice. Or restes with something from
>    http://dev-builds.libreoffice.org/daily/libreoffice-4-1/
>    that is generated after today.
> 
> 2) If you still get problems and/or you get problems with dates,
>    please file a separate bug and put me in CC.
If this is a connector problem what do I need to re-install?  The connector or LO or both?

I have been struggling slightly having made the jump to 4.1 on my main system.  Ubuntu kept wanting to re-install a 3.n version, and I couldn't find a good way of having 3 and 4 on my system, so I added the pre-release PPA for 4.1.  However, that doesn't seem to include the GB localisation, and seems to use an old mysql connector.

Is there a recommended way of having both a final release and a pre-release (or beta) fully installed on ubuntu?  I have failed to find it.
Comment 19 Commit Notification 2013-06-29 07:53:10 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=ca0bbec3c9ce2defee96743cf63227e46cd01c11&h=libreoffice-4-1

fdo#66130 correct "skip column name" condition for LIKE predicate


It will be available in LibreOffice 4.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 20 tim 2013-06-30 07:05:24 UTC
(In reply to comment #16)
> (In reply to comment #14)
> > Just to be clear, using the ppa to install the connector I now get:
>  
> > ii  libreoffice-mysql-connector
> > 1.0.2+LibO4.1.0~rc1-buildfix1-0ubuntu1~precise1~ppa5 MariaDB/MySQL Connector
> > extension for LibreOffice
> 
> > Date/time fields are still mangled.
> 
> OK, this is a different bug than this one.
> 
> 1) I fixed several issues around mangling of time and datetime (timestamp)
>    fields in the last 2-3 days. If you could retest when the PPA is updated
>    after today, it would be nice. Or restes with something from
>    http://dev-builds.libreoffice.org/daily/libreoffice-4-1/
>    that is generated after today.
> 
> 2) If you still get problems and/or you get problems with dates,
>    please file a separate bug and put me in CC.

Having taken extra care about which native connector I am using I can now confirm that the mysql native connector shipped with the ubuntu pre-release PPA works OK with date/times, but the extension downloaded from the website (tools, extensions ...), which states it is version 1.0.3, does not.  I had to ensure that I had completely removed the 1.0.3 version before using synaptic to re-install the connector from the PPA (recorded as 1.0.2), and it seems I had previously not done so correctly.

With apologies for wasting people's time.
Comment 21 Terrence Enger 2013-07-01 13:31:53 UTC
With master commit a08f579 fetched 2013-06-28 18:30 UTC, following the steps from comment 7, I see that the bug is fixed.

Fernand:  If the problem is fixed for you and you are reporting the same problem, can you please change the bug status to VERIFIED?

Thanks,
Terry.