lucene-solr-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Luis SepĂșlveda <l...@abroadwith.com>
Subject Re: Solr incorrectly fetching elements that do not match conditions in where as all-null rows
Date Mon, 15 Aug 2016 14:38:21 GMT
I'm very sorry, but you're right. Using one of the queries from the query
log, I get a 1 row(s) returned. So it itsn't a Solr issue.

Thanks a lot Alexandre.

2016-08-15 16:17 GMT+02:00 Alexandre Rafalovitch <arafalov@gmail.com>:

> Hmm. I would still take as truth the database logs as opposed to Solr
> logs. Or at least network traces using something like Wireshark.
>
> Otherwise, you need some way to reduce your DIH query to the minimum
> reproducible example. I am used to reading tech support emails and
> even then I am not sure I can parse the significant configuration
> aspects from the multiple parallel and nested entities. Can you reduce
> this to the simplest (two level?) entity definition with a single
> field and explain what you expected and what you are seeing.
>
> Regards,
>    Alex.
> P.s. Solr DIH does have a gotcha with SQL import that it automagically
> tries to match table column names to fields defined in schema and
> populate them even if not explicitly declared. This does not match to
> the way you describe the problem (your select statement still needs to
> return those fields), but perhaps it interacts with something else to
> trigger it.
> ----
> Newsletter and resources for Solr beginners and intermediates:
> http://www.solr-start.com/
>
>
> On 15 August 2016 at 23:54, Luis SepĂșlveda <luis@abroadwith.com> wrote:
> > Thanks for the promp reply.
> >
> > h.enabled=true is a typo. It should be c.enabled=true, because the table
> > companies also has a column called enabled. That part is working fine (it
> > doesn't fetch companies with enabled=false).
> >
> > About the DB queries, I've taken, by turning Debug and Verbose on in the
> > Dataimport tab, the queries that Solr is sending to DB, executed the same
> > queries in my MySQL client. It clearly says '0 row(s) returned'.
> >
> > 2016-08-15 15:37 GMT+02:00 Alexandre Rafalovitch <arafalov@gmail.com>:
> >
> >> Solr (well DIH) just passes that query to the DB, so if you are
> >> getting extra rows (not extra fields), than I would focus on the
> >> database side of the situation.
> >>
> >> Specifically, I would confirm from the database logs what the sent
> >> query actually looks like.
> >>
> >> Very specifically, in your very first entity, I see the condition
> >> "h.enabled=true" where "h" does not match the table names in the FROM
> >> statement. Perhaps, that's the problem?
> >>
> >> Regards,
> >>    Alex.
> >> ----
> >> Newsletter and resources for Solr beginners and intermediates:
> >> http://www.solr-start.com/
> >>
> >>
> >> On 15 August 2016 at 23:27, Luis SepĂșlveda <luis@abroadwith.com> wrote:
> >> > Hello,
> >> >
> >> > Solr is trying to process non-existing child/nested entities. By
> >> > non-existing I mean that they exist in DB but should not be at Solr
> side
> >> > because they don't match the conditions in the query I use to fetch
> them.
> >> >
> >> > I have the below solr data configuration. The relationship between
> tables
> >> > is complicated, but the point is that I need to fetch child/nested
> >> entities
> >> > and perform some calculations at query time. My problem is that some
> >> > products have onSite services that are not enabled. I would expect
> Solr
> >> > from ignoring those elements because of the conditions in the query.
> If I
> >> > turn debug on when importing, I can see that all fields are null.
> >> However,
> >> > Solr still tries to process them, which results in invalid SQL queries
> >> > because it replaces null fields with nothing.
> >> >
> >> >     <entity name="product" query="SELECT p.id AS productId,
> >> > CONCAT(unitPrice,',',currency) AS unitPriceCurr FROM products p LEFT
> JOIN
> >> > companies c ON c.id=p.companyId WHERE h.enabled=true">
> >> >
> >> >         <entity name="serviceTypes"
> >> >                 query="SELECT s.serviceType, sl.language FROM
> services s
> >> > LEFT JOIN serviceLanguages sl ON s.id=sl.serviceId WHERE
> >> > companyId=${product.companyId} AND s.enabled=true">
> >> >             <field column="serviceType" name="services" />
> >> >             <field column="language" name="languages" />
> >> >         </entity>
> >> >
> >> >         <entity name="onSite"
> >> >                 query="SELECT s.id, s.enabled, ${product.unitPrice} +
> >> > (hourlyPrice * MIN(hours)) AS onSitePriceRaw,
> >> CONCAT(${product.unitPrice} +
> >> > (hourlyPrice * MIN(hours)), ',', '${product.currency}') AS onSitePrice
> >> FROM
> >> > services s LEFT JOIN serviceHourlyPrices shp ON s.id=shp.serviceId
> WHERE
> >> > companyId=${product.companyId} AND s.enabled=true AND
> >> s.serviceType='OS'">
> >> >             <field column="onSitePrice" name="onSitePrice" />
> >> >             <entity name="fullReducedOnSitePrice"
> >> >                     query="SELECT CONCAT(${onSite.onSitePriceRaw} *
> (1 -
> >> > percentage), ',', '${product.currency}') AS fullReducedOnSitePrice
> FROM
> >> > discounts WHERE companyId=${product.companyId} AND category='FULL'">
> >> >                 <field name="fullReducedOnSitePrice"
> >> > column="fullReducedOnSitePrice"/>
> >> >             </entity>
> >> >             <entity name="partialReducedOnSitePrice"
> >> >                     query="SELECT CONCAT(${onSite.onSitePriceRaw} *
> (1 -
> >> > percentage), ',', '${product.currency}') AS partialReducedOnSitePrice
> >> FROM
> >> > discounts WHERE companyId=${product.companyId} AND
> category='PARTIAL'">
> >> >                 <field name="partialReducedOnSitePrice"
> >> > column="partialReducedOnSitePrice"/>
> >> >             </entity>
> >> >         </entity>
> >> >     </entity>
> >> >
> >> > The problem seems to be related to the condition s.enabled=true in the
> >> > query, because are rows with enabled=false that are causing problems
> >> (Solr
> >> > interprets them as rows with all fields null). I get an invalid SQL
> query
> >> > SELECT CONCAT( * (1 - percentage), ',', 'USD') AS
> fullReducedOnSitePrice
> >> > FROM discounts WHERE companyId=65.
> >> >
> >> > How can I force Solr to ignore, as it should, those elements?
> >>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message