Hi Everyone,

After upgrading to Hibernate Search 3.3CR1, Core 3.6 Final versions, (Oracle 10g DB; XE 10g test environment) I've encountered the following Exception on a preaviously working query:

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Exception in thread "AWT-EventQueue-0" org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2536)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
at org.hibernate.search.engine.ObjectLoaderHelper.initializeObjects(ObjectLoaderHelper.java:116)
at org.hibernate.search.engine.QueryLoader.executeLoad(QueryLoader.java:80)
at org.hibernate.search.engine.AbstractLoader.load(AbstractLoader.java:70)
at org.hibernate.search.query.FullTextQueryImpl.list(FullTextQueryImpl.java:317)
at org.hibernate.search.jpa.impl.FullTextQueryImpl.getResultList(FullTextQueryImpl.java:137)
at faun.views.forms.SearchForm.refreshButtonActionPerformed(SearchForm.java:724)
at faun.views.forms.SearchForm.access$1(SearchForm.java:706)
at faun.views.forms.SearchForm$FormListener.actionPerformed(SearchForm.java:692)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "THIS_"."IDCOL": invalid identifier

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
at org.hibernate.loader.Loader.doQuery(Loader.java:802)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2533)
... 38 more


I'm working on an Eclipse JPA project, persistence.xml:
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/ ... ce_2_0.xsd">
<persistence-unit name="FaunPU" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<class>faun.persistence.Ajanlatok</class>
<class>faun.persistence.DmDcskCsCskTarif</class>
<class>faun.persistence.DmDcskMobk</class>
<class>faun.persistence.DmDcskCalcData</class>
<properties>
<property name="hibernate.connection.username" value="agotadaniel"/>
<property name="hibernate.connection.driver_class" value="oracle.jdbc.OracleDriver"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.OracleDialect"/>
<property name="hibernate.connection.password" value="Hid1234"/>
<property name="hibernate.connection.url" value="jdbc:oracle:thin:@192.168.121.160:1521:tshid"/>
<property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>
<!-- <property name="hibernate.search.default.indexBase" value="/indexes"/>-->
<property name="hibernate.lucene.default.directory_provider" value="org.hibernate.lucene.store.RAMDirectoryProvider"/>
</properties>
</persistence-unit>
</persistence>

Where the class faun.persistence.Ajanlatok is indexed (via annotations) and queried against as:


EntityManager em = Logic.getEntityManager();
FullTextEntityManager entityManager = new FullTextEntityManagerImpl(em);

List<Ajanlatok> items = em
.createNativeQuery(
"select * from DM_DCSK_Ajanlatok ",
Ajanlatok.class).getResultList();
for (Ajanlatok item : items) {
entityManager.index(item);
}
....
private org.apache.lucene.search.Query buildLuceneQuery(
String searchString,
Class<?> searchedEntity) {
org.apache.lucene.search.Query luceneQuery = null;

String[] productFields = { "col1", "col2"};

MultiFieldQueryParser parser = new MultiFieldQueryParser(Version.LUCENE_30, productFields, new StandardAnalyzer(Version.LUCENE_30));
try {
luceneQuery = parser.parse(searchString);
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

....
org.apache.lucene.search.Query luceneQuery = buildLuceneQuery(searchString, Ajanlatok.class);
query = entityManager.createFullTextQuery(luceneQuery,
Ajanlatok.class);
java.util.Collection data = query.getResultList();

PhraseQuery works, but gives no results (probably separate issue)
FuzzyQuery doesn't work either, nor does QueryParser implemented as follows:


// StringTokenizer st =
// new StringTokenizer(searchString, " ");
// PhraseQuery query = new PhraseQuery();
// while (st.hasMoreTokens()) {
// String token = st.nextToken();
// query.add(new Term("col1",token
// ));
// }
//


// luceneQuery = query;//parser.parse( words );
//
//
// String searchQuery = "col1:Karát~";
// QueryParser parser = new QueryParser(Version.LUCENE_30,
// "title",
// new StandardAnalyzer(Version.LUCENE_30)
// );

Based on the affected clases the problem seems to be parser implementation related and has something to do with the way Hibernate handles aliases...(the same time I upgraded to 3.0CR1 had to change to a table definition where the columns where defined as aliases -> column names in "" in the generated create table SQL statement: create table tableName as select * from source, looking at the generated code in SQLDeveloper it contained "" quotes... I have no clue wether this could cause the issue actually :( )


Thanks in advance, Daniel

Looking at this: Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "THIS_"."IDCOL": invalid identifier
your problem has definitely something to do with your changes to table definition.

• Have you rebuild your index after the change?
• Have you turned on debug logging? If so look for the SQL query executed to load the entities which matched your Lucene query
• Can you run Hibernate Criteria queries properly? (This might help to narrow down the problem)
• Do you generate the DB schema from the mapping or do you need to create a mapping matching your DB schema?
• For further help it would be helpful to see your annotated classes

--Hardy

Hi Hardy



The table change was nothing more than "create table Ajanlatok as select * from oldAjanlatok"
Table names where remapped in the annotations. -> edit: wasn't...



Yes, the project is early in development stage, the indeces are built from the scratch after every startup.



No, but will and I'll post the results as soon as possible.



Haven't tried, will as above, but I'm not familiar with the Criteria API.



I have an existing db schema, unfortunately not modifyable...
the Entity was genereted with th Netbeans entity generation from existing DB feature and annotated by hand to support search...

I had to remove business specific stuff (nothing different from the stuff below, like setter/getter pairs and annotations included)

@Entity
@Indexed()
@Table(name = "....", catalog = "", schema = "....")
@NamedQueries({
@NamedQuery(name = "Ajanlatok.findAll", query = "SELECT d FROM Ajanlatok d")})
public class Ajanlatok implements Serializable {


@Transient
private PropertyChangeSupport changeSupport = new PropertyChangeSupport(this);
private static final long serialVersionUID = 1L;

@Field(index = Index.TOKENIZED, store = Store.YES)
@Column(name = "VEVO_NEV")
private String vevoNev;
@Column(name = "SAVE_LOGIN")
private String saveLogin;
@Column(name = "STATUS")
private String statusz;
@Column(name = "VALID_FROM")
private String validFrom;
@Column(name = "VALID_TO")
private String validTo;
@Id
@Basic(optional = false)
@Column(name = "IDCOL")
@DocumentId
private BigDecimal idcol;



public Ajanlatok() {
}

public Ajanlatok(BigDecimal idcol) {
this.idcol = idcol;
}
....

public String getVevoNev() {
return saveLogin;
}

public void setVevoNev(String vevoNev) {
String oldVevoNev= this.vevoNev;
this.vevoNev= vevoNev;
changeSupport.firePropertyChange("vevoNev", oldVevoNev, vevoNev);
}

public String getStatus() {
return statusz;
}

public void setStatus(String status) {
String oldStatusz = this.statusz;
this.statusz = statusz;
changeSupport.firePropertyChange("statusz", oldStatusz, statusz);
}

public String getValidFrom() {
return validFrom;
}

public void setValidFrom(String validFrom) {
String oldValidFrom = this.validFrom;
this.validFrom = validFrom;
changeSupport.firePropertyChange("validFrom", oldValidFrom, validFrom);
}

public String getValidTo() {
return validTo;
}

public void setValidTo(String validTo) {
String oldValidTo = this.validTo;
this.validTo = validTo;
changeSupport.firePropertyChange("validTo", oldValidTo, validTo);
}

......

public BigDecimal getIdcol() {
return idcol;
}

public void setIdcol(BigDecimal idcol) {
BigDecimal oldIdcol = this.idcol;
this.idcol = idcol;
changeSupport.firePropertyChange("idcol", oldIdcol, idcol);
}

@Override
public int hashCode() {
int hash = 0;
hash += (idcol != null ? idcol.hashCode() : 0);
return hash;
}

@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof Ajanlatok)) {
return false;
}
Ajanlatok other = (Ajanlatok) object;
if ((this.idcol == null && other.idcol != null) || (this.idcol != null && !this.idcol.equals(other.idcol))) {
return false;
}
return true;
}

public void addPropertyChangeListener(PropertyChangeListener listener) {
changeSupport.addPropertyChangeListener(listener);
}

public void removePropertyChangeListener(PropertyChangeListener listener) {
changeSupport.removePropertyChangeListener(listener);
}
}

Thanks, Daniel

Hi, Hardy

Problem is resolved but I still have some concerns:

I have forget to mention the following details:
After creating the table as descriped above and getting the error the first time I took a look on the table definition sql in SqlDeveloper and noticed the "" quotes in the table name. I droped the table and regenerated with the SqlDeveloper script without the "" quotes. Lets refer to this table as Schema2.Ajanlatok. The original with the same content/structure is called Schema1.Ajanlatok.

I' have tried to query against both tables (after reindexing them with Lucene(Search)) -> I posted the results, see above.

thanks to your well pointed questions I've realised that I havent changed the Schema after creating the new table in the annotations.

After changing the schema in the annotations too, the Search query-s ran fine as well, without any problems.
(SQL scripts where modified so I haven't got any problems when executing them).

Bottom line:
- My original issue is resolved, solution was to regenerate the table generation SQL with SQLDeveloper and remove the ""-s.
- This solution was masked by mistake on updating the annotations to refer to the new table...

- Hibernate Search can't handle table aliases in Lucene Queries, probably has something to do with the Parser implementations (my opinion, without proof)
- Will setup a demo project to demonstrate the issue and send in the SQL queries from log trace to determine wether this is a problem or defined behaviour as an update to this post...

Thanks for the clear ad well pointed questions helped a lot :-)

I don't quite understand what you mean with "Hibernate Search can't handle table aliases ", but if you can create some demo project to demonstrate it, that would be great. If you think it is a bug, you can create a Jira issue and attach your sample project there.

--Hardy