lucene-solr-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alexandre Rafalovitch <arafa...@gmail.com>
Subject Re: Solr incorrectly fetching elements that do not match conditions in where as all-null rows
Date Mon, 15 Aug 2016 14:17:00 GMT
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
View raw message